Generating Seed Data for Tests

1. Why good seed data is the foundation of reliable tests

Seed data is the starting point of every database test, and the quality of that data directly determines how meaningful a test is. A test that runs against three hand-maintained test rows only finds bugs that affect exactly those three rows. As soon as a fourth variant shows up in production, an empty field, a special character, or an unusual combination of foreign keys, the test stays blind to it because the seed data never modeled that case.

Systematically generated seed data solves this by not relying on a fixed handful of example rows, but on a script that generates arbitrarily many, structurally diverse datasets. The difference between manually maintained fixtures and generated seed data is comparable to the difference between example-based and property-based testing: instead of naming individual cases, a rule is defined that produces arbitrarily many plausible cases. The sections below show how to generate such seed data deterministically, referentially correctly, and with realistic edge cases.

Another often underestimated effect of good seed data is its influence on how meaningful performance tests are. A query that runs against ten test rows in a few milliseconds can, against realistic data volumes, expose a missing index or an inefficient join plan that stays invisible with small data volumes. Anyone who wants to catch performance regressions early therefore needs not only correct but also sufficiently large seed data that at least proportionally reflects the scale of production data.

2. Synthetic versus anonymized seed data

There are two fundamental ways to obtain seed data: generate it entirely synthetically, or anonymize and reuse an existing dataset, for example from production. Synthetic seed data has the advantage that it can be freely shared, checked into public repositories, and reproduced in any environment without any privacy risk. The downside: it only reflects real distributions as well as the generation script models them, and it easily misses patterns that occur in real data but that nobody explicitly modeled.

Anonymized production data naturally reflects real distributions more precisely, but comes with extra effort for legally sound anonymization and is harder to version because it is tied to a concrete snapshot. In practice, a hybrid approach has proven effective: synthetic seed data for day-to-day test development, complemented by periodic tests against an anonymized copy for performance and integrity checks that depend on real data distributions.

Team size is another factor in this decision: in small teams with direct access to a secured production environment, the extra effort of periodic anonymization weighs more heavily than in larger organizations that already have a dedicated database owner responsible for compliance questions. Anyone working with external freelancers or agencies should default to synthetic seed data, to avoid ever passing real personal data outside the own infrastructure.

3. Generating deterministic seed data with fixed seeds

A common mistake with generated test data is regenerating it randomly on every test run. This leads to flaky tests that sometimes fail and sometimes do not, without any change to the code, simply because the random seed data happened to contain an edge case the test did not expect this time. The solution is a fixed random seed: the random generator is initialized with a constant starting value, so every run produces exactly the same sequence of seemingly random values.

With a fixed seed, generated test data becomes reproducible without losing the variety of genuine random data. A failed test can be reproduced exactly, because the same input data is generated again. For cases where deliberately different datasets per test run are wanted, for example for fuzzing, the actual seed used should be logged in the test output, so a failed run can later be reproduced with the same seed.

Another benefit of fixed seeds shows up in parallel testing: when several CI jobs run simultaneously against isolated test databases, the same seed produces identical starting data in every job, which is what makes comparisons between jobs meaningful in the first place. Without a fixed seed, a difference in test results between two parallel jobs could not be clearly attributed to a code difference, since it could just as easily be caused by different random data.

-- Deterministic seed data example
-- PostgreSQL: deterministic pseudo-random data with a fixed seed
-- setseed() takes a value between -1 and 1, always yields the same sequence
SELECT setseed(0.42);

INSERT INTO customers (id, email, created_at)
SELECT
  gs                                              AS id,
  'customer' || gs || '@example.test'             AS email,
  NOW() - (random() * INTERVAL '365 days')        AS created_at
FROM generate_series(1, 10000) AS gs;
-- Re-running this script produces the exact same created_at values every time

4. Faker libraries and realistic distributions

Faker libraries such as faker-js, the PHP package fakerphp/faker, or Python's Faker generate plausible names, addresses, email addresses and free text instead of falling back on generic placeholders like Test1, Test2. This is more than cosmetic: a search feature tested only against alphabetically sorted, equal-length test names can miss sorting or truncation bugs that would surface with real names containing umlauts, hyphens, or varying length.

It is important to think beyond names and addresses: Faker libraries also generate realistic distributions for numeric values, for example order totals following a log-normal-like distribution instead of a uniform one, which comes much closer to the real distribution of cart values. Anyone generating seed data with uniformly distributed random values tests a system against a data distribution that never occurs in production, and misses performance problems that only surface under a strongly skewed real distribution, for example a few very large orders alongside many small ones.

Localized Faker providers deserve particular attention for internationally oriented applications: a German address format differs structurally from a US or Japanese format, and a seed script that only models one locale misses parsing errors that only surface with real international addresses. Most Faker libraries support several locales simultaneously and can be configured so generated rows randomly switch between them.

-- PostgreSQL: realistic order amounts via log-normal-like distribution
-- Most orders small, a long tail of large orders — closer to real e-commerce data
INSERT INTO orders (id, customer_id, total_amount, status)
SELECT
  gs,
  (random() * 10000)::int % 10000 + 1,
  ROUND((EXP(RANDOM() * 2.0 + 3.0))::numeric, 2)  AS total_amount,
  (ARRAY['pending','paid','shipped','refunded'])[FLOOR(RANDOM() * 4 + 1)]
FROM generate_series(1, 50000) AS gs;
-- EXP(random * 2 + 3) skews heavily toward smaller values with a long tail

5. Preserving referential integrity while generating data

Generated seed data must respect the same foreign key relationships as real production data, otherwise inserts fail on constraint violations or produce test setups that could never occur in the real world. The most reliable approach is to populate parent tables first and then deliberately reuse the generated IDs in the child tables, instead of generating foreign key values independently at random.

A common mistake is filling foreign key columns with purely random IDs drawn from the entire possible value range without checking whether those IDs actually exist in the referenced table. That works in the short term if constraints are not enforced, but it produces test data that is semantically nonsensical, for example orders with customer IDs that never existed. Seed scripts should therefore always select from actually generated parent rows, for example via ORDER BY RANDOM() LIMIT 1 against the already populated table, instead of using completely independent random values.

For multi-level relationships, for example orders with line items and line items with products, the generation order must respect the entire chain: products first, then orders, then order line items referencing both previous tables. If this order is reversed, either constraint errors occur or, worse, seed scripts silently disable constraints and thereby produce referentially broken data that only triggers inexplicable failures in later tests.

-- Correct generation order for a multi-level relationship chain
-- Step 1: parent table first
INSERT INTO products (id, name, price)
SELECT gs, 'Product ' || gs, ROUND((RANDOM() * 100)::numeric, 2)
FROM generate_series(1, 1000) AS gs;

-- Step 2: orders reference existing customers (assumed already seeded)
INSERT INTO orders (id, customer_id, status)
SELECT gs, (SELECT id FROM customers ORDER BY RANDOM() LIMIT 1), 'paid'
FROM generate_series(1, 5000) AS gs;

-- Step 3: order_items reference BOTH orders and products, seeded last
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
  (SELECT id FROM orders ORDER BY RANDOM() LIMIT 1),
  (SELECT id FROM products ORDER BY RANDOM() LIMIT 1),
  1 + FLOOR(RANDOM() * 5)::int,
  ROUND((RANDOM() * 100)::numeric, 2)
FROM generate_series(1, 15000) AS gs;

6. Deliberately generating edge cases and boundary values

Purely randomly generated seed data covers common cases well, but systematically misses rare edge cases, because they are rare enough in a random distribution to be entirely absent from small samples. A robust seed script therefore deliberately complements the randomly generated bulk with known boundary values: empty strings, maximum field lengths, NULL values in optional columns, dates at year boundaries, negative numbers where only positive ones are expected, and unicode characters outside the basic multilingual plane, such as emoji.

These deliberately sprinkled-in edge cases are often more valuable than thousands of additional generic rows, because they test exactly the spots where software breaks most often. A proven pattern is to place a fixed number of explicit edge case rows at the start of the seed script, labeled with speaking IDs or comments so a test failure can be immediately attributed to the concrete edge case, followed by the large bulk of randomly generated but referentially correct rows.

An often forgotten edge case involves timezones and date boundaries: a seed row with a timestamp exactly at midnight in the server timezone can behave differently in application logic than the same timestamp interpreted in the timezone of a user on another continent. Deliberately complementing seed data with such timezone boundary cases catches errors in date and time handling that virtually never occur by chance in purely randomly distributed test data.

-- Seed script section: edge cases
-- Explicit edge cases FIRST, clearly labeled, before the bulk random data
INSERT INTO products (id, name, price, description) VALUES
  (1, '',                                  0.00,   NULL),                    -- empty name, zero price, no description
  (2, REPEAT('x', 255),                    999999.99, 'Max length name'),     -- max VARCHAR length boundary
  (3, 'Product with emoji ???? and umlaut ä', 19.99,  'Unicode edge case'),    -- non-BMP + umlaut
  (4, 'Negative test',                      -1.00,  'Should never be valid'), -- invalid negative price
  (5, 'Year boundary product',              49.99,  'Created at year boundary');

UPDATE products SET created_at = '2025-12-31 23:59:59' WHERE id = 5;

7. Versioning seed scripts and reusing them in CI

Seed scripts belong in version control just as much as migration scripts, because they must evolve alongside the schema. If a column is added or a constraint tightened, the seed script must be updated in lockstep, otherwise it fails on the next test run or produces data that does not cover the new constraint. A sensible pattern is to organize seed scripts into versions that match the schema migrations, so a given database state is always tested with the matching seed script.

In the CI pipeline, the seed script should run automatically after every migration, before the actual tests start. That ensures every test run builds on the same, known data state, regardless of which developer last ran the pipeline. It is important to make the seed script idempotent, so it also runs cleanly against an already partially populated test schema, for example with TRUNCATE ... CASCADE as the first step before the actual inserts.

A proven naming scheme also helps a team unambiguously identify seed scripts: a prefix with the date or migration version in the filename immediately shows which schema state a seed script matches, without every developer having to inspect the contents individually. With frequent schema changes, it is worth keeping old seed script versions archived in the repository instead of deleting them, so bug reports against older database versions can still be reproduced.

#!/usr/bin/env bash
# ci-seed-and-test.sh — runs after every migration in the CI pipeline
set -euo pipefail

echo "[1/3] Truncating existing seed data (idempotent re-run)"
psql "$DB_URL" -c "TRUNCATE TABLE order_items, orders, products, customers CASCADE;"

echo "[2/3] Loading versioned seed script matching current schema state"
SEED_FILE="./seeds/$(migrate -database "$DB_URL" -path ./migrations version)_seed.sql"
psql "$DB_URL" -f "$SEED_FILE"

echo "[3/3] Running test suite against freshly seeded database"
npm test -- --database-url="$DB_URL"

8. Performance when generating large data volumes

Once seed scripts need to generate tens of thousands or millions of rows, the performance of the generation process itself becomes a concern. Individual INSERT statements per row, as many simple Faker scripts produce by default, are unsuitable for large data volumes because each statement causes its own round trip to the database plus transaction overhead. The much faster approach is to collect values in batches and load them with multi-row INSERT statements or the database's native bulk load feature, such as COPY in PostgreSQL.

Another frequently overlooked lever is temporarily disabling indexes and constraints during the bulk insert and re-enabling them afterward, because every index would otherwise need to be updated on every single insert. For very large seed data volumes, it is also worth parallelizing the generation process, for example by running multiple batches simultaneously against different ID ranges, as long as referential integrity across the batches is preserved.

The choice of transaction scope also measurably affects performance: a single huge transaction spanning millions of rows keeps the undo log and transaction memory occupied for an unnecessarily long time, while transactions that are too small, with one commit per row, add up the overhead of frequent commits. Committing every few thousand rows is usually the best compromise in practice between memory consumption and commit overhead, and can be implemented in most seed scripts with only a few extra lines of logic.

9. Seed data strategies compared

Depending on the test goal, a different seed data strategy is the better fit. The table below ranks the most important approaches by effort, reproducibility and how much confidence they provide.

The table is meant as a decision aid, not a rigid rule: a team just starting out with systematically generated seed data should first start with Faker plus a fixed seed and a small edge case catalog, before investing in more elaborate strategies such as anonymized production snapshots.

In practice, these strategies complement each other: Faker with a fixed seed provides the reproducible bulk, deliberate edge case rows cover known boundary values, and periodic tests against anonymized production data validate that the synthetic seed data comes close to reality. Relying on just one strategy risks either missing reproducibility or missing realism.

When choosing the right strategy, it helps to clearly state the actual test goal: for functional correctness, Faker with a fixed seed plus an edge case catalog is usually enough. For performance validation ahead of a major release, the extra effort of an anonymized production snapshot is generally justified, because otherwise performance regressions only become visible once the system is already live.

10. Summary

Generating seed data for tests systematically means moving away from a handful of hand-maintained example rows and toward deterministic, referentially correct datasets with realistic distributions. A fixed random seed makes generated data reproducible. Faker libraries provide plausible values instead of generic placeholders, and deliberately sprinkled-in edge cases cover the spots where software breaks most often.

Referential integrity, versioning in lockstep with migration scripts, and performant bulk inserts turn a one-off script into a solid, repeatable part of the CI pipeline. Anyone who regularly validates synthetic seed data against anonymized production data ensures the generated test data stays close to reality instead of drifting away from it.

In the end, the initial investment in a well-designed seed data script pays off many times over: every new test, every new CI pipeline and every new developer on the team benefits from a data foundation that neither has to be maintained by hand nor reinvented on every run, but is reliably documented and reproducible.

11. FAQ: Generating Seed Data for Tests