Seeding Realistic Database Data for E2E and QA Testing

The Short Version

Two failure modes show up constantly in E2E testing, and they sit at opposite ends of the same mistake. An empty test database looks clean and fast but misses almost every bug that depends on existing data: broken joins, pagination edge cases, permission checks against a user that already exists. A raw production database dump looks realistic but carries real customer PII into a disposable environment that gets destroyed, logged, and possibly screen-shared without the same controls production data is supposed to have. The fix is neither extreme: a deterministic seed script that produces realistic, varied, but entirely synthetic data, run automatically as part of every environment's deploy step.

Why Empty Databases Miss the Bugs That Matter

A schema with no rows in it exercises the create path and almost nothing else. It cannot exercise a join across three tables, since there is nothing to join. It cannot exercise pagination, since there is no second page. It cannot exercise a permission check against an existing user's role, a report computed over a date range with actual records in it, or a uniqueness constraint that only fails once a conflicting row already exists.

This matters more for E2E environments specifically than for unit tests, because E2E tests are supposed to be the layer that catches exactly this class of bug: the one that only appears once the system has real, interconnected state to operate on. An E2E suite that runs green against an empty database every time is not actually testing the thing E2E tests exist to test.

Why a Raw Production Dump Is the Wrong Fix

The intuitive fix, copying a recent production database into every test environment, solves the realism problem and creates a much worse one. A production dump carries real customer names, emails, addresses, and whatever domain-specific sensitive fields the application stores, directly into an environment that a developer's laptop can reach, that CI logs might capture fragments of, and that gets destroyed and recreated dozens of times a day with no audit trail matching who saw which dump when.

Even with anonymization applied after the fact, this approach means every test environment inherits the actual scale and shape of production data, which is usually far more than E2E tests need and slows down every environment's creation time proportionally to production's current size, a cost that only grows over time as the real database does.

The Middle Path: A Deterministic Seed Script

The pattern that actually works is a seed script, checked into the same repository as the application, that generates a fixed, realistic dataset from scratch every time, with no dependency on a production snapshot at all. It runs as a lifecycle script on the database component (or a dedicated component that depends on it), the same way a schema migration would:

1components:
2  - kind: Database
3    name: postgres
4    dockerCompose:
5      environment:
6        POSTGRES_PASSWORD: SECRET['seed-db-pass']
7        POSTGRES_DB: app_test
8
9  - kind: GenericComponent
10    name: seed-data
11    dependsOn: [postgres]
12    runnerImage: 'postgres:15-alpine'
13    deploy:
14      - 'PGPASSWORD={{ components.postgres.exported.DB_PASSWORD }} psql -h {{ components.postgres.name }} -U postgres -d app_test -f /seed/seed.sql'

dependsOn: [postgres] guarantees the seed script runs only after the database component is actually ready to accept connections, not just after its pod has started, which matters because a database container reporting "running" and a database actually accepting connections are not the same moment.

What Makes Seed Data Realistic Without Being Real

The specific content of seed.sql (or an equivalent seed script in whatever language the application's ecosystem favors) should aim for variety in state, not volume of rows. A handful of users covering every account state the application treats differently (new, active, suspended, deleted-but-soft-retained) catches more real bugs than ten thousand identical active users would. A few dozen orders spanning enough date range to trigger any date-bucketed report logic, with at least one in every status the order state machine defines, does the same job a full year of production order history would, at a fraction of the size and none of the privacy exposure.

1INSERT INTO users (email, name, status, created_at) VALUES
2  ('active.user@example.test', 'Active User', 'active', now() - interval '30 days'),
3  ('new.user@example.test', 'New User', 'active', now() - interval '1 day'),
4  ('suspended.user@example.test', 'Suspended User', 'suspended', now() - interval '60 days'),
5  ('legacy.user@example.test', 'Legacy User', 'deleted', now() - interval '400 days');

Using an obviously non-real domain (@example.test, which is reserved specifically for this purpose under RFC 2606) makes it immediately visually distinguishable from real customer data in logs, screenshots, and screen shares, which is a small habit that prevents an entire category of "wait, is this real data?" incident review later.

Making the Seed Deterministic Across Every Environment

The property that makes this pattern reliable for E2E testing specifically is determinism: the same seed script produces the exact same starting dataset every single time, in every environment created from the same template. That means an E2E test asserting "the suspended user's dashboard shows a specific banner" can rely on that user existing with that exact status, in every environment, without the test needing to create its own fixture data first.

1$ bns environments create --from-path bunnyshell.yaml \
2    --name "pr-482-e2e" --project <PROJECT_ID> --k8s <CLUSTER_ID> --deploy
3
4Environment successfully created... deploying...

Every environment created from this same bunnyshell.yaml, regardless of which PR triggered it, gets the identical seed data, which removes an entire class of test flakiness that comes from tests implicitly depending on data that happened to exist in whichever environment they ran against last.

Handling Data That Needs to Vary Per Test Scenario

Not every test scenario wants the exact same default dataset. A test suite covering an empty-state UI needs a user with zero orders, which the default seed intentionally does not include if the default is meant to exercise pagination. Rather than maintaining two entirely separate seed scripts, branch the same script on an environment variable set per environment:

1environmentVariables:
2  SEED_SCENARIO: default
3
4components:
5  - kind: GenericComponent
6    name: seed-data
7    dependsOn: [postgres]
8    deploy:
9      - 'psql -h postgres -U postgres -d app_test -f /seed/{{ env.vars.SEED_SCENARIO }}.sql'

A PR environment that needs the empty-state scenario overrides SEED_SCENARIO to empty-state at creation time, while every other PR environment inherits the default seed with no changes required to the template itself.

Keeping Seed Data in Sync With Schema Changes

The seed script's biggest maintenance risk is drift: a migration adds a new required column, and the seed script, unaware of it, starts failing or silently producing rows missing that field. The fix is running the seed script in the exact same pipeline step sequence as the migration, immediately after it, against the exact same environment, so a broken seed script fails the same deploy pipeline the broken migration would have, instead of surfacing later as a mysterious test failure with no obvious connection to the schema change that actually caused it.

1components:
2  - kind: GenericComponent
3    name: migrate
4    dependsOn: [postgres]
5    deploy:
6      - 'npx prisma migrate deploy'
7
8  - kind: GenericComponent
9    name: seed-data
10    dependsOn: [migrate]
11    deploy:
12      - 'psql -h postgres -U postgres -d app_test -f /seed/default.sql'

Chaining seed-data off migrate instead of directly off postgres guarantees the seed script only ever runs against a fully migrated schema, catching schema drift at deploy time rather than at whatever point downstream a test happens to touch the missing column.

What This Replaces in Practice

Teams coming from a manual process (a developer periodically running a seed script by hand against a shared staging database, or worse, a stale production snapshot someone downloaded once and never refreshed) usually find the actual seed script logic barely needs to change to fit this pattern. What changes is when and how often it runs: automatically, on every environment's first deploy, against a fresh, isolated database that only that environment's tests will ever touch, instead of manually, occasionally, against infrastructure other people's tests are also relying on staying in a known state.

Generating Volume Without Hand-Writing Every Row

Hand-written INSERT statements work well for the handful of specific-state records a test suite asserts against directly (the suspended user, the empty-state account), but real pagination and load-bearing tests also need enough generic volume to actually trigger boundary behavior: a listing page's second page, a report that only shows something once fifty-plus rows exist. Hand-writing fifty rows is tedious and produces data that looks suspiciously uniform. A small generator script, run as part of the same seed step, covers this better:

1import psycopg2
2from faker import Faker
3
4fake = Faker()
5conn = psycopg2.connect(host="postgres", dbname="app_test", user="postgres")
6cur = conn.cursor()
7
8for _ in range(75):
9    cur.execute(
10        "INSERT INTO orders (customer_name, email, status, created_at) VALUES (%s, %s, %s, now() - (random() * interval '90 days'))",
11        (fake.name(), fake.email(domain="example.test"), fake.random_element(["pending", "shipped", "delivered", "cancelled"]))
12    )
13
14conn.commit()

Faker's domain="example.test" argument keeps every generated email obviously synthetic even at volume, and randomizing created_at within a real date range naturally exercises any date-bucketed report logic without needing to hand-pick specific dates for each row. This script runs as the same deploy lifecycle step as the hand-written fixtures, typically executed right after them, so both pieces (specific-state fixtures plus generic volume) land in the same database in one pass.

Seed Data for Third-Party Integrations

A meaningful share of E2E test flakiness in practice traces back not to the application's own database at all, but to a third-party API the application calls during the test: a payment processor, an email provider, a mapping service. Hitting the real third-party sandbox from every ephemeral E2E environment multiplies API calls by however many environments exist concurrently, which risks hitting the third party's own rate limits and makes tests dependent on that third party's uptime, not just the application's.

The seeding pattern extends naturally to this case by seeding a local mock or stub server with fixture responses instead of seeding a database:

1components:
2  - kind: Service
3    name: payment-mock
4    dockerCompose:
5      image: wiremock/wiremock:3.3.1
6      volumes:
7        - ./fixtures/payment-mock:/home/wiremock/mappings
8
9  - kind: Application
10    name: api
11    dependsOn: [payment-mock]
12    dockerCompose:
13      environment:
14        PAYMENT_API_BASE_URL: 'http://payment-mock:8080'

The fixture files checked into ./fixtures/payment-mock are exactly as deterministic and version-controlled as the SQL seed script, and they remove the E2E suite's dependency on an external service's sandbox environment staying available and rate-limit-friendly during a busy CI day.

Keeping Seed Scripts Versioned Alongside the Schema

A seed script is code, and it should live under the same review and versioning discipline as the migrations it depends on, not as a one-off file someone wrote once and occasionally patches by hand when something breaks. Checking the seed script into the same pull request as a schema-changing migration means a reviewer sees both changes together and can catch a seed script that's about to break before it merges, rather than discovering it the next time an unrelated PR's environment fails to seed for a reason that has nothing to do with that PR's own changes.

1migrations/
2  0042_add_orders_priority_column.sql
3seed/
4  default.sql          # Updated in the same PR to populate the new column

Teams that skip this discipline tend to discover seed drift the expensive way: a schema migration merges cleanly, and every subsequent PR's environment starts failing to seed, with the failure surfacing on unrelated work with no obvious connection back to the migration that actually broke it, days or weeks later.

Common Mistakes Worth Naming Directly

Seeding with INSERT statements that assume auto-incrementing IDs starting at 1. A seed script that hardcodes foreign keys against assumed ID values breaks the moment insertion order changes even slightly, or the moment a schema switches from serial IDs to UUIDs. Referencing rows by a stable business key (an email, a slug) inside the same script, rather than an assumed numeric ID, survives both kinds of change.

Treating the seed script as a one-time setup cost instead of a maintained artifact. A seed script that nobody has touched since it was first written tends to silently drift out of sync with what the application actually needs from its test data, especially once new features add new required fields or new business rules that make some previously-valid seed rows invalid.

Seeding too much instead of too little. The instinct, once a team decides empty databases are a problem, sometimes overcorrects toward seeding thousands of rows to feel "production-like." This mostly adds deploy time without adding test coverage, since a few hundred well-chosen rows covering every distinct state already exercises pagination, joins, and edge cases as effectively as ten thousand rows that are mostly duplicates of the same shape.

Related Reading

For the full environment lifecycle this seeding step is one part of, see Automated PR Environments for E2E Testing. For the database component configuration this pattern builds on, see Preview Environments With PostgreSQL. For a related but distinct data problem, curating data for demos rather than tests, see Sales Demo Environments.

Checklist

• Seed data is generated by a deterministic script, never copied from a production dump
• Seed data uses an obviously non-real email domain and no genuine customer identifiers
• The seed script covers every distinct state the application treats differently, not just a volume of identical rows
• The seed step depends on the migration step completing first, not just the database being up
• Scenario variations use a variable-driven branch in one script rather than duplicated scripts