DEV Community: Mikhail Shytsko

Synthetic Data for CI/CD: Seed Fresh, Valid Rows on Every Run

Mikhail Shytsko — Fri, 14 Aug 2026 22:35:34 +0000

Every CI pipeline that touches a database needs rows to run against, and there are only three ways to get them: restore a copy of production, replay a checked-in seed.sql, or generate the data inside the run. Synthetic data for CI/CD is the third option — generate fresh, referentially-valid rows from the schema on every build, so no production data is copied and the data can't go stale between migrations the way a static seed file does. For most teams it's the one option that both stays correct as the schema changes and stays clean of real customer data.

This guide covers why pipeline-time generation beats the other two, what a generator needs to be usable in CI, and the newer pressure behind it: AI coding agents that open pull requests and need a real database to test against.

Why generate synthetic data for CI/CD

Generating synthetic data in the pipeline beats the two older approaches (copying production and replaying a static seed.sql) on the two things CI cares about: it moves no real customer data, and it stays in sync with the schema. Each older approach fails one of those.

Copying production drags real customer data into a build environment. That's a compliance problem the moment the schema holds anything personal, and it usually means a masking pipeline bolted on top, plus a slow restore of a database far larger than any test needs. You inherit production's size and its risk to test a pull request.

A checked-in seed.sql avoids the privacy problem but rots. The day someone adds a NOT NULL column or a new foreign key, the file is wrong: it fails, or worse, quietly seeds an incomplete row. Static fixtures drift because the schema moves and the file doesn't. The seed file maintenance problem is exactly this, and every migration charges the tax.

Generating in the pipeline sidesteps both. The data is synthetic, so there are no production records to move or mask, and because it's built from the live schema each run, a new migration is picked up automatically: the pipeline-time seeding step re-reads the schema and writes rows that fit it. The catch is that the generator has to produce valid, connected data unattended, which is where most tools fall down. Seedfast is a schema-aware generator built for that: point it at a connection string and it reads the live schema itself.

On the axes that decide whether a seed source survives in CI:

Approach	Moves production PII?	Stays valid after a schema change?	Sized for the test?	Runs unattended in CI?
Copy / restore production	Yes (real records)	Only after a fresh copy and re-mask	No (production-sized)	Not without a masking step first
Checked-in `seed.sql`	No	No, breaks when the schema moves	Yes	Until the next migration breaks it
Generate in the pipeline	No	Yes, re-reads the live schema each run	Yes	Only with a generator good enough to run on its own

The bottom row's last column is the honest cost of generating: it works only if the generator holds integrity without a human watching. Run your first seed free to put that on your own schema.

What a CI seed step actually needs

A CI seed step has to clear five bars a point-and-click generator never faces. Miss one and the step either won't run unattended or won't fail loudly enough to trust.

A non-interactive command. It runs from a connection string and an API key in an environment variable, with no UI, no login prompt, and no paste step. If a human has to click, it isn't a CI step.
A schema read at run time. The seed runs after migrations apply, so the generator must read the current schema then, not a snapshot from when someone configured it.
Referential integrity across the foreign-key graph. Data that violates constraints fails the insert; data that skips relationships makes integration tests lie. The generator produces connected rows and handles nullable circular foreign keys without hand-holding.
Machine-readable output and honest exit codes. A JSON output mode and a non-zero exit on failure are how the build gate knows whether the seed worked.
A cost that doesn't scale with run frequency. Seeds fire on every push, every branch, across a job matrix, and a per-row or per-token price turns that volume into a bill that climbs with your commit rate. A flat plan doesn't move.

Test data for AI agents in the pipeline

AI coding agents now open pull requests, and agentic test runners drive an app end to end on every PR. Neither one seeds the database it depends on. Both assume realistic, connected rows are already there: the agent that wrote the code and the agent that tests it each expect something else to have prepared the schema.

That gap closes cleanly when the generator is both a CLI step and an MCP tool. Seedfast exposes the same seed run over the Model Context Protocol, so an agent in Claude Code or Cursor calls seedfast_run to seed the branch database, then lets the tests (human-written or agent-driven) run against it. The seeded-data prerequisite collapses into one tool call. As more of the pipeline runs on agents, "the test data is generated, valid, and current" becomes the assumption everything else rests on.

Seedfast as the CI seed step

Seedfast runs as a single step after migrations: one command points at the database, reads the live schema, and writes connected rows.

# .github/workflows/test.yml (excerpt)
- name: Seed test database
  run: npx seedfast seed --scope "realistic accounts, orders, and line items" --output json
  env:
    SEEDFAST_API_KEY: ${{ secrets.SEEDFAST_API_KEY }}
    SEEDFAST_DSN: ${{ secrets.TEST_DATABASE_URL }}

The --scope is plain English: Seedfast generates connected accounts, orders, and line items with valid foreign keys between them, sized to the test rather than to production. --output json returns a machine-readable result, and the command exits non-zero on failure so the build gate can read it. Add a table or column in a later migration and the next run picks it up, with nothing to reconfigure. The SEEDFAST_API_KEY comes from a free Seedfast account, and the CI/CD database seeding guide has the full GitHub Actions and GitLab CI setup, including per-environment keys and exit-code handling — the service container and health-check mechanics underneath that setup get their own walkthrough.

For ephemeral databases this pairs with branch-per-PR workflows: the Neon branching seed data guide covers seeding a fresh branch database per run, and E2E test fixtures covers generating data for Playwright and Cypress.

Choosing a generator for CI

Not every test-data tool clears that bar. Web-based column generators like Mockaroo are built for clicking, so they don't drop into an unattended command. ORM-coupled seeders like Drizzle's and Prisma's seed scripts need your application code running and only work inside one framework. Production-copy tools like pg_dump hand back the privacy and size problems you came here to avoid, and masking platforms like Tonic Structural still start from a copy of production to de-identify it — the production access CI-time synthetic data skips entirely. Even Faker-based scripts generate columns in isolation, with no way to keep the rows connected. Seedfast takes a connection string, reads the live schema, and holds referential integrity with nobody watching.

If you're cross-shopping on that axis, the best Postgres test data generator comparison ranks tools on schema-awareness and CI fit, Seedfast vs Tonic Fabricate weighs a metered web agent against a flat CLI step, and the data seeding tools guide covers the regulated-industry angle where copying production isn't allowed at all. For the broader strategy of where test data comes from and how it stays valid, see test data management.

Frequently asked questions

What is synthetic data for CI/CD?

Synthetic data for CI/CD is test data generated inside the pipeline from your database schema, after migrations apply, rather than copied from production or replayed from a static seed file. Because it's regenerated from the live schema each run, it stays valid as the schema changes, and because it's synthetic it carries no real customer data.

How is generating data in CI different from a copied production dump?

A copied dump is production-sized, so a multi-gigabyte restore can take minutes on every job, and it usually needs a masking step before it's safe to use. A generated seed is sized to the test and skips masking entirely, because there's no real data in it to mask. The dump also reflects whatever the schema looked like when it was taken; the generator re-reads the current schema instead.

Can a synthetic data generator keep referential integrity in CI?

It has to, or the seed is useless. A generator built for CI has to produce parent and child rows that reference each other correctly, including tables with circular references. Seedfast handles this automatically, so the data that lands is referentially valid with no manual ordering on your side.

Does seeding in CI send my data to an AI provider?

No rows leave your database. The generator reads the schema's shape (table and column names, types, and constraints) to plan the data, then generates the values itself; your production records are never involved, because they aren't in the test database to begin with. If a table or column name is itself sensitive, confirm that path fits your policy before wiring it into CI.

Does seeding on every pipeline run get expensive?

It depends on the pricing model. A four-job matrix on a twenty-push day is eighty seed runs; a per-row or per-token generator bills all eighty, scaling with your commit rate. Seedfast sells a flat monthly plan that includes a pool of credits, and a run draws from that pool by how much data it generates, with no per-row or per-token meter underneath. Eighty runs on a mid-size schema is roughly $48 of credits, well inside Premium's $180, and the monthly bill itself doesn't move with your commit rate.

Seed your CI database as a pipeline step

Seedfast connects to your PostgreSQL database, reads the live schema, and generates connected, realistic data through a single CLI step or an MCP tool, fresh and referentially-valid on every pipeline run, with no copied production data and no seed.sql to patch. Set up CI/CD seeding or run your first seed in about two minutes — the free plan doesn't ask for a card. See pricing for the paid tiers.

Related guides:

CI/CD Database Seeding: the full GitHub Actions and GitLab CI setup
Staging Without Production Data: generate instead of copy, for lower environments
Neon Branching Seed Data: seed a fresh branch database per run
E2E Test Fixtures: generate data for Playwright and Cypress runs

Originally published at seedfa.st.

ALTER TABLE, 5 Million Rows, and the Deploy That Took Down the Site

Mikhail Shytsko — Fri, 14 Aug 2026 22:34:58 +0000

A migration that takes 50ms on your dev database can lock a production table for 20 minutes, and running it against production-scale volume first is how you catch that before your users do.

The Thursday-afternoon deploy looked harmless. Adding a NOT NULL column with a default value to the orders table had passed review and run fine on staging, and the pipeline showed green.

Then production went quiet, the way an on-call channel goes quiet, because the orders table, all 8 million rows of it, sat locked while every API endpoint that touched orders queued and the load balancers began returning 502s. Forty minutes passed before the locks released and the retro could start. The schema change was exactly what the team needed, and the outage came down to a single missing step — nobody had run it against 8 million rows anywhere but production.

This article covers migration performance — lock duration, rewrite time, and the row counts that turn a 50ms change into a 40-minute outage. Its correctness companion, catching NULL concatenations and failed constraints before they ship, is how to review a migration against realistic data.

Key Takeaways

Migration time scales with data volume. A change that runs in 50ms on 50 development rows can hold a lock for minutes on millions of production rows.
The dangerous operations rewrite or scan the whole table — backfilling a new column, a non-concurrent CREATE INDEX, an integer-to-bigint type change, and validating a new FOREIGN KEY. Each one holds a lock proportional to row count.
Some changes are metadata-only and safe at any size — adding a column with a constant default (PostgreSQL 11+), widening a varchar, or varchar to text. Reading the SQL won't tell you which class you're in.
Lock duration, not total runtime, is what takes the site down. A two-minute migration that never holds a lock longer than 200ms is safer than a 30-second one that locks the table the entire time.
You can time any migration before it ships by seeding production-scale, foreign-key-valid data with Seedfast and watching for lock contention as it runs. The data-shape side of the same check, at small volume, lives in migration review.

The Migration Time Bomb

Schema migrations carry a property most code changes lack. Their execution time scales with data volume, because on many operations PostgreSQL rewrites or scans every row rather than only updating metadata.

This creates a class of problems that are completely invisible in development:

Operation	100 rows	1M rows	5M rows
`ADD COLUMN ... DEFAULT` (pre-PG 11)	< 1ms	8s	42s
`ALTER COLUMN TYPE` (integer to bigint)	< 1ms	12s	65s
`CREATE INDEX` (single column)	< 1ms	4s	22s
`ADD COLUMN NOT NULL DEFAULT` (PG 11+)	< 1ms	< 1ms	< 1ms
`CREATE INDEX CONCURRENTLY`	< 1ms	6s	35s

Figures are illustrative orders of magnitude on commodity hardware; real numbers depend on your hardware, table width, indexes, and concurrent load.

That fourth row is worth a look. PostgreSQL 11 made ADD COLUMN ... DEFAULT a metadata-only operation for constant defaults, while the other operations still rewrite or scan the table and even the fast ones have nuances that bite at scale.

5 Migrations That Look Harmless at 100 Rows

1. Adding a NOT NULL Column With Backfill

-- Step 1: Looks fine
ALTER TABLE orders ADD COLUMN region VARCHAR(50);

-- Step 2: The backfill that locks the table
UPDATE orders SET region = 'us-east-1' WHERE region IS NULL;

-- Step 3: Now add the constraint
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

The backfill in step 2 is where it goes wrong. A single UPDATE touching 5 million rows acquires row locks across the entire table, so concurrent writes block, and autovacuum can't reclaim the dead tuples the backfill creates while the transaction stays open. If you have foreign key references, those tables might lock too.

At scale , a 5M-row backfill UPDATE can take 30+ seconds and block all concurrent writes to the table for the duration.

The safer pattern:

-- Batch the backfill
UPDATE orders SET region = 'us-east-1'
WHERE id IN (SELECT id FROM orders WHERE region IS NULL LIMIT 10000);
-- Repeat until no rows remain

2. Creating an Index on a Large Table

-- Acquires a SHARE lock — blocks writes for the entire build
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

On 100 rows it finishes instantly, but on 10 million rows it holds a SHARE lock for the entire index build, so every INSERT, UPDATE, and DELETE on that table queues behind it.

The safer pattern:

-- Builds the index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

CONCURRENTLY doesn't block writes, but it takes roughly 2-3x longer and can fail if there are concurrent schema changes. It also can't run inside a transaction block, which means most migration frameworks need special handling.

3. Changing a Column Type

-- Widening an integer primary key that's about to overflow
ALTER TABLE events ALTER COLUMN id TYPE BIGINT;

Every growing team eventually runs this one, an integer ID column approaching its ~2.1 billion ceiling. It reads like a one-line metadata tweak, but because integer and bigint have different on-disk representations, PostgreSQL has to rewrite the entire table while holding an ACCESS EXCLUSIVE lock, so a 5M-row table stays fully locked until it finishes.

The catch is that not every type change rewrites. Widening a varchar, or going from varchar to text, is binary-coercible, so PostgreSQL changes only the catalog and returns instantly even on 50 million rows, while id INTEGER to id BIGINT rewrites the whole table.

The safer pattern for a true rewrite adds a new column, backfills in batches, swaps with a rename, and drops the old column, which is more steps but never holds an extended lock.

4. Adding a Foreign Key Constraint

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);

Adding a foreign key makes PostgreSQL validate every existing row, a full table scan over 5 million orders while it holds a lock on both orders and customers. If customers is also large, you've locked two critical tables at once, and it only works if every order already points at a real customer, the kind of referential integrity that production data drifts away from over time.

The safer pattern:

-- Add the constraint without validating existing rows
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

-- Validate in a separate step (holds a weaker lock)
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;

NOT VALID adds the constraint for new rows immediately, and then VALIDATE checks the existing rows under a less aggressive lock whose duration still tracks the row count.

5. Dropping a Column (Yes, Really)

ALTER TABLE orders DROP COLUMN legacy_status;

In PostgreSQL, DROP COLUMN doesn't rewrite the table; it only marks the column as invisible. That sounds fast, and usually it is, but the operation still takes an ACCESS EXCLUSIVE lock, so if long-running queries are reading from the table, the DROP waits for them to finish, and while it waits, every new query queues behind it. A 1ms metadata operation can block the table for minutes when a slow SELECT is running.

At scale , the lock acquisition time becomes unpredictable, driven by whatever concurrent query workload happens to be running when the DROP fires.

The Seedfast Workflow: Seed, Migrate, Measure

The fix is to run your migration against production-scale data first. Seedfast generates realistic, relational data straight from your schema, so you can stand up a table at production row counts, without copying production rows or maintaining seed scripts, and time the migration against it.

Step 1: Seed Production-Scale Data

# Match your production table sizes
seedfast seed --scope "seed 5 million orders with customers, order items, and payments"

The generated rows come out FK-valid and realistically distributed, with proportions that match the scope you asked for (batching and scope sizing for the very largest runs are covered in the large-volume seeding guide):

Seeding Plan:
  public.customers — 500,000 records
  public.orders — 5,000,000 records
  public.order_items — 12,000,000 records
  public.payments — 4,800,000 records

Total: 22,300,000 records across 4 tables

Approve? (Y/n)

Step 2: Run the Migration and Measure

# Time the migration
time psql $DATABASE_URL -f migrations/20260225_add_region_column.sql

# Or with your migration framework
time flyway migrate
time rails db:migrate
time alembic upgrade head

Step 3: Check for Lock Contention

While the migration runs, open another terminal and monitor locks:

-- See which queries are blocked and what's blocking them
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  now() - blocked.query_start AS waiting_time
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Queries stacking up behind your migration are exactly what production will do, only with real user traffic behind them; PostgreSQL's explicit locking documentation explains which lock modes conflict.

Step 4: Benchmark the Safe Alternative

If the naive migration locks the table for too long, implement the safer pattern and measure again:

# Reseed a fresh database (or use a separate test database)
seedfast seed --scope "seed 5 million orders with customers"

# Run the batched migration
time psql $DATABASE_URL -f migrations/20260225_add_region_column_safe.sql

Now you have concrete numbers your team can decide on: "The naive migration locks orders for 38 seconds. The batched version takes 2 minutes total but never holds a lock for more than 200ms."

Run both candidate migrations against the same populated copy and you can see which lock pattern actually holds up (the seeding guide covers a first run).

CI/CD: Automated Migration Benchmarking

Wire this into your pipeline so it stops being a manual exercise.

name: Migration Benchmark

on:
  pull_request:
    paths:
      - 'migrations/**'

jobs:
  benchmark-migration:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: bench
          POSTGRES_USER: bench
          POSTGRES_PASSWORD: bench
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - name: Apply base schema
        run: |
          for f in migrations/*.sql; do
            psql $DATABASE_URL -f "$f" 2>/dev/null || true
          done
        env:
          DATABASE_URL: postgres://bench:bench@localhost:5432/bench

      - name: Seed test data
        run: seedfast seed --scope "seed 1 million orders with customers and payments" --output plain
        env:
          SEEDFAST_API_KEY: ${{ secrets.SEEDFAST_API_KEY }}
          DATABASE_URL: postgres://bench:bench@localhost:5432/bench

      - name: Benchmark new migration
        run: |
          NEW_MIGRATIONS=$(git diff --name-only origin/main -- migrations/ | sort)
          for f in $NEW_MIGRATIONS; do
            echo "--- Benchmarking: $f ---"
            time psql $DATABASE_URL -f "$f"
          done
        env:
          DATABASE_URL: postgres://bench:bench@localhost:5432/bench

      - name: Check migration duration
        run: |
          echo "Review migration timing above. Migrations over 30s need batching or CONCURRENTLY."

Now every PR with a migration gets benchmarked against realistic data, so a 200-row staging pass no longer vouches for a change. For running it unattended, the CI/CD database seeding guide covers non-interactive mode and spinning up a database per PR.

For Smaller Teams

Even without full CI integration, you can add a one-liner to your PR template:

## Migration Checklist
- [] Tested against production-scale data (`seedfast seed --scope "..."`)
- [] Migration completes in under 30 seconds on 1M+ rows
- [] No ACCESS EXCLUSIVE locks held for more than 5 seconds
- [] Uses CONCURRENTLY for index creation (if applicable)
- [] Backfills are batched (if applicable)

PostgreSQL Migration Gotchas Worth Knowing

A slow migration is just one of the bugs that only real test data catches; the same volume blind spot behind slow queries and N+1s hides slow migrations too. A few more that trip teams up at scale:

Transaction-wrapped migrations lock longer than you think. Most frameworks run each migration inside a transaction, which means the lock acquired at the start isn't released until the whole file finishes, backfills and constraint validations and index builds included.

Because CONCURRENTLY can't run in a transaction, a framework that wraps migrations by default (Rails, Flyway, Alembic) makes CREATE INDEX CONCURRENTLY fail until you configure that specific migration to run outside the transaction block.

For zero-downtime rewrites like a column-type change, pg_repack rebuilds a table in the background with minimal locking, though on a 50M-row table it still takes significant time and I/O, so test it against real volume first.

Autovacuum can fall behind after a large backfill. The dead tuples an UPDATE leaves behind cause table bloat that slows subsequent queries if autovacuum can't keep up, so test the migration and then check bloat with pg_stat_user_tables.

Before You Run That Migration in Production

Checklist for any migration touching a table with more than 100K rows:

Seed production-scale data locally

seedfast seed --scope "seed [your production row count] [table] with related records"

Time the migration. If it takes more than 10 seconds, consider batching or alternative approaches.
Monitor locks during execution. An ACCESS EXCLUSIVE lock held for more than a few seconds will impact production traffic.
Test the rollback too. A 30-second migration with a 5-minute rollback is a risky deploy.
Run it during low traffic if the lock duration is unavoidable, and know exactly how long that is, in seconds.
Check CONCURRENTLY support in your migration framework for index operations.
Validate constraints separately using NOT VALID + VALIDATE CONSTRAINT for foreign keys and check constraints.
Batch backfills — never UPDATE millions of rows in a single statement.

You can't measure lock duration on 50 rows, benchmark a backfill on an empty table, or catch CONCURRENTLY failing inside your framework's transaction wrapper without production-scale data.

Frequently asked questions

How do you test a database migration before running it in production?

You test a migration by running it against a database seeded with production-scale data and measuring how long it locks each table. Stand up a database on the current schema, seed it to the same row counts as production, run the migration with a timer, and monitor pg_locks in a second session while it executes. Whatever lock duration shows up there is what production will hit, only with live traffic queuing behind it.

Why does a migration that runs fast in development lock the table in production?

Because most migration cost scales with row count, and a development database has almost no rows. A backfill UPDATE, a non-concurrent CREATE INDEX, or an integer-to-bigint rewrite touches every row, so the same statement that finishes in under a millisecond on 50 dev rows can hold a lock for 40 seconds on 8 million production rows.

Which PostgreSQL operations require a full table rewrite?

Changing a column to an incompatible type (such as integer to bigint), adding a column with a volatile default, and some SET/DROP operations rewrite the whole table under an ACCESS EXCLUSIVE lock. Operations that are binary-coercible do not rewrite: widening a varchar, varchar to text, and, since PostgreSQL 11, adding a column with a constant default are metadata-only and instant at any size.

How do you add an index to a large Postgres table without downtime?

Use CREATE INDEX CONCURRENTLY, which builds the index without blocking writes. It takes roughly 2–3× longer than a plain CREATE INDEX and cannot run inside a transaction block, so most migration frameworks (Rails, Flyway, Alembic) need that specific migration configured to run outside their default transaction wrapper. A plain CREATE INDEX holds a SHARE lock that queues every write for the entire build.

Can I use a copy of production data to test migration performance?

A production copy answers the volume question, but it drags real customer records (and their compliance exposure) into an environment with weaker controls. By the time you refresh it, the schema has usually moved as well. Seeding the same row counts from the schema itself gives you identical lock behavior with nothing sensitive in the database. Seedfast works from your schema definitions rather than your rows, passing them to an external AI service to generate the data, so the thing to check is whether that schema handling fits your data-governance rules.

How does Seedfast help test migrations at scale?

It gives you a database at production row counts to benchmark against, seeded with FK-valid rows generated from your live schema. You point it at a local or throwaway CI database, where it reads the schema structure without touching your production rows. There's a free plan that doesn't ask for a card, and because the schema is read fresh on every run, the same command keeps working after a migration changes the table.

Related guides

Review SQL Migrations in 30 Seconds — the data-shape half of the same check: catching NULLs, duplicates, and silent truncation at small volume during code review
Load Testing Data: Why Your Benchmarks Lie — the other half of production-scale testing, for query latency and throughput rather than migration locks
PostgreSQL Test Data: A Syntax Cookbook — the raw SQL patterns for generating volume and realistic distributions by hand
Small Data, Big Lies: 6 Bugs Only Real Test Data Catches — why a 50-row development database hides the failures that only appear at scale
Circular Foreign Key Seed — handling FK cycles when you seed the large, related datasets a migration test needs
Your Staging Database Is a Compliance Violation Waiting to Happen — generating realistic test data instead of copying and anonymizing production
Get started with Seedfast — connect your database and seed production-scale data for your next migration test

Test the migration at production scale before you deploy it

Get Started | Documentation | Pricing

Seed a test database to production row counts with Seedfast and time your next migration against volume that behaves like the real thing.

Originally published at seedfa.st.

E2E Tests Without Brittle Fixtures: Generate Data on the Fly

Mikhail Shytsko — Fri, 14 Aug 2026 22:34:22 +0000

Flaky Playwright and Cypress runs trace back, more often than anyone expects, to e2e test fixtures that quietly stopped matching the schema after last Tuesday’s migration.

You have probably watched a Cypress test that passed for months fail on a Monday, though nobody touched the test or the feature. The cause is almost always the data — a column landed on users over the weekend while cypress/fixtures/users.json kept the old shape, the shared test database got wiped, or the hardcoded user 42 an assertion needs was deleted by a parallel test.

Failures like these stay quiet, so the cost is easy to miss. It surfaces as one flaky test this week and two the next, until someone loses half a day “fixing” tests that were never wrong.

Key Takeaways

Schema drift is the hidden cause behind most fixture failures. Every migration behind a checked-in fixture adds maintenance nobody scheduled, and the failure surfaces in the test runner while the small-data bugs that only real data catches stay hidden underneath.
The five anti-patterns share one habit, treating test data as shared, mutable state. Shared databases, hardcoded IDs, checked-in fixtures, test-order dependencies, and restored dumps each block isolation, and without it you can’t run tests in parallel.
Let the live schema generate the data your tests need. A migration that adds a column is picked up on the next run, with no fixture-update PR.
Query for the shape of the data a test needs rather than a hardcoded ID. Asking for a user who has orders is a mechanical rewrite, and it’s what makes generated data usable.
Seedfast turns the schema itself into the test-data source. Wired in as a setup step, it seeds a fresh database on every E2E run from the live structure (tables, constraints, foreign keys), so any local or CI database works as a target.

The Fixture Maintenance Trap

The first fixture you write always looks completely harmless. You need a single user to exercise the login flow, so you create test-user.json:

{
  "id": 42,
  "email": "testuser@example.com",
  "name": "Test User",
  "role": "admin"
}

Then the users table gets a department_id foreign key the fixture knows nothing about. Its seeded row fails to insert, or comes in with a NULL the code never expected, and the E2E test blows up with a cryptic error far from the real cause.

So someone adds department_id. But a department has to exist first, so departments.json appears, and the pull repeats for every related table, until six months later you have 30 fixture files in a dependency graph nobody fully understands.

cypress/fixtures/
  departments.json
  users.json # depends on departments
  categories.json
  products.json # depends on categories
  orders.json # depends on users
  order_items.json # depends on orders AND products
  payments.json # depends on orders
  shipping.json # depends on orders AND users
  reviews.json # depends on users AND products

Every schema migration is now a fixture migration too. As the ORM models and API contracts change, a JSON file somewhere drifts out of sync, and the tests fail only later, on a fresh database, in a new CI container, or when stale data meets new code.

Five Anti-Patterns That Make It Worse

1. The Shared Test Database

The whole QA team points at one database. Tests pass locally because the data happens to be there, CI fails because someone truncated the table, and two developers running it at once step on each other’s rows, none of it reproducible when the state differs per machine.

Developer A: INSERT INTO users (id, email) VALUES (1, 'alice@test.com')
Developer B: INSERT INTO users (id, email) VALUES (1, 'bob@test.com')
-- ERROR: duplicate key value violates unique constraint "users_pkey"

The usual fix, random suffixes, only pushes the mess into the assertions.

2. Hardcoded IDs and Magic Values

// Cypress test
cy.visit('/users/42/orders')
cy.get('[data-testid="order-row"]').should('have.length', 3)

This test assumes user 42 exists and has exactly 3 orders, so it breaks the moment the seed data shifts, somebody cleans the database, or CI starts auto-increment from a different number. The failure reads “expected 3, got 0” and explains nothing.

3. Fixture Files Checked Into Git

// fixtures/orders.json -- last updated 4 months ago
[
  { "id": 1, "user_id": 42, "total": 99.99, "status": "completed" },
  { "id": 2, "user_id": 42, "total": 149.50, "status": "pending" },
  { "id": 3, "user_id": 42, "total": 29.99, "status": "completed" }
]

Four months ago the orders table had no currency column, no shipping_address_id foreign key, and no NOT NULL on created_at, and the fixture still doesn’t know. It inserts silently with defaults until one is missing or a constraint rejects the row, leaving the code to assume a field never provided.

4. Test-Order Dependencies

describe('Order flow', () => {
  it('creates a user', () => {
    // Creates user, stores ID in Cypress alias
    cy.request('POST', '/api/users', { name: 'Test' }).as('user')
  })

  it('creates an order for the user', () => {
    // Depends on the previous test having run successfully
    cy.get('@user').then(user => {
      cy.request('POST', '/api/orders', { user_id: user.id })
    })
  })

  it('verifies the order appears in the list', () => {
    // Depends on BOTH previous tests
    cy.visit('/orders')
    cy.get('[data-testid="order-row"]').should('exist')
  })
})

If “creates a user” fails or is skipped, every later test fails too. Nothing runs in parallel or in isolation, because the suite is a chain and one broken link takes down everything after it.

5. The “Just Restore a Dump” Approach

# CI pipeline
- name: Restore test database
  run: pg_restore --clean --no-owner -d testdb fixtures/test_dump.sql

Database dumps are the heavyweight version of fixture files, large and usually binary, painful to diff, and just as quick to drift. They carry a developer’s machine assumptions about sequences and PostgreSQL versions, and a dump from production inherits every privacy and compliance liability of running real production data through staging.

The Test Isolation Problem

The same mistake sits under all five — data any test can read, write, or delete without the others knowing.

Share data and isolation goes first, taking parallel execution with it, and an 8-minute E2E suite stretches to 45. Developers then stop running it locally, so bugs a local run would have caught slip into CI, and fixes that took minutes now take hours.

The dependency chain:

Shared fixtures
  -> Tests depend on each other's data
    -> No parallel execution
      -> Slow test suite
        -> Developers skip it
          -> Bugs in CI
            -> Slow feedback loops

Every serious testing framework recommends test isolation as a core principle — Playwright gives each test its own browser context, and Cypress resets browser state before every test. But isolation means each test must set up its own data, and if that setup is a fixture file, you’re back where you started.

Generate Data, Don’t Maintain It

The alternative is to generate fresh data from the schema before each suite run, skipping the fixture file and the dump entirely.

The workflow:

Start with an empty database (or TRUNCATE the tables)
Generate the data your tests need
Run the tests
Tear down

Each run gets fresh data matching the current schema, so nothing drifts and a new column shows up automatically.

Seedfast as a Test Setup Step

Seedfast slots in front of your E2E suite as a setup step. Point the CLI at the live database and it works out the schema, then writes realistic, relational data to match:

# Before your E2E suite runs:
seedfast seed --scope "5 users with 3 orders each, all with payments and shipping addresses"

Add a NOT NULL currency column next sprint, and the next run simply includes a value for it; the fixture file that would have needed editing no longer exists.

The --scope flag describes what you need in plain language, and Seedfast works out how everything connects. You never sequence tables by hand, and the generated rows hold the same referential integrity your fixtures kept breaking.

Practical Example: E-Commerce Test Suite

Say you have a Playwright test suite for an e-commerce application. The tests cover:

User registration and login
Product browsing and search
Cart operations
Checkout flow
Order history

Each needs different data. The traditional route is five fixture files, ordered and maintained by hand. Generating it from the schema looks like this:

# test-setup.sh
#!/bin/bash
set -e

# Truncate all tables (clean slate)
psql $DATABASE_URL -c "TRUNCATE users, products, categories, orders, order_items, payments, cart_items CASCADE"

# Generate fresh data for this test run
seedfast seed --scope "seed 10 users, 50 products across 5 categories, 20 orders with order items and payments" --output plain

Now your Playwright tests query for data instead of assuming it:

// Instead of: cy.visit('/users/42/orders')
// Do this:

test('user can view their order history', async ({ page }) => {
  // Query for a user that has orders (generated data guarantees this)
  const response = await page.request.get('/api/users?has_orders=true&limit=1')
  const user = (await response.json())[0]

  await page.goto(`/users/${user.id}/orders`)
  await expect(page.getByTestId('order-row')).toHaveCount.above(0)
})

The test carries no hardcoded IDs and no assumption about order counts; it verifies that a user with orders can see their history, never a specific value.

Scenario-Specific Seeding

Different test files seed different scenarios. The scope is just a string; describe what you need:

# For testing empty states
seedfast seed --scope "3 users with no orders"

# For testing pagination
seedfast seed --scope "1 user with 50 orders"

# For testing search and filtering
seedfast seed --scope "100 products across 10 categories with varied prices from 5 to 500 dollars"

# For testing admin dashboards
seedfast seed --scope "50 users with mixed roles: 2 admins, 5 managers, 43 regular users, each with activity logs"

Each scope produces data matching your current schema. You never spell out column names, types, or foreign-key values; the schema defines them and Seedfast fills the rows.

CI/CD Integration: Seed, Test, Clean

This fits a CI pipeline, the same pattern as reseeding the CI database with synthetic data before every run:

name: E2E Tests

on: [pull_request]

jobs:
  e2e:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      - name: Apply migrations
        run: npm run db:migrate
        env:
          DATABASE_URL: postgres://test:test@localhost:5432/testdb

      - name: Seed test data
        run: seedfast seed --scope "10 users with orders, products across categories, and reviews" --output plain
        env:
          SEEDFAST_API_KEY: ${{ secrets.SEEDFAST_API_KEY }}
          DATABASE_URL: postgres://test:test@localhost:5432/testdb

      - name: Run E2E tests
        run: npx playwright test
        env:
          DATABASE_URL: postgres://test:test@localhost:5432/testdb

      - name: Upload test report
        if: failure()
        uses: actions/upload-artifact@v4
        with:
          name: playwright-report
          path: playwright-report

The pipeline stays simple, applying the current schema, generating matching data, and running the tests. When a migration changes the schema the generated data changes with it, so the pipeline stays green without a fixture PR. The CI/CD database seeding guide covers the full setup, and the service-container and health-check mechanics behind that YAML get their own walkthrough.

For isolation between test files, truncate and reseed between groups:

// playwright.config.js
import { defineConfig } from '@playwright/test'

export default defineConfig({
  globalSetup: './tests/global-setup.ts',
  globalTeardown: './tests/global-teardown.ts',
})


// tests/global-setup.ts
import { execSync } from 'child_process'

export default async function globalSetup() {
  // Truncate and reseed before the entire suite
  execSync('psql $DATABASE_URL -c "TRUNCATE users, orders, products CASCADE"', {
    stdio: 'inherit',
  })
  execSync(
    'seedfast seed --scope "10 users with orders and reviews, 50 products across 5 categories" --output plain',
    { stdio: 'inherit' }
  )
}

Seeding runs once for the whole suite and does not repeat before each test. A run takes seconds, sometimes a few minutes on larger volumes or schemas, which suits a per-suite setup step but would drag before every test. Structure your tests to read whatever data is present, and per-test seeding never comes up.

Factory Pattern vs. Fixture Files vs. Generated Data

Three approaches are common, each with trade-offs.

Dimension	Fixture files	Factory pattern	Generated data (Seedfast)
Maintenance on migration	Manual edit every change	Update the factory every change	Zero — re-reads the schema
Schema drift	Drifts silently	Drifts less, still drifts	None — schema is the source
Foreign-key handling	Manual ordering	Coded per relationship	Automatic dependency order
Test isolation	Poor (shared state)	Good (per-test data)	Good (fresh per run)
Determinism	High (fixed values)	High (seeded sequences)	Low — assert on shape, not values
Setup speed	Milliseconds	Milliseconds	Seconds (network call)
Scales past ~20 tables	No	Painful	Yes

Fixture Files (JSON, SQL, YAML)

// fixtures/users.json
[
  { "id": 1, "email": "admin@test.com", "role": "admin", "department_id": 1 },
  { "id": 2, "email": "user@test.com", "role": "user", "department_id": 2 }
]

Pros: They’re simple to understand, deterministic, and fast to load.

Cons: They drift from the schema and need manual maintenance on every migration; hardcoded IDs create coupling, foreign keys have to be ordered by hand, and the approach stops scaling past a dozen tables.

Breaks when: Any schema change that adds a NOT NULL column, moves a foreign key, or alters a constraint will break it, as will two fixture files that disagree about shared state (e.g., both reference department ID 1 but expect different names).

Factory Pattern (Factory Bot, Fishery, test containers)

// factories/user.js
import { Factory } from 'fishery'

const userFactory = Factory.define(({ sequence }) => ({
  id: sequence,
  email: `user-${sequence}@test.com`,
  name: `User ${sequence}`,
  role: 'user',
  department_id: departmentFactory.create().id,
}))

Pros: Being programmatic, factories handle relationships and let each test build its own data, which keeps isolation clean.

Cons: You write and maintain one per model, updating it on every schema change. Complex relationships (polymorphic associations, multi-level nesting) get messy, and the factory code is one more representation of your schema that can drift.

Breaks when: Factories fall out of sync with migrations, though less often than fixtures since the code sits closer to the model. Tables without a corresponding model (join tables, audit logs, materialized views) still slip through.

AI-Generated Data (Seedfast)

seedfast seed --scope "10 users with orders across 5 product categories"

Where a tool like this lands among its AI-labeled peers is what the best AI test data generator comparison sorts out.

Pros: Maintenance drops to zero because it reads the actual schema and handles foreign keys, constraints, and ordering on its own; schema changes appear on the next run, and it works at any table count.

Cons: Its data is non-deterministic, so assertions check structural properties, looking for “user has at least one order” and never “user 42 has order 101”. It needs a running database and network access to Seedfast’s backend, and loads slower than a fixture file (seconds vs. milliseconds). On a sensitive codebase, note that generating the data sends your table and column definitions (never the row values) to an external AI service, so a regulated project should clear that path against its data-governance policy first.

Breaks when: It breaks if Seedfast’s backend goes unavailable, though API-key caching and retry logic soften that. The bigger cost is restructuring an existing suite around data-shape assertions, which is genuine migration work.

When to Use What

Unit tests with no database — Factory pattern or in-memory fakes

Integration tests (few tables) — Factory pattern

E2E tests (full schema) — Generated data

Performance/load testing — Generated data at scale

Existing suite with hundreds of fixture files — Gradual migration, with generated data for new tests and factories for legacy

The approaches aren’t mutually exclusive; many teams pair factories for unit and integration tests with generated data for E2E and performance runs.

Making Tests Data-Shape-Aware

The biggest shift is how tests reference data. Where a fixture-bound test asserts on known values, a data-shape test queries for records with the properties it needs.

Before (fixture-dependent):

test('admin can delete a user', async ({ page }) => {
  // Assumes fixture user with ID 1 is an admin
  await loginAs(1)
  // Assumes fixture user with ID 2 exists and is deletable
  await page.goto('/admin/users/2')
  await page.click('[data-testid="delete-user"]')
  await expect(page.getByText('User deleted')).toBeVisible()
})

After (data-shape-aware):

test('admin can delete a user', async ({ page, request }) => {
  // Find any admin user
  const admins = await request.get('/api/users?role=admin&limit=1')
  const admin = (await admins.json())[0]

  // Find any non-admin user
  const users = await request.get('/api/users?role=user&limit=1')
  const targetUser = (await users.json())[0]

  await loginAs(admin.id)
  await page.goto(`/admin/users/${targetUser.id}`)
  await page.click('[data-testid="delete-user"]')
  await expect(page.getByText('User deleted')).toBeVisible()
})

The second version is more resilient. It doesn’t care which user happens to be the admin or which one gets deleted; it tests the behavior that an admin can delete a non-admin user, and that holds up through schema changes, data reseeds, and parallel execution.

Yes, it’s more code, and it earns its keep — the old version only ever proved that one specific sequence of clicks kept working against one specific dataset, which is a much smaller promise than the feature actually makes.

A Pragmatic Migration Path

If your E2E suite is built on fixtures, you don’t need to rewrite it all at once:

Week 1: Add a seeding step to CI alongside your existing fixtures, and run both together.

- name: Load legacy fixtures
  run: psql $DATABASE_URL -f fixtures/seed.sql

- name: Seed additional data
  run: seedfast seed --scope "seed 20 extra users with varied roles and order histories" --output plain
  env:
    SEEDFAST_API_KEY: ${{ secrets.SEEDFAST_API_KEY }}

Week 2-4: Write new tests using the data-shape-aware pattern, and leave the old ones alone for now.

Month 2: Find the fixture files that break most often (git blame shows which get updated every sprint) and migrate those first.

Month 3+: As old fixture-dependent tests break from schema changes, rewrite them with the generated pattern and drop the fixture.

Over time the fixture directory shrinks. New tests skip it, old ones migrate out as they break, and one seedfast seed command becomes the only setup left.

What This Gets You

Fewer flaky tests is what you notice first; underneath, your team’s whole approach to test data changes.

Schema changes stop breaking tests. A migration adds a column, the next run already includes it, and no one files a fixture-update PR to catch up.

Test isolation becomes the default. Every CI run starts from a fresh database with no shared state to inherit, so the suite parallelizes cleanly.

New developers can run the suite immediately. Nobody hands them a database dump or a seed script from someone’s head; they clone, run migrations, seed, and it’s ready.

Your tests end up documenting the feature itself. A test that reads “a user with orders can view their order history” checks that claim against any valid data, and at that point the test reads as a small specification of the feature.

Frequently asked questions

Why do my Playwright or Cypress tests fail after a database migration?

The fixture they load no longer matches the migrated schema. Nothing flags a checked-in file when it falls behind, so the failure surfaces as an insert error or an unexpected null. Look in the fixture file, well upstream of the assertion that fails.

What is the difference between test fixtures, factories, and generated test data?

Fixtures are static files (JSON, SQL, YAML) you edit by hand on every schema change, while a factory keeps that record-building in code and still needs updating whenever a migration lands. Generated data reads the live schema at run time, picking up changes automatically with no upkeep, at the cost of being non-deterministic.

How do you generate test data for E2E tests without fixture files?

Point a generation step at the schema so there’s no fixture file in the loop. Seedfast reads the live tables and inserts rows in the order the foreign keys require, so one seed step replaces the whole fixture directory.

How do you keep E2E tests isolated so they can run in parallel?

Give each run its own fresh data so nothing is shared between files, and drop the ordering assumptions that make one test wait on another. When every test sets up, runs, and tears down its own records, nothing is left to fight over, and parallel execution follows.

Can you seed a test database in a CI pipeline?

Yes, a seed step goes in after migrations and before the test run in GitHub Actions, GitLab CI, or any pipeline, with the CLI pointed at the throwaway CI database and a non-interactive flag so nothing waits on input. Because it calls an external service, regulated teams should clear it with whoever owns data-governance policy first.

Should you use a production database dump for E2E test data?

Usually not, for the two reasons production dumps are risky anywhere. The moment a dump leaves production it carries real customer records into an environment with weaker access controls, and it starts aging as soon as the schema shifts. Generating data from the schema avoids both, with no real rows to guard and nothing to go stale.

Related guides

Small Data, Big Lies: 6 Bugs Only Real Test Data Catches — why a tiny fixture database hides the failures that only appear with realistic data
Your Staging Database Is a Compliance Violation Waiting to Happen — generating realistic data instead of copying and anonymizing a production dump
ALTER TABLE, 5 Million Rows, and the Deploy That Took Down the Site — the other side of schema change: testing migrations against production-scale data
Review SQL Migrations in 30 Seconds — catching the data-shape bugs a migration introduces before they reach your tests
PostgreSQL Test Data: A Syntax Cookbook — the raw SQL patterns behind generating volume and realistic distributions
Database Seed File Maintenance: Stop Patching seed.sql — the same drift problem on the seed-script side instead of the fixture side
Data Seeding Tools Compared — where fixtures, factories, and schema-aware generators fit among the other approaches
Agentic QA Test Data — why even AI agents that write and run your E2E tests still don't generate the data underneath them
Pytest Database Fixtures That Stay Fast and Honest — the backend/integration-test counterpart to this page: fixture scopes, the SQLAlchemy SAVEPOINT rollback pattern, and seeding a Postgres baseline once for pytest
Get started with Seedfast — connect a database and seed it for your next test run

Generate E2E test data straight from your schema

Get Started | Documentation | Pricing

Point Seedfast at the schema once, and keeping test fixtures in sync stops being your team’s problem.

Originally published at seedfa.st.

Seed an Empty Postgres Database Without Leaving Claude Code

Mikhail Shytsko — Fri, 14 Aug 2026 22:33:46 +0000

You have a feature branch open in Claude Code and the integration tests are ready to run, but the dev database behind them is empty. Claude Code MCP database seeding closes that last gap without a detour, letting the agent already sitting in your editor fill the schema with valid rows before a single test fires. Ask the model to write a seed script instead and you tend to get inserts whose foreign keys point at rows that were never created, a failure the generate test data with AI playbook walks through in depth.

This page stays deliberately narrow. One config block, one prompt, and an empty Postgres database comes back populated, all from the tool you already keep open. I run this a dozen times a day against branch databases, so what follows is the exact path from zero to seeded, not the full map of MCP servers for test data you could wire up instead.

TL;DR: Seedfast runs as an MCP server, so an agent inside Claude Code populates a Postgres database in one call. It reads your live schema, generates relational rows with valid foreign keys, and writes them back, without you leaving the editor.

How Claude Code MCP database seeding works

When Claude Code starts, it reads your project's .mcp.json and launches the Seedfast server through npx, which then advertises a small set of tools over the Model Context Protocol. The one that matters here is seedfast_run. You hand the agent a scope in plain English and it forwards that scope to seedfast_run. From there the engine reads your live Postgres schema and generates data that's valid and connected before it writes a single row, so nothing ends up pointing at a row that isn't there.

That division of labor keeps the data coherent, since the agent stays in the part it does well, describing intent and reasoning about your feature, and hands the relational bookkeeping to code that reads the real schema rather than guessing at it. Keeping every row valid, including tables that reference themselves or each other, belongs to the seeder, while enforcement of referential integrity never leaves the database. The deeper argument for why a language model loses the relational thread lives in the same playbook, so I won't relitigate it here.

Set up Seedfast in .mcp.json

Two things have to exist before the agent can seed anything, a Seedfast account and an API key, so log in at seedfa.st, open Settings → API Keys , and create one; it comes back in the sfk_live_... format. Treat that key like any other credential and keep the real value out of version control, because anything you commit to .mcp.json travels with the repo. Teams that check the file in usually commit it with the placeholder and paste the real key only into their local copies.

Add Seedfast to the project's .mcp.json at the repository root:

{
  "mcpServers": {
    "seedfast": {
      "command": "npx",
      "args": ["-y", "seedfast@latest", "mcp"],
      "env": {
        "SEEDFAST_API_KEY": "sfk_live_your_api_key_here"
      }
    }
  }
}

You'll need Node.js 18 or newer on the machine, since the server runs through npx. Restart Claude Code so it picks up the new config, then confirm the wiring by asking the agent to run seedfast_doctor, which reports whether the CLI is healthy and your key authenticated. If you're setting up Cursor, VS Code, or Claude Desktop instead, or you hit a snag along the way, the MCP setup guide carries the config for every client and the troubleshooting steps.

One prompt, a seeded database

With the server wired up, a real session reads like a sentence. Say you're building order history and the tests need customers who actually own something. You would type roughly this:

Seed customers with related orders and line items, a few rows each

Claude Code reads that, calls seedfast_run with the scope, and the engine seeds the three tables so every order points at a real customer and every line item at a real order. And because scope controls scale as much as shape, you ask for a few rows each when a unit test just needs something to assert against, and for thousands when you want to watch a query plan buckle under load.

How long it takes tracks how much you asked for. A single table lands in five to fifteen seconds, five to ten related tables in thirty to sixty, and a full schema in two to five minutes, so the order-history seed above finishes while you're still reading the test you're about to run. Once it's done, ask the agent for the run summary and it reports which tables succeeded along with the total rows written, or run a quick SELECT count(*) yourself and read the numbers straight from the database.

Point it at a dev database, never production

There is an honest worry sitting underneath all of this. You are handing an agent a write path into a database, which is a reasonable thing to feel cautious about, so the single rule that keeps it boring is to point Seedfast at a dev or branch database only, the same discipline you would already apply to any seed script a teammate wrote. The blast radius stays small for a structural reason too, since Seedfast needs no production data to do its job; it reads the schema and writes fresh rows generated from scratch, so nothing about your real customer records has to come anywhere near the run.

That same seed call drops straight into CI, where the MCP wrapper gives way to the plain CLI. The pipeline version of the workflow, fresh rows for every pull request with no hand-maintained seed.sql in sight, has its own write-up in synthetic data for CI/CD.

Frequently asked questions

Does Claude Code support MCP servers like Seedfast?

Claude Code, Anthropic's agentic coding tool, supports MCP servers natively and reads their configuration from a project-scoped .mcp.json at the repository root. Once you add an mcpServers block naming the command to launch, Claude Code starts that server at boot and its tools, Seedfast's included, become available to the agent mid-conversation.

What can I ask Claude Code to seed with Seedfast?

Seedfast can seed anything from a single table to every table across every schema, described to Claude Code in plain language rather than SQL. A scope such as "seed customers with related orders and line items" populates one connected slice, while "seed all tables in all schemas" fills the whole database. Because the engine re-reads your live schema on each run, a table you added this morning flows through without extra wiring. Sensitive tables stay out when you name them as exclusions in the scope.

How long does a seed run take from Claude Code?

A seed run from Claude Code usually finishes in well under a minute, and even a full schema of fifty-plus tables stays inside a few minutes. Expect five to fifteen seconds when a single table is in scope and two to five minutes at the everything-at-once end, which is why scoping the seed to the feature at hand keeps the wait short.

Does the same MCP setup work in Cursor or VS Code?

Yes, the same server block works unchanged in Cursor, VS Code, Claude Desktop, and Claude Code, because each client launches Seedfast through the identical npx command. Only the file it lives in moves around, with Cursor reading .cursor/mcp.json and VS Code keeping the block in its own MCP settings. The MCP setup guide spells out the location for each client, and Codex CLI takes the same npx command in TOML form, covered in its own walkthrough.

Seed the empty database from where you already work

Seedfast turns the empty-database step into one line inside Claude Code, reading your live schema and writing valid, connected rows off it with no production data involved. Start on the free plan, no card required, and the first rows usually land a couple of minutes after the config block goes in.

Originally published at seedfa.st.

How to Seed a Neon Database: psql, Prisma, Drizzle

Mikhail Shytsko — Fri, 14 Aug 2026 22:33:11 +0000

To seed a Neon database, use the unpooled connection string with psql, Prisma, or Drizzle. The pooled URL breaks prepared statements mid-seed. Neon gives you an empty serverless Postgres in under a second; then you stare at it. This guide covers three ways to seed Neon database tables (psql, an ORM seed, and Seedfast, which reads your live Neon schema and generates FK-valid relational data from a plain-English scope with no seed scripts to maintain) plus how branching changes the seeding model.

Key Takeaways

If your seed throws prepared statement "s1" already exists or cached plan must not change result type, you're connecting through the pooled URL. Switch to the unpooled string (no -pooler in the hostname). PgBouncer transaction mode breaks prepared statements and times out long-running scripts
Neon's main branch starts empty. Every new project, every new branch, and every preview environment needs test data before the app is usable
Prisma and Drizzle both seed Neon fine, but Prisma on edge runtimes requires the @prisma/adapter-neon adapter plus ws; the standard pg driver won't work there
Neon branching inherits data from the parent branch, so seeding once at the parent lets preview branches start with that dataset, ready in about a second
When the schema changes, and it will, static seed files break. Seedfast notices the change on its next run and regenerates valid data, so there is no seed file to maintain

Quick fix if you landed here from a broken seed:

# Pooled URL — breaks seeding (PgBouncer transaction mode)
postgresql://user:pass@ep-xxxx-pooler.region.aws.neon.tech/dbname?sslmode=require

# Unpooled URL — use this for seeds, migrations, admin scripts
postgresql://user:pass@ep-xxxx.region.aws.neon.tech/dbname?sslmode=require

You provisioned Neon because it spins up in milliseconds, scales to zero, and lets you branch databases per pull request. But your main branch is empty, and your CI branches inherit that emptiness. To seed a Neon database, you need three things: the right connection string, a strategy that survives schema changes, and an understanding of how Neon branching changes the seeding model.

We'll cover all three, starting with raw SQL, then Prisma and Drizzle seed scripts with Neon's serverless driver, then how Seedfast turns a plain-English scope into connected rows without a seed file to write. If you want the general PostgreSQL version first, how to seed a database covers the cross-framework fundamentals; this article is specifically about Neon.

When do you need to seed a Neon database?

Neon's serverless Postgres starts life as an empty database. Unlike a shared dev server that accumulates data over months, a Neon project is fresh. Branches are fresh too. By default they copy from a parent, so if the parent is empty, the branch is empty. This matters more on Neon than on traditional Postgres hosting because the branching workflow puts a new database in front of you on every pull request.

Three scenarios force the question of how to seed a Neon database:

New project onboarding. A developer clones the repo, creates a Neon project, and runs the migrations; the tables exist, but nothing else does.
Preview branches per PR. The GitHub Action creates a branch for every pull request, and if the parent is empty, every preview is empty too, which means your end-to-end tests hit 404s.
Staging and demo environments. You need 500 products and realistic order histories to show someone what the app looks like with data in it.

Neon's branching docs push branching over seeding, arguing for forking from a parent that already has data so every child branch inherits it automatically. That's elegant, but it leaves the parent-seeding problem unsolved. Someone still has to fill the parent branch the first time.

Connection strings: pooled, unpooled, and when each matters

Neon gives every branch two connection strings. You can copy them from the Neon dashboard under Connection Details :

# Pooled (goes through PgBouncer, up to 10,000 concurrent connections)
postgresql://user:pass@ep-xxxx-pooler.region.aws.neon.tech/dbname?sslmode=require

# Unpooled / direct (straight to Postgres)
postgresql://user:pass@ep-xxxx.region.aws.neon.tech/dbname?sslmode=require

Note the -pooler in the pooled hostname. That's PgBouncer in transaction mode, which returns connections to the pool after every transaction. It's what you want for a serverless Next.js app handling thousands of short requests. It's not what you want for seeding, because:

Prepared statements don't survive the transaction boundary
Long-running COPY operations and large transactional seeds can hit statement timeouts
Some ORMs emit session-level SET statements that get discarded

For seeding, migrations, and any admin script, use the unpooled string. Export it explicitly:

# .env.local
DATABASE_URL="postgresql://...ep-xxxx-pooler.../dbname?sslmode=require" # app runtime
DIRECT_URL="postgresql://...ep-xxxx.../dbname?sslmode=require" # migrations + seeds

Prisma calls this directUrl in schema.prisma, while Drizzle doesn't care and just wants whichever URL matches your context. If your seed inserts 10,000 rows and fails halfway with "prepared statement already exists" or "cached plan must not change result type", you're seeding through the pooler, so switch to the unpooled URL.

Method 1: Seed Neon database with raw SQL

The simplest thing that works is writing INSERTs and running them with psql.

-- seed.sql
INSERT INTO teams (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Design')
ON CONFLICT (id) DO NOTHING;

INSERT INTO users (id, email, team_id) VALUES
  (1, 'alice@example.com', 1),
  (2, 'bob@example.com', 2)
ON CONFLICT (id) DO NOTHING;


psql "$DIRECT_URL" -f seed.sql

SSL is mandatory on Neon. The sslmode=require query parameter in the connection string handles it automatically. If you get FATAL: connection requires SSL, your URL is missing it.

When you have tens of thousands of rows to load, COPY FROM STDIN is substantially faster than row-by-row INSERTs. The gap narrows when comparing against batched multi-row INSERTs, but COPY still avoids per-row parsing overhead:

psql "$DIRECT_URL" -c "COPY products (name, price, category_id) FROM STDIN CSV" < products.csv

ON CONFLICT DO NOTHING keeps the seed idempotent so CI reruns don't fail on the second attempt. For data that should reflect the latest values, use ON CONFLICT DO UPDATE:

INSERT INTO feature_flags (key, enabled) VALUES
  ('new_checkout', true)
ON CONFLICT (key) DO UPDATE SET enabled = EXCLUDED.enabled;

Raw SQL is fine for reference data like roles, feature flags, and country codes. Past ten tables with foreign keys, though, it starts breaking, because every migration that adds a required column or a new FK reference forces you to hand-edit the seed file. Seed file maintenance covers why this lifecycle is so brutal on active codebases.

Method 2: Seed Neon database with an ORM

Most Neon projects run through Prisma, Drizzle, or Kysely. Each has its own seeding path.

Prisma + Neon

Prisma on a Node.js server works with Neon out of the box via the standard pg driver. Edge runtimes (Vercel Edge, Cloudflare Workers) require the Neon serverless driver adapter.

In a regular Node.js seed script, just point Prisma at the unpooled URL:

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  const team = await prisma.team.upsert({
    where: { name: "Engineering" },
    update: {},
    create: { name: "Engineering" },
  });

  await prisma.user.upsert({
    where: { email: "alice@example.com" },
    update: {},
    create: { email: "alice@example.com", teamId: team.id },
  });
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());


// prisma.config.ts
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
    seed: "tsx prisma/seed.ts",
  },
  datasource: { url: env("DIRECT_URL") },
});


npx prisma db seed

If you deploy on Vercel Edge or Cloudflare Workers, the runtime has no TCP sockets. Install the Prisma Neon adapter:

npm install @prisma/adapter-neon @neondatabase/serverless ws


import { PrismaClient } from "@prisma/client";
import { PrismaNeon } from "@prisma/adapter-neon";
import { Pool, neonConfig } from "@neondatabase/serverless";
import ws from "ws";

neonConfig.webSocketConstructor = ws;
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const adapter = new PrismaNeon(pool);
const prisma = new PrismaClient({ adapter });

Seeding itself rarely runs on edge. It runs in CI or locally, so the plain Node.js setup with the unpooled URL is what most teams use for the seed script.

Drizzle + Neon

Drizzle ships two Neon-specific packages: drizzle-orm/neon-http for one-shot HTTP queries, which suits serverless app code but not seed transactions, and drizzle-orm/neon-serverless for WebSocket sessions with full transaction support, the one you want for seeds.

The simplest seed path is the node-postgres driver against the unpooled URL:

// scripts/seed.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { teams, users } from "../src/db/schema";

const pool = new Pool({ connectionString: process.env.DIRECT_URL });
const db = drizzle(pool);

async function seed() {
  const [team] = await db
    .insert(teams)
    .values({ name: "Engineering" })
    .onConflictDoUpdate({ target: teams.name, set: { name: "Engineering" } })
    .returning();

  await db
    .insert(users)
    .values({ email: "alice@example.com", teamId: team.id })
    .onConflictDoNothing();

  await pool.end();
}

seed().catch((e) => {
  console.error(e);
  process.exit(1);
});


tsx scripts/seed.ts

If you prefer the Neon serverless driver for consistency with the rest of your codebase:

import { drizzle } from "drizzle-orm/neon-serverless";
import { Pool, neonConfig } from "@neondatabase/serverless";
import ws from "ws";

neonConfig.webSocketConstructor = ws;
const pool = new Pool({ connectionString: process.env.DIRECT_URL });
const db = drizzle(pool);

Either works, since the pg version is one less dependency while the serverless-driver version keeps your app and seed script on the same driver.

Both Prisma and Drizzle share the same ORM seed problem, since the values are hand-written. When a migration adds a NOT NULL organization_id column, your seed breaks on the next run, and someone (usually the person on call) has to fix it before anyone on the team can run the app. Seedfast removes that break-on-migration step entirely, picking up the new column automatically and filling it without a seed-file edit.

Using @neondatabase/serverless with local Postgres for development

@neondatabase/serverless cannot talk to a regular local Postgres on localhost:5432. The HTTP driver (neon()) speaks HTTP to Neon's proxy, and the WebSocket driver (Pool from @neondatabase/serverless) speaks WebSocket to Neon's serverless gateway. Because plain Postgres only understands the binary wire protocol on TCP, the connection fails before any query runs.

This is the standard combination for apps deployed on Vercel Edge Functions or Cloudflare Workers against Neon, where production runs on the edge with the serverless driver while local development wants plain Postgres in Docker. There are two ways out. Swap the Drizzle driver per environment (recommended), or run a local WebSocket proxy that translates for @neondatabase/serverless.

Solution 1: swap drivers per environment (drizzle-orm/node-postgres locally, neon-http in production)

Keep drizzle-orm/neon-http (or drizzle-orm/neon-serverless) for production and use drizzle-orm/node-postgres against local Postgres. Drizzle's schema, types, and query API are identical across drivers, so only the file that constructs db changes.

// src/db/index.ts
import { drizzle as drizzleNeon } from "drizzle-orm/neon-http";
import { drizzle as drizzlePg } from "drizzle-orm/node-postgres";
import { neon } from "@neondatabase/serverless";
import { Pool } from "pg";
import * as schema from "./schema";

const connectionString = process.env.DATABASE_URL!;

export const db =
  process.env.NODE_ENV === "production"
    ? drizzleNeon(neon(connectionString), { schema })
    : drizzlePg(new Pool({ connectionString }), { schema });

Local .env:

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/postgres"

Production .env:

DATABASE_URL="postgresql://user:pass@ep-xxxx.region.aws.neon.tech/dbname?sslmode=require"

Your schema file and every query (db.select().from(users)…) stays the same. The only divergence is the driver factory. If you need transactions locally, node-postgres already supports them. neon-http does not, so any code that uses db.transaction(...) either has to live behind a server action that runs on Node, or you switch the production driver to drizzle-orm/neon-serverless (WebSocket, transactions supported on the edge).

It comes down to one process-env flip and two factories, and the query code stays identical either way.

Solution 2: local WebSocket proxy for @neondatabase/serverless

If you need a single code path that uses @neondatabase/serverless everywhere, run a local WebSocket-to-Postgres proxy. Neon's wsproxy accepts WebSocket connections on a local port and forwards them to a real Postgres instance, so the serverless driver thinks it's talking to Neon.

Minimal docker-compose.yml:

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: postgres
    ports: ["5432:5432"]

  wsproxy:
    image: ghcr.io/neondatabase/wsproxy:latest
    environment:
      APPEND_PORT: "postgres:5432"
      ALLOW_ADDR_REGEX: ".*"
    ports: ["4444:80"]
    depends_on: [postgres]

Point @neondatabase/serverless at the local proxy:

import { Pool, neonConfig } from "@neondatabase/serverless";
import ws from "ws";

neonConfig.webSocketConstructor = ws;
neonConfig.wsProxy = (host) => `localhost:4444/v2`;
neonConfig.useSecureWebSocket = false;
neonConfig.pipelineTLS = false;
neonConfig.pipelineConnect = false;

const pool = new Pool({
  connectionString: "postgresql://postgres:postgres@localhost:5432/postgres",
});

The trade-off is an extra container, an extra protocol to debug when something breaks, and a second handshake on every connection. Solution 1 needs one env check; Solution 2 needs a running container and a WebSocket connection to inspect. Reach for the proxy only if you need exact edge parity in local dev, for example hunting a bug that only reproduces over WebSocket.

drizzle-kit push local postgres "neon serverless" websocket warning

drizzle-kit push and drizzle-kit migrate always use node-postgres under the hood. The warning means your drizzle.config.ts is pointing at a Neon serverless or pooled URL, and drizzle-kit is telling you it will ignore the serverless bits and connect over plain TCP. Giving it a pg-compatible URL fixes it, Neon's unpooled direct connection for production and plain local Postgres for development.

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url:
      process.env.DATABASE_URL_UNPOOLED || "postgresql://postgres:postgres@localhost:5432/postgres",
  },
});

For Neon, DATABASE_URL_UNPOOLED is the hostname without -pooler and with ?sslmode=require. Locally, plain postgresql://postgres:postgres@localhost:5432/postgres is enough. Do not try to silence the warning by setting driver: 'pg' plus a serverless URL. The underlying mismatch is the connection string, not the config key. (Drizzle-kit removed the driver field altogether in 0.21+, making the workaround a no-op on newer versions.)

drizzle-orm/neon-http vs drizzle-orm/neon-serverless vs drizzle-orm/node-postgres

Three drivers speak three different wire protocols behind one Drizzle query API.

Driver	Wire protocol	Transactions	Runtime	Works against
`drizzle-orm/neon-http`	HTTPS to Neon proxy	No	Node + edge	Neon only
`drizzle-orm/neon-serverless`	WebSocket to Neon	Yes	Node + edge	Neon only
`drizzle-orm/node-postgres`	TCP wire protocol	Yes	Node only	Any Postgres (including local)

Pick by deployment target:

Edge with one-shot queries, no transactions: use neon-http for the lowest latency on a single read.
Edge with transactions: use neon-serverless to keep a WebSocket session open, matching the edge-runtime story.
Local development, CI Postgres in Docker, or any non-Neon host: use node-postgres, since the serverless drivers won't connect here.

The conditional-import pattern from Solution 1 above lets you mix and match: node-postgres locally for pnpm dev, neon-http or neon-serverless for the deployed app.

Method 3: Schema-aware seeding with Seedfast

Seedfast connects to your Neon branch, reads the live schema (tables, columns, constraints, foreign keys) and generates a valid, connected dataset. You describe what the data should look like in plain English. There's no seed.sql or seed.ts to maintain.

npm install -g seedfast
# or: brew install argon-it/tap/seedfast

# Log in and connect to your Neon database
seedfast connect
# Paste the Neon unpooled connection string when prompted

# Generate data from the current schema
seedfast seed --scope "small engineering team with 3 projects and task assignments"

When a migration adds a new table or column, the next seedfast seed picks it up automatically, with no file to update and no foreign key order to work out by hand.

In our internal runs, Seedfast generates around a million FK-valid rows into a typical 20-table SaaS schema in roughly three and a half minutes.

Different scopes for different environments:

# Local dev — minimal, fast
seedfast seed --scope "2 teams, 5 users, 10 products"

# Preview branch for a feature PR
seedfast seed --scope "3 users with completed onboarding, 5 draft posts, 2 published"

# Staging demo
seedfast seed --scope "500 realistic products, 50 users with 6 months of order history"

Seedfast works alongside Prisma, Drizzle, Kysely, and plain pg because it talks to PostgreSQL directly over the wire. Run your migrations first with whichever tool you prefer, then run Seedfast to fill the tables.

For production reference data (feature flags, country codes, admin roles) you still want a versioned SQL file or ORM seed, because that data belongs to the application, not a test dataset. Seedfast is built for development, CI, staging, and demo data; the handful of rows that actually ship to production still belongs in that versioned file.

Generated rows land straight in your Neon branch, with no CSV to import, no intermediate file to load, and no manual psql step at the end. Run the seed command once and the tables are full. See data handling and privacy for exactly what crosses the wire.

A free plan is available. Connect and run your first seed in about two minutes.

Generate test data for a Neon branch (that survives a branch reset)

After a branch reset, the thing that has to survive is the generator, not a git-tracked seed file. A schema-aware test data generator for Neon regenerates from the branch's current schema on every run, so repopulating means running the generator again instead of restoring a file. Neon lets you instantly reset a branch to its parent, which wipes every branch-local write. After a reset, you have to repopulate. With a static seed.sql that's a psql run; with a hand-edited ORM seed it's a psql run plus whatever migrations have landed since you last touched the file.

Two things make a Neon branch different from a plain database, and both favor reading the schema over replaying a file:

A branch created off main inherits main's schema at fork time, but if a migration lands on the branch in the same PR, the branch's tables no longer match the seed file written against main. Regenerating against the branch's own schema sidesteps that, since Seedfast targets the branch's real tables, columns, and FKs, not the ones from yesterday. And because a reset throws away branch-local data, the cheap loop becomes resetting the branch and rerunning the generator, instead of resetting the branch and then reviewing a diff on the seed file. Re-run the same scope against the branch's unpooled URL (the install and seedfast connect steps are in Method 3 above):

# After resetting the branch — repopulate from its current schema
seedfast seed --scope "3 users with completed onboarding, 5 draft posts, 2 published"

Keeping neon branch seed data current becomes a property of the run instead of something stored in a file you maintain. Static psql/ORM seeds are still the right call for versioned reference rows like feature flags and country codes, because those belong in git and should survive a reset by being committed. For the deeper per-PR branching workflow (seed-parent-once, drift-reseed, schema-only rescue), see Neon branch seeding; for the broader tool decision across Postgres hosts, the best Postgres test data generator comparison weighs the options.

Seed Neon once, branch many times

Neon branching turns one seeded parent into many populated branches, since you seed the parent once and every branch forked from it inherits that dataset in milliseconds. Keeping that parent dataset current across schema changes is where Seedfast fits. Every run starts from the parent's schema as the migrations have left it, so parent data and schema never drift apart.

A typical workflow looks like:

# One-time setup on main branch
psql "$DIRECT_URL" -f seed.sql
# or
seedfast seed --scope "realistic e-commerce dataset"

Then in CI, every pull request gets its own branch:

# .github/workflows/preview.yml
name: Preview branch

on:
  pull_request:

jobs:
  preview:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Create Neon branch from main
        id: neon
        uses: neondatabase/create-branch-action@v6
        with:
          project_id: ${{ secrets.NEON_PROJECT_ID }}
          branch_name: preview/pr-${{ github.event.pull_request.number }}
          parent_branch: main
          api_key: ${{ secrets.NEON_API_KEY }}

      - name: Run migrations on the new branch
        run: npx prisma migrate deploy
        env:
          DIRECT_URL: ${{ steps.neon.outputs.db_url }}

      - name: Run E2E tests
        run: npm run test:e2e
        env:
          DATABASE_URL: ${{ steps.neon.outputs.db_url_pooled }}

There's no seeding step in CI, because the branch already has data, inherited from main. Branch creation takes about a second, so the PR pipeline isn't waiting on database provisioning.

When the PR is closed or merged, a cleanup action deletes the branch:

# .github/workflows/cleanup.yml
on:
  pull_request:
    types: [closed]

jobs:
  delete-branch:
    runs-on: ubuntu-latest
    steps:
      - uses: neondatabase/delete-branch-action@v3
        with:
          project_id: ${{ secrets.NEON_PROJECT_ID }}
          branch: preview/pr-${{ github.event.pull_request.number }}
          api_key: ${{ secrets.NEON_API_KEY }}

The two pieces fit together when you seed the parent with a realistic dataset, then branch per PR for isolated preview environments. The only thing you have to get right is keeping the parent's seed data current, and that's exactly what Seedfast does, rebuilding the parent dataset each time from the schema the migrations have already produced.

For the general CI case without branching, the approach in CI/CD database seeding applies to Neon too.

Common Neon seeding issues and how to fix them

"prepared statement `s1` already exists"

You're seeding through the pooled (-pooler) connection string. PgBouncer transaction mode discards prepared statements between transactions, and the driver tries to reuse a statement that's gone. Switch to the unpooled URL for the seed script.

"cached plan must not change result type"

This is a server-side Postgres plan-cache error, where the database cached the execution plan for a prepared statement, but a subsequent schema change (column type, table restructure) invalidated it. Run migrations before the seed, and make sure you are using the unpooled URL. PgBouncer can mask this error by discarding statement state between transactions rather than surfacing it cleanly.

"connection terminated unexpectedly" mid-seed

Neon compute auto-suspends branches that have been idle (default is 5 minutes on all plans, including paid). If your seed script pauses between large batches, the connection may close before you resume. For long seeds, keep the script running continuously or increase the compute's suspend delay in the Neon dashboard.

"SSL required"

Your connection string is missing ?sslmode=require, and Neon rejects non-SSL connections outright. Add the query parameter or set PGSSLMODE=require in the environment.

"permission denied for schema public"

Neon projects have one default role, the owner role (named for your database, e.g., neondb_owner), which has full access. If you are using a role you created separately with limited grants, your seed INSERTs will fail. Use the project owner role for seeding.

"too many connections"

You've opened more connections than the compute allows. The limit scales with compute size (a 0.25 CU Neon compute supports around 104 total connections; a 1 CU supports 419, per Neon's compute docs). Close pools after seeding (pool.end() for pg / Drizzle, prisma.$disconnect() for Prisma). For parallel seed scripts, serialize them or lower the pool size (max: 5 in new Pool(...)). Use the direct URL for the seed process only.

"relation does not exist"

Run migrations before seeding. Neon branches copy data from the parent, but if the parent hasn't had migrations applied, the schema is stale. The order is always migrate → seed; it never runs in reverse.

Manual SQL vs ORM vs Seedfast for Neon

Aspect	Raw SQL (`psql -f`)	Prisma / Drizzle seed	Seedfast
Setup time	None	Already there if you use the ORM	`npm install -g seedfast`
External dependency	None — `psql` is everywhere	None — already in your stack	Yes — separate CLI plus a network call to your DB
File you maintain	`seed.sql`	`seed.ts`	None — reads the live schema
FK order	Manual	Manual	Automatic
Survives migrations	No — requires manual updates on schema changes	No — requires manual updates on schema changes	Yes — regenerates from the live schema
Realistic volumes	Painful beyond ~50 rows	Works with Faker, still manual	Natural-language scope describes the dataset
Works with Neon branches	Yes (unpooled URL)	Yes (unpooled URL)	Yes (unpooled URL)
Good for static config (feature flags, country codes, roles)	Excellent — versioned and reviewed	Excellent — versioned and reviewed	Not the target
Good for dev / CI / staging datasets	Manual re-sync on every migration	Manual re-sync on every migration	Regenerates from the live schema

Pick based on the job. Ship reference data as committed SQL or ORM seed. Use Seedfast for the large, evolving test datasets that are the actual source of seed-file pain. Try it free on your Neon schema, which takes about two minutes and never asks for a credit card.

Frequently asked questions

How do I seed a Neon database from the command line?

Copy the unpooled connection string from the Neon dashboard (the hostname without -pooler) and run psql "$DATABASE_URL" -f seed.sql. Include ?sslmode=require on the connection string. For Prisma projects, npx prisma db seed runs your prisma/seed.ts. For schemas with many tables or foreign keys, seedfast seed --scope "..." generates connected data without a seed file.

Should I use the pooled or unpooled Neon URL for seeding?

Use the unpooled URL for seeding, migrations, and admin scripts. The pooled URL routes through PgBouncer in transaction mode, which breaks prepared statements and can time out on large transactions. Use the pooled URL for your application at runtime, where short-lived transactions benefit from the pool.

Do Neon branches inherit seed data from the parent?

Yes by default. When you create a branch, Neon copies both the schema and the data from the parent branch. That means you can seed your main branch once and every preview branch forked from it starts with that dataset. Neon also supports schema-only branching if you want the structure without the data.

How do I seed a Neon database in GitHub Actions?

Create the branch with neondatabase/create-branch-action, run migrations against the branch's direct URL, then run your seed script. If the parent branch is already seeded, you can skip the seeding step, since the branch inherits the data. Use the pooled URL for application queries in your tests and the direct URL for migrations and seeds.

How do I seed a Neon database with Prisma?

Set directUrl in schema.prisma to Neon's unpooled connection string, keep url pointing at the pooled one for app queries, write your prisma/seed.ts, and run npx prisma db seed. If you deploy on an edge runtime, also install @prisma/adapter-neon, @neondatabase/serverless, and ws. But seeding itself usually runs in Node.js, not edge, so the adapter isn't required for the seed script.

How do I seed a Neon database with Drizzle?

Use drizzle-orm/node-postgres with the pg driver and Neon's unpooled URL for the seed script. For serverless/edge app code, switch to drizzle-orm/neon-http (one-off queries) or drizzle-orm/neon-serverless (transactions over WebSocket). Seedfast can also seed Drizzle-managed schemas directly, since it talks to Postgres instead of going through the ORM.

Can I use @neondatabase/serverless with local Postgres for development?

No. The @neondatabase/serverless package speaks HTTP (neon()) or WebSocket (Pool) to Neon's gateway, not the regular Postgres wire protocol. A plain Postgres at localhost:5432 will not accept either connection. Use drizzle-orm/node-postgres with the pg driver against local Postgres and keep drizzle-orm/neon-http or drizzle-orm/neon-serverless for production. If you need a single code path, run Neon's wsproxy in Docker so @neondatabase/serverless can reach local Postgres over WebSocket.

What connection string does @neondatabase/serverless use for local Postgres?

There is no working local connection string for @neondatabase/serverless unless you also run a WebSocket proxy in front of Postgres. For local development, switch to drizzle-orm/node-postgres and use postgresql://postgres:postgres@localhost:5432/postgres. Production keeps Neon's serverless string: postgresql://user:pass@ep-xxxx.region.aws.neon.tech/dbname?sslmode=require.

What's the best @neondatabase/serverless alternative driver for local Drizzle development?

drizzle-orm/node-postgres with the pg package. The Drizzle schema, types, and query code are identical to drizzle-orm/neon-http, so the only file that changes is the one constructing db. Pick the driver at runtime with process.env.NODE_ENV and your local app behaves like the deployed one. drizzle-orm/postgres-js (with postgres) works too and is slightly faster on cold connects, but node-postgres is the closest one-to-one swap.

Why does drizzle-kit push warn about neon serverless WebSocket against local Postgres?

drizzle-kit push and drizzle-kit migrate use node-postgres internally regardless of which Drizzle driver your app uses. If your drizzle.config.ts points at a Neon serverless or pooled URL, drizzle-kit prints a WebSocket warning and falls back to TCP. Give it a pg-compatible URL (Neon's unpooled direct connection or postgresql://postgres:postgres@localhost:5432/postgres for local) and the warning goes away. Do not switch the production app to pg just to silence drizzle-kit.

Why does my Neon seed work locally but fail in CI?

The two most common causes are using the pooled URL in CI (switch to unpooled for seeds) and a missing sslmode=require in the environment variable. Also check that migrations ran before the seed, since CI often skips the migrate step when databases are recreated.

What's the best test data generator for a Neon branch?

The best fit for a Neon branch survives a reset without a file to restore, regenerating the branch's tables with valid FKs in a single run no matter what migrations have landed since. Seedfast works this way against the branch's unpooled URL, and raw psql or ORM seeds remain the right choice for static reference data that should live in git and survive a reset by being committed.

How do I keep Neon branch seed data current when the schema changes?

Re-run a schema-aware generator after migrating the branch. It reads the new tables and columns and regenerates valid rows, so there's no file to hand-edit. A static seed.sql or ORM seed needs a manual update for every added column or foreign key. Really, it comes down to editing a file on every migration versus letting the generator re-read the schema, and for evolving test data, re-reading wins.

Related guides

How to seed a database: PostgreSQL practical guide: the framework-agnostic version of this article, covering raw SQL, Prisma, Drizzle, TypeORM, and node-postgres
Database seeding: methods and best practices: the conceptual companion covering reference vs test data, idempotency, and when seed files stop scaling
Database seeder tools compared: quick reference for Laravel, Prisma, Drizzle, TypeORM, and EF Core seeders next to standalone tools
Seed file maintenance: why static seed files fall out of sync with your schema and what to do about it
Microservice database seeding: when one Neon project isn't enough and you're seeding multiple databases that reference each other
How to seed a Supabase database: the Supabase sibling to this guide, covering seed.sql, supabase db reset, auth.users, and Supabase preview branches
Neosync alternative: migration guide for teams who ran Neosync against Neon and are now orphaned after the September 2025 Grow Therapy acquisition
Snaplet Seed alternative: migration guide for teams whose @snaplet/seed setup stalled after the 2024 shutdown, with the move to schema-aware seeding
Get started with Seedfast: connect to your Neon database and run your first schema-aware seed

Originally published at seedfa.st.

Circular Foreign Key Seed: Three Workarounds That Actually Run

Mikhail Shytsko — Fri, 14 Aug 2026 22:32:36 +0000

To seed two Postgres tables that reference each other, mark the foreign keys DEFERRABLE INITIALLY IMMEDIATE and wrap the inserts in a transaction that issues SET CONSTRAINTS ALL DEFERRED — Postgres then validates both FKs at COMMIT instead of after each row.

-- both foreign keys must already be declared DEFERRABLE (see Workaround 1)
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO organizations (id, name, primary_owner_id) VALUES (1, 'Acme', 1);
INSERT INTO employees (id, full_name, organization_id) VALUES (1, 'Ada', 1);
COMMIT;

A circular foreign key seed is the case where two Postgres tables reference each other and your seed file has no valid first row to insert. You pick a parent, INSERT it, and Postgres replies with violates foreign key constraint. You flip the order. Same error, from the other direction. Every ordering fails the constraint check the moment the row hits disk.

This is a narrower problem than the generic foreign-key ordering one. The general FK case has a topological-sort answer — insert parents before children, the ordering database seeding walks through. The circular case doesn't have a sort answer; it's a different shape of fix. This article walks through the three SQL patterns Postgres actually supports for this — deferred constraints, a nullable back-edge with a two-phase UPDATE, and a single-statement data-modifying CTE — then shows what happens when you point Seedfast at the same schema, and what doesn't.

Key Takeaways

A circular foreign key seed fails by default because Postgres checks each FOREIGN KEY constraint immediately after each row. With two tables that reference each other and both columns NOT NULL, no insertion order satisfies both checks at row-write time.
The canonical fix is DEFERRABLE INITIALLY IMMEDIATE on the constraint plus SET CONSTRAINTS ALL DEFERRED inside the seed transaction. Postgres then validates the constraints at COMMIT instead of after each row.
If one side of the cycle is genuinely optional, a nullable back-edge plus a two-phase INSERT … INSERT … UPDATE is simpler and doesn't change constraint semantics for normal app traffic.
A single data-modifying CTE that inserts both sides with explicit, cross-referencing keys satisfies both foreign keys at the end of the statement — so it seeds the cycle even on the strict schema (NOT NULL on both sides, no deferral) without a nullable column. The cost is that every key has to be known and hand-written up front.
Before reaching for any workaround, ask whether the cycle is intrinsic to the domain or an accident of the schema. Removing a back-edge that loses business meaning is the first case; removing one that only changes how queries are written is the second, and refactoring beats INITIALLY DEFERRED there.
When the schema permits a two-phase insert, Seedfast handles it automatically — no hand-written SQL. When the schema doesn't (strict NOT NULL on both sides with no deferral), no tool can; the deferred-constraint pattern in Workaround 1 stays the answer.

Why a circular foreign key seed fails on the first INSERT

Take the simplest concrete case the brief in your head is probably already running on. Two tables. Each one references the other. Both back-edges are NOT NULL because the domain says they have to be:

CREATE TABLE organizations (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    primary_owner_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    organization_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE organizations
    ADD CONSTRAINT organizations_primary_owner_id_fkey
    FOREIGN KEY (primary_owner_id) REFERENCES employees(id);

ALTER TABLE employees
    ADD CONSTRAINT employees_organization_id_fkey
    FOREIGN KEY (organization_id) REFERENCES organizations(id);

Every employee belongs to an organization. Every organization has a primary owner who is one of its employees. Both halves are real, both halves are required, and the schema is fine — until the seed file runs.

The naive seed reaches for the parent table first:

BEGIN;
INSERT INTO organizations (id, name, primary_owner_id)
VALUES (1, 'Northwind Logistics', 1);

INSERT INTO employees (id, full_name, email, organization_id)
VALUES (1, 'Sam Patel', 'sam.patel@northwind.example', 1);
COMMIT;

The output:

BEGIN
psql:/work/naive-insert.sql:9: ERROR: insert or update on table "organizations" violates foreign key constraint "organizations_primary_owner_id_fkey"
DETAIL: Key (primary_owner_id)=(1) is not present in table "employees".
psql:/work/naive-insert.sql:12: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

The reason is one sentence: by default, Postgres runs each FOREIGN KEY check at the moment a row is inserted, not at COMMIT. The organization row needs an existing employee 1, and that row doesn't exist yet. Flipping the order — employee first — produces the symmetric error from employees_organization_id_fkey because the organization the employee is supposed to belong to doesn't exist either. The cycle has no entry point under the default check timing. That timing is what each of the workarounds below changes, in different ways.

Is your circular FK actually circular, or is it a schema smell?

The competitor articles that rank for this query mostly skip this question and jump straight to INITIALLY DEFERRED. It's worth pausing on, because the answer changes which workaround is the right one — or whether you need one at all.

There's a useful distinction between intrinsic cycles and accidental ones.

An intrinsic cycle is one where removing either back-edge changes the meaning of the data. The organizations ↔ employees cycle above is intrinsic: an organization without a designated primary owner is a different domain object, and an employee with no organization is too. Both directions of the relationship encode something the business actually cares about, and the cycle is the honest expression of that — it's also the case where what referential integrity actually guarantees starts to matter, because the cycle is the constraint, not just an ordering puzzle. For these, you have to pick a workaround — the cycle isn't going away.

An accidental cycle is one where removing the back-edge only changes how queries are written. A common accidental shape is a users.created_by_user_id self-reference plus a created_users denormalized column on the parent — the same fact represented twice, in opposite directions, "for query performance". Another is a parent table with a latest_<child>_id pointer — the child already references the parent, and the back-pointer exists so a join can be skipped. In both cases, the cycle is a cache, not a fact. The fix is to drop the cached column and write the join.

A practical heuristic: if you remove the back-edge and the team has to re-explain a business rule (every organization must have a primary owner), the cycle is intrinsic. If you remove it and the team only has to rewrite one query, it's accidental, and the schema is the bug.

For accidental cycles, the cheapest workaround is the migration that removes the cycle. A junction table dissolves the back-edge into a join. Moving one column to a third table — for example, an organization_owners table with organization_id and employee_id — preserves the data without forcing either parent table to know about the other. These are migrations, not seed-script tricks, and they pay back every time anyone touches the schema afterwards.

For intrinsic cycles, keep reading.

In practice, cycles often arrive after the fact: a migration last sprint added a back-edge — a created_by_user_id on users, a latest_invoice_id on customers for a dashboard query — and the seed file that was fine yesterday now fails. This is a more common origin story than the schema looking circular from day one.

Workaround 1: deferred constraints inside the transaction

The canonical Postgres answer is to tell the constraint to defer its check to COMMIT. Two pieces:

The constraints must be declared DEFERRABLE. The default is NOT DEFERRABLE and must be changed at the constraint level, either in the original ALTER TABLE or in a follow-up migration.
Inside the seed transaction, SET CONSTRAINTS ALL DEFERRED flips the runtime behavior so checks run at COMMIT instead of after each row.

If your migrations live in an ORM (Prisma, Drizzle, TypeORM), this redeclaration is a raw-SQL migration step regardless — the DEFERRABLE flag isn't expressible in their schema DSLs.

-- Step A: redeclare the constraints as DEFERRABLE INITIALLY IMMEDIATE.
-- Default-immediate means normal app traffic still gets row-by-row checks.

BEGIN;

ALTER TABLE organizations
    DROP CONSTRAINT organizations_primary_owner_id_fkey;
ALTER TABLE organizations
    ADD CONSTRAINT organizations_primary_owner_id_fkey
    FOREIGN KEY (primary_owner_id) REFERENCES employees(id)
    DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE employees
    DROP CONSTRAINT employees_organization_id_fkey;
ALTER TABLE employees
    ADD CONSTRAINT employees_organization_id_fkey
    FOREIGN KEY (organization_id) REFERENCES organizations(id)
    DEFERRABLE INITIALLY IMMEDIATE;

COMMIT;

-- Step B: the seed transaction itself.
BEGIN;
SET CONSTRAINTS ALL DEFERRED;

INSERT INTO organizations (id, name, primary_owner_id)
VALUES (1, 'Northwind Logistics', 1);

INSERT INTO employees (id, full_name, email, organization_id)
VALUES (1, 'Sam Patel', 'sam.patel@northwind.example', 1);

INSERT INTO organizations (id, name, primary_owner_id)
VALUES (2, 'Cascadia Foods', 2);

INSERT INTO employees (id, full_name, email, organization_id)
VALUES (2, 'Mira Okafor', 'mira.okafor@cascadia.example', 2);

COMMIT;

Run against the schema above:

BEGIN
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COMMIT
BEGIN
SET CONSTRAINTS
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT
  table_name | count
---------------+-------
 organizations | 2
 employees | 2
(2 rows)

Two trade-offs to know. First, deferred constraints cost more memory: Postgres has to track the pending checks until COMMIT, and the bigger the transaction the higher the queue. For seed files that's usually fine; for million-row bulk loads it's worth measuring. Cybertec's PostgreSQL blog makes the same point about the memory cost of holding deferred checks. Second, INITIALLY IMMEDIATE is the safer default than INITIALLY DEFERRED: it leaves normal application transactions on row-by-row checking and only relaxes that for sessions that explicitly opt in with SET CONSTRAINTS ALL DEFERRED. The seed file opts in; the live app doesn't.

Workaround 2: nullable foreign key + two-phase insert

If your business rule actually allows one side of the cycle to be temporarily empty — for example, an organization can exist for a few rows of bookkeeping before its primary owner is decided — make that column NULL-able and break the cycle in three statements:

ALTER TABLE organizations
    ALTER COLUMN primary_owner_id DROP NOT NULL;

BEGIN;

INSERT INTO organizations (id, name, primary_owner_id)
VALUES
  (1, 'Northwind Logistics', NULL),
  (2, 'Cascadia Foods', NULL);

INSERT INTO employees (id, full_name, email, organization_id)
VALUES
  (1, 'Sam Patel', 'sam.patel@northwind.example', 1),
  (2, 'Mira Okafor', 'mira.okafor@cascadia.example', 2);

UPDATE organizations SET primary_owner_id = 1 WHERE id = 1;
UPDATE organizations SET primary_owner_id = 2 WHERE id = 2;

COMMIT;

The trade-off is the obvious one: this requires the column to be nullable. If the domain says every organization must have a primary owner from the moment of its creation, you can't do this — the column has to stay NOT NULL, and you're back to Workaround 1. Don't drop NOT NULL purely to get the seed file to run, because that quietly weakens a business rule that the rest of the application relies on.

Where this pattern earns its keep is when one side of the cycle is genuinely optional in the domain — a manager_id that's nullable for the CEO's row, a parent_category_id that's nullable for the root category. In those cases the schema already allows it, the seed file doesn't have to fight DEFERRABLE, and the result reads like ordinary code.

Workaround 3: data-modifying CTE for one-shot inserts

A NOT DEFERRABLE foreign key is checked at the end of each statement, not after each row — and a data-modifying WITH … RETURNING is one statement. So you can insert both sides of the cycle, each row naming the other's key, in a single statement: write the employees in a CTE, then the organizations in the outer INSERT. By the time the statement ends and the foreign keys are checked, both rows exist, and both checks pass.

WITH new_employees AS (
    INSERT INTO employees (id, full_name, email, organization_id)
    VALUES (1, 'Sam Patel', 'sam.patel@northwind.example', 1),
           (2, 'Mira Okafor', 'mira.okafor@cascadia.example', 2)
    RETURNING id
)
INSERT INTO organizations (id, name, primary_owner_id)
VALUES (1, 'Northwind Logistics', 1),
       (2, 'Cascadia Foods', 2);

This is the one pattern here that runs on the strict schema unchanged — both back-edges NOT NULL, neither constraint DEFERRABLE, no SET CONSTRAINTS. The order of the two INSERTs doesn't matter, and a dangling key still fails the end-of-statement check and rolls the whole statement back, so you don't trade away referential integrity to get the rows in.

The catch is that every cross-referencing key has to be known and written by hand into the one statement. That's fine for a bounded set of reference rows; it doesn't scale to a seed file with hundreds of rows, and it can't lean on BIGSERIAL — you can't reference an id the sequence hasn't handed out yet. It also doesn't help an automated seeder: a tool that inserts one table per statement never has both sides of the cycle in a single statement, so the end-of-statement trick isn't available to it. It's a sharp tool for a small, one-shot cyclic insert, written by hand.

There's also a fourth pattern in the wild — ALTER TABLE … DISABLE TRIGGER ALL to bypass FK checks during a sync. A widely-shared rubyrep gist recommends it for replication. Don't use it in a seed file. Disabling triggers lets bad data in and bypasses domain constraints, which is the failure mode the seed file is supposed to catch in the first place.

When the seeder picks the order for you

The patterns above pull two different levers. Deferred constraints and the two-phase insert change when the check runs — deferred to COMMIT, or split so the back-reference lands in a later UPDATE. The single-statement CTE instead packs both sides into one statement, so the end-of-statement check already sees a closed cycle. The first two are the ones a seeder can apply for you: if the schema permits deferral or a nullable side, a tool that reads the schema can handle the cycle for you without you writing the SQL. The CTE stays hand-written, because it needs every key up front.

That word "permits" is doing real work. Here's what happens when Seedfast runs against the strict-NOT-NULL schema we started with — both back-edges NOT NULL, neither constraint declared DEFERRABLE:

[2026-05-01T13:07:57+02:00] INFO: Seeding started
[2026-05-01T13:08:06+02:00] INFO: Tables in scope: public.organizations, public.employees
[2026-05-01T13:08:06+02:00] INFO: Planned: 8 records across 2 tables
[2026-05-01T13:08:06+02:00] INFO: Auto-approving plan (scope provided)
[2026-05-01T13:08:07+02:00] INFO: Seeding table public.organizations (1/2)
[2026-05-01T13:08:07+02:00] INFO: Seeding table public.employees (2/2)
[2026-05-01T13:09:31+02:00] ERROR: Failed table public.organizations: Failed: delta=0 < expected=2 (baseline=0, final=0)
[2026-05-01T13:09:31+02:00] ERROR: Failed table public.employees: Failed: delta=0 < expected=6 (baseline=0, final=0)
[2026-05-01T13:09:31+02:00] INFO: Seeding completed: 0/2 tables succeeded, 0 rows, 95.67s
[2026-05-01T13:09:31+02:00] WARN: 2 tables failed

It fails. So does every other seeder, because no tool can violate a NOT NULL immediate-checked constraint that Postgres itself enforces row by row. Filling the tables is the seeder's job; constraint enforcement is the database's job, and that line doesn't move. A DEFERRABLE INITIALLY IMMEDIATE variant behaves the same way unless the constraints are actually deferred at runtime — until they are, it's still a strict cycle, and declaring or deferring them is a schema decision the operator makes.

Now drop NOT NULL from organizations.primary_owner_id — the nullable variant we used in Workaround 2 — and run the same scope:

[2026-05-01T13:11:58+02:00] INFO: Seeding started
[2026-05-01T13:12:03+02:00] INFO: Tables in scope: public.organizations, public.employees
[2026-05-01T13:12:03+02:00] INFO: Planned: 8 records across 2 tables
[2026-05-01T13:12:03+02:00] INFO: Auto-approving plan (scope provided)
[2026-05-01T13:12:04+02:00] INFO: Seeding table public.organizations (1/2)
[2026-05-01T13:12:04+02:00] INFO: Seeding table public.employees (2/2)
[2026-05-01T13:12:32+02:00] INFO: Table public.organizations completed: 2 rows in 27.144s
[2026-05-01T13:12:32+02:00] INFO: Table public.employees completed: 6 rows in 27.144s
[2026-05-01T13:12:32+02:00] INFO: Seeding completed: 2/2 tables succeeded, 8 rows, 35.16s

Two organizations, six employees, in cyclic order, with each organization's primary owner one of its own employees — and no hand-written two-phase INSERT. When the schema gives Seedfast room — one side nullable, or DEFERRABLE INITIALLY DEFERRED declared and accepted at runtime — Seedfast handles it automatically, and the operator writes zero seed-side SQL. The other case — strict NOT NULL on both sides with no deferral — no tool can solve in software; that's a schema decision, not a tooling one.

If your seed file keeps running into this and your schema permits the two-phase write, see seedfa.st for what that looks like end-to-end on your own database. If it doesn't permit it, the deferred-constraint pattern in Workaround 1 is still the cleanest answer, and it's worth rolling once into the migration that adds the back-edge so the next person to write a seed file doesn't repeat this.

Frequently asked questions

How do you seed two Postgres tables with circular foreign keys?

Declare both foreign keys DEFERRABLE INITIALLY IMMEDIATE, then wrap the seed inserts in a transaction that runs SET CONSTRAINTS ALL DEFERRED. Postgres validates both constraints at COMMIT instead of after each row, so the two mutually-referencing rows can be inserted in either order inside one transaction without tripping the foreign-key check.

Why does every insert order fail with a circular foreign key?

By default Postgres checks each FOREIGN KEY the moment a row is written, not at COMMIT. When two tables reference each other and both columns are NOT NULL, the first row you insert always references a parent that doesn't exist yet — and flipping the order produces the symmetric error from the other constraint. The cycle has no valid entry point under immediate checking.

What does `SET CONSTRAINTS ALL DEFERRED` do?

It postpones every deferrable constraint check in the current transaction until COMMIT. Only constraints already declared DEFERRABLE are affected; non-deferrable ones still fire row by row. The live application keeps immediate checking unless it opts in, so the relaxed timing stays scoped to the seed transaction that issued the statement.

Can you seed a circular foreign key without making a column nullable?

Yes — two patterns do. Deferred constraints keep both columns NOT NULL and validate at COMMIT. A single data-modifying CTE that inserts both sides with explicit cross-referencing keys also keeps them NOT NULL, because a non-deferrable foreign key is checked at end-of-statement and a writable CTE is one statement. Only the two-phase INSERT … INSERT … UPDATE needs a nullable back-reference column.

Does `DEFERRABLE INITIALLY IMMEDIATE` slow down normal application traffic?

No — INITIALLY IMMEDIATE leaves everyday transactions on row-by-row checking. Only a session that explicitly runs SET CONSTRAINTS ALL DEFERRED relaxes the timing. Deferred checks do cost extra memory, because Postgres queues the pending checks until COMMIT — that matters for million-row bulk loads, but rarely for a seed file.

Related guides

What is Referential Integrity? — the property the cycle is bumping into, with the self-referential and composite-key edge cases that share the same insert-order discipline
Postgres Seed Script: Survive the Next Migration — what to do once you have working SQL: idempotency, sequence resets, and writing a seed script that doesn't break the next time the schema moves
Get started with Seedfast — connect to your PostgreSQL database and run a seed against your own schema in under five minutes

Originally published at seedfa.st.

One docker compose up, One Seeded Postgres

Mikhail Shytsko — Fri, 14 Aug 2026 22:31:58 +0000

Docker Compose made your dev database reproducible, one command bringing up the same Postgres for the whole team, down to the version and extensions, and the schema it hands you arrives perfect and empty. That emptiness is the part nobody standardized, so docker compose up gets you a database while filling it remains a separate step every project reinvents.

A docker compose seed database workflow comes down to two decisions, where the seed command runs and how it knows Postgres is ready for it. Get both right and the empty-database step folds into docker compose up. The rest of the page applies database seeding to this one stack, using the compose file I run most days.

TL;DR: Seedfast seeds a Postgres running under Docker Compose in one command. Bring the stack up with docker compose up -d --wait so the pg_isready healthcheck gates readiness, then run seedfast seed from the host against the published port. Keep schema bootstrap in /docker-entrypoint-initdb.d, and let the healthcheck-gated seed step handle the test data you actually iterate on.

Where the seed goes in a docker compose seed database setup

Three spots can hold the seed, and they aren't interchangeable. The first is /docker-entrypoint-initdb.d, the postgres image's init directory, where scripts run exactly once, on the first boot of an empty data directory. Inside the compose file itself sits the second, a one-shot service that waits for Postgres to report healthy, runs the seed, and exits — and the third stays outside compose altogether, a seed run from your host shell once the healthcheck clears.

Which one fits turns on how often the data changes. Schema bootstrap and a CREATE EXTENSION line belong at first boot, where they run once and stay put, while test data you regenerate as the schema moves wants a health-gated placement, one that re-runs on demand instead of hiding behind a volume populated weeks ago.

The compose file that boots a ready Postgres

Here is the compose file I start from. It defines one postgres service on the official postgres:16 image, publishes the port to the host, and keeps its data in a named volume so a restart doesn't wipe your work:

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
      POSTGRES_DB: app
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d app"]
      interval: 5s
      timeout: 3s
      retries: 5
      start_period: 5s

volumes:
  pgdata:

Of everything in that file, the healthcheck carries the most weight. Docker reports a container "running" the instant its process starts, but PostgreSQL needs a moment to finish recovery and open its socket, and a seed that fires inside that window dies on "connection refused" — the gap pg_isready closes, since it exits 0 only once the server is accepting connections. Binding the container's health to that check makes "healthy" and "ready to seed" the same event, and the interval, timeout, retries, and start_period fields tune how long Docker probes before it declares failure.

What /docker-entrypoint-initdb.d actually does

The init directory is where most people meet this pattern, and where they hit its sharp edge. On first boot, and only while the data directory is still empty, the postgres image runs everything in /docker-entrypoint-initdb.d in alphabetical order (every *.sql, *.sql.gz, and *.sh file). Mount 01-schema.sql and 02-seed.sql there and a fresh volume picks them up.

The trap is the word "fresh." Because the named volume persists across docker compose down and every ordinary docker compose up, the init scripts never fire again. Edit 02-seed.sql, run docker compose up again, and nothing changes, because the data directory isn't empty anymore and the image skips the whole directory. Only docker compose down -v drops the volume and lets the next up start clean, which makes the init directory a good home for schema bootstrap and a CREATE EXTENSION pgcrypto line, and a poor one for the test rows you tweak between runs. For the mechanics of the seed.sql those scripts contain, writing a Postgres seed script covers the file; the point here is only where it belongs in a compose stack.

The healthcheck-gated seed step

By default I reach for this placement — bring the stack up and wait for health:

docker compose up -d --wait

--wait holds until every healthcheck reports healthy, then hands the shell back — no sleep 10 guesswork, no polling loop. Once it returns, Postgres is ready, and the seed runs against the published port:

SEEDFAST_DSN=postgresql://app:app@localhost:5432/app \
  npx seedfast seed --scope "customers with a few orders and line items each" --output plain

That connection string is the same localhost:5432 you would hand to psql, reaching the container through the published port. npx pulls the CLI from npm; SEEDFAST_DSN tells the seed where to write and takes priority over DATABASE_URL, so it won't clobber another tool's setting; SEEDFAST_API_KEY, exported in your shell beforehand, authenticates the run; and --scope feeds Seedfast a plain-English description and skips the interactive prompt that would otherwise stall a script. Under the hood it reads the live schema and writes valid, connected rows, so every foreign key resolves to a parent that already exists. The flags match the CLI quick start; nothing changes because Postgres lives in a container.

If you would rather keep everything in one file, lift that command into a one-shot service that waits on Postgres:

  seed:
    image: node:22
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      SEEDFAST_API_KEY: ${SEEDFAST_API_KEY}
      SEEDFAST_DSN: postgresql://app:app@postgres:5432/app
    command: npx -y seedfast seed --scope "customers with a few orders each" --output plain

condition: service_healthy is the piece that matters, holding the seed container until the healthcheck passes so the seed never races a database that hasn't opened its socket. Inside the compose network the host becomes postgres, the service name, since localhost points the container at itself.

The loop stays short when the schema moves. After a migration adds a column, drop the volume and bring the stack back up:

docker compose down -v && docker compose up -d --wait

Then rerun the same seedfast seed command, and the new column simply appears in the next batch of generated rows, because the schema read happens fresh on every run rather than being cached from the first one.

None of this is only a local trick; the CI/CD database seeding guide runs the same gated-seed shape against a GitHub Actions service container, on the identical healthcheck.

Frequently asked questions

Why doesn't my seed script in docker-entrypoint-initdb.d run again?

Scripts in /docker-entrypoint-initdb.d run only on the first boot of an empty data directory, so a persisted named volume is almost always the reason a second run does nothing. The image skips the entire init directory whenever its data directory already holds a database, and no edit to a seed script there takes effect until the volume is gone — run docker compose down -v to drop it, bring the stack back up, and the scripts execute against a clean directory.

Can Seedfast seed a Postgres database running in Docker Compose?

Seedfast treats a Compose-managed Postgres like any other database, reaching it over the port the compose file publishes. Point SEEDFAST_DSN at postgresql://user:pass@localhost:5432/db once docker compose up -d --wait returns, and run seedfast seed. Migrations don't break it, since the schema is read live on every run, and when you would sooner keep everything inside the file, the same command drops into a one-shot Compose service.

How do I make Docker Compose wait for Postgres before seeding?

Docker Compose waits for Postgres when the service carries a pg_isready healthcheck and you gate the seed on it. Either bring the stack up with docker compose up -d --wait, which returns only after every healthcheck passes, or give the seed service a depends_on block with condition: service_healthy so Compose holds it until the database is accepting connections.

Should test data be baked into the Docker image or seeded after startup?

Test data you change with any regularity belongs in a seed step after startup, well away from the image build and the first-boot init script. A baked-in dataset goes stale the moment a migration lands and forces an image rebuild to refresh, whereas a post-startup seed reads the current schema and regenerates rows on demand, which is why the image and init directory should keep only the parts that rarely move, the schema and required extensions. A health-gated seed handles the data your tests assert against.

Seed the Postgres your stack boots empty

The compose file above hands every machine on your team the same clean Postgres, and Seedfast fills it with referentially valid rows generated from the live schema, no production data anywhere in the run. Start on the free plan without a card, and the whole loop, up --wait included, fits inside a few minutes.

Originally published at seedfa.st.

Prisma Postgres Seed: prisma db seed, psql, and the Direct URL

Mikhail Shytsko — Fri, 14 Aug 2026 22:31:09 +0000

Prisma Postgres seed in one command — or rewrite your seed script every time the schema changes. Prisma's managed Postgres spins up an empty database in seconds; then you have to fill it. Here are three ways to do it without fighting the connection string.

TL;DR. To seed a Prisma Postgres database, run npx prisma db seed against the direct connection string (db.prisma.io, not pooled.db.prisma.io). Configure seed: "tsx prisma/seed.ts" in prisma.config.ts and point directUrl at the direct URL in schema.prisma. In Prisma ORM v7, migrate dev no longer triggers the seed — invoke prisma db seed explicitly. For schemas past ~15 related tables, Seedfast reads the live schema on each run and skips the seed file entirely.

Key Takeaways

Prisma Postgres ships with two TCP connection strings — direct (db.prisma.io:5432) and pooled (pooled.db.prisma.io:5432). Always seed against the direct URL. The pooled endpoint runs through a connection pooler and breaks long transactions and prepared statements, the same class of failure Neon and Supabase users hit on their poolers
For edge runtimes that can't open a TCP socket (Cloudflare Workers, Vercel Edge), the @prisma/adapter-ppg package — exporting PrismaPostgresAdapter — wraps Prisma's serverless driver and accepts the same direct TCP connection string. For Node-side seeds the plain TCP URL with the standard pg driver is simpler
prisma db seed still works — but in Prisma ORM v7 it no longer runs automatically during prisma migrate dev. You have to invoke it explicitly, or call it from your CI step
Prisma Postgres has no production data to copy, no PII, no compliance sign-off — just an empty schema waiting for realistic test data. That's the same starting point developers in fintech, healthcare, and other regulated industries hit on every greenfield project
When the schema changes — which it will — hand-written seed files break. Seedfast reads the live schema on every run and regenerates valid, connected data, so there's no seed.ts to maintain

Quick fix if you landed here from a broken seed:

# Pooled URL — breaks seeding (transaction-mode pooler, no prepared statements)
postgres://USER:PASS@pooled.db.prisma.io:5432/?sslmode=require

# Direct URL — use this for seeds, migrations, admin scripts
postgres://USER:PASS@db.prisma.io:5432/?sslmode=require

The rest of this guide walks through psql, prisma db seed, and the @prisma/adapter-ppg serverless-driver path that actually work against Prisma Postgres, and shows how to keep the dataset alive across migrations.

You provisioned Prisma Postgres because it spins up instantly, scales to zero, and ships with the tooling you already use — prisma migrate, the Prisma Client, the new create-prisma scaffold. But the database is empty, and the only realistic data you can copy in from somewhere is production data — which, if you work in a regulated industry, you're not allowed to touch. To seed a Prisma Postgres database, you need three things: the right connection string, a seed strategy that survives schema changes, and a way to make the data look like the real thing without ever leaving development. This guide covers all three.

If you want the framework-agnostic version of this article first, how to seed a database covers the cross-stack fundamentals. This article is specifically about Prisma Postgres — the managed product, not the ORM seed pattern.

Why a Prisma Postgres database needs seeding

A fresh Prisma Postgres database is empty. Migrations create the schema; nothing fills it. Unlike a long-lived shared dev database that accumulates data over months, a Prisma Postgres project is born clean and stays clean until you put something in it.

Three scenarios force the seeding question:

New project onboarding. A teammate clones the repo, runs npx prisma migrate dev, and ends up with thirty empty tables. The app boots but every list view is blank.
CI and ephemeral environments. Each CI run, each preview deploy, each rebuilt local stack starts from zero. Without a seed step, end-to-end tests hit empty queries and fail in ways that have nothing to do with the change being tested.
Demos and staging. You need 500 products, a year of order history, and users that look real. Faker-style random columns aren't good enough — the data has to hold up under a 10-minute customer demo.

Prisma's own docs cover the mechanics of prisma db seed — how to wire up prisma.config.ts, how to run TypeScript seeds with tsx. They don't cover the lifecycle problem : the seed file gets stale on every migration, and the team that wrote it hasn't touched it in six weeks. That's the part that bites.

Prisma Postgres seed connection strings: direct vs pooled

Every Prisma Postgres database exposes two TCP connection strings. The dashboard shows both:

# Direct (one-to-one, full Postgres protocol, prepared statements work)
postgres://USER:PASS@db.prisma.io:5432/?sslmode=require

# Pooled (transaction-mode pooler, optimized for short-lived requests)
postgres://USER:PASS@pooled.db.prisma.io:5432/?sslmode=require

The difference is the pooled. subdomain. The pooled endpoint sits behind a transaction-mode connection pooler — every query gets a fresh backend connection, which is great for serverless apps with bursty traffic, and bad for everything else. Specifically:

Prepared statements don't survive across transactions, so any client that prepares a query (most of them) will hit prepared statement "s1" already exists after the first reuse
Long-running operations like a multi-statement seed transaction can be terminated when the pool decides to recycle the backend
Session-level settings (SET, advisory locks, temporary tables that span statements) silently disappear

For seeding, migrations, and any admin script, use the direct URL. Wire it up explicitly:

# .env.local
DATABASE_URL="postgres://USER:PASS@pooled.db.prisma.io:5432/?sslmode=require" # app runtime
DIRECT_URL="postgres://USER:PASS@db.prisma.io:5432/?sslmode=require" # migrations + seeds

In schema.prisma, point directUrl at the direct URL — Prisma uses it for migrations automatically:

datasource db {
  provider = "postgresql"
  url = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

If a multi-statement seed dies halfway through with prepared statement "s1" already exists or cached plan must not change result type, the script is talking to the pooler. Point it at the direct URL and re-run.

You may also see the older Prisma Accelerate–style URL — prisma+postgres://accelerate.prisma-data.net/?api_key=... — referenced in some examples. That URL is part of the Accelerate caching layer, not the standard Prisma Postgres connection. The two TCP strings above are what the Prisma Console gives you for a Prisma Postgres database, and they cover every seeding scenario this guide describes.

Method 1: Prisma Postgres seed with raw SQL

The simplest path. Write INSERTs, run them with psql.

-- seed.sql
INSERT INTO organizations (id, slug, plan) VALUES
  (1, 'acme-corp', 'team'),
  (2, 'beta-labs', 'free')
ON CONFLICT (id) DO NOTHING;

INSERT INTO projects (id, organization_id, name, status) VALUES
  (1, 1, 'Mobile rewrite', 'active'),
  (2, 1, 'Billing refactor', 'planned'),
  (3, 2, 'Internal demo', 'active')
ON CONFLICT (id) DO NOTHING;


psql "$DIRECT_URL" -f seed.sql

SSL is mandatory on Prisma Postgres — the sslmode=require parameter on the connection string handles that automatically. Drop it and you'll get connection requires SSL.

For larger reference loads, COPY FROM STDIN is meaningfully faster than row-by-row inserts because it skips per-row parsing and planning:

psql "$DIRECT_URL" -c "COPY products (name, price, category_id) FROM STDIN CSV" < products.csv

ON CONFLICT DO NOTHING keeps the seed idempotent — CI can run it twice without falling over on duplicate keys. For values that should always reflect the latest state, use ON CONFLICT DO UPDATE:

INSERT INTO feature_flags (key, enabled) VALUES
  ('new_checkout', true)
ON CONFLICT (key) DO UPDATE SET enabled = EXCLUDED.enabled;

Raw SQL is fine for reference data — feature flags, country codes, role definitions, the dozen rows your app reads at boot. It starts breaking once you have ten or fifteen tables with foreign keys, because every migration that adds a NOT NULL column or a new FK forces you to hand-edit the seed. Seed file maintenance covers why this lifecycle is so brutal on active codebases.

Method 2: Seed Prisma Postgres with prisma db seed

Most teams using Prisma Postgres are also using Prisma ORM, so the natural path is prisma db seed. In current Prisma versions, the seed command is configured in prisma.config.ts:

// prisma.config.ts
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
    seed: "tsx prisma/seed.ts",
  },
  datasource: { url: env("DIRECT_URL") },
});

A typical TypeScript seed against Prisma Postgres looks like any other Prisma seed — point the client at the direct URL and use the standard API:

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  const org = await prisma.organization.upsert({
    where: { slug: "acme-corp" },
    update: {},
    create: { slug: "acme-corp", plan: "team" },
  });

  await prisma.project.upsert({
    where: { organizationId_name: { organizationId: org.id, name: "Mobile rewrite" } },
    update: {},
    create: { organizationId: org.id, name: "Mobile rewrite", status: "active" },
  });
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(() => prisma.$disconnect());


npx prisma db seed

Two things changed in Prisma ORM v7 that catch teams off guard:

prisma migrate dev no longer runs prisma generate or the seed automatically. The --skip-seed flag is gone because there's nothing to skip — you have to invoke prisma db seed explicitly when you want it
prisma migrate reset still triggers the seed, so the "wipe and restart" workflow is intact

If you want the seed to run after every migration locally, wrap it in a package.json script:

{
  "scripts": {
    "db:reset": "prisma migrate reset --force",
    "db:migrate": "prisma migrate dev && prisma db seed"
  }
}

Seeding from an edge runtime with `@prisma/adapter-ppg`

Most seeds run in Node — locally or in CI — and the plain TCP direct URL is the right choice there. If the seed has to run from an environment that can't open a TCP socket (a Cloudflare Worker, a Vercel Edge function, a managed worker without raw networking), use Prisma's serverless driver adapter:

npm install @prisma/adapter-ppg


// prisma/seed.ts
import { PrismaClient } from "@prisma/client";
import { PrismaPostgresAdapter } from "@prisma/adapter-ppg";

const adapter = new PrismaPostgresAdapter({
  connectionString: process.env.DIRECT_URL!,
});
const prisma = new PrismaClient({ adapter });

async function main() {
  await prisma.organization.upsert({
    where: { slug: "acme-corp" },
    update: {},
    create: { slug: "acme-corp", plan: "team" },
  });
}

main().finally(() => prisma.$disconnect());

The adapter takes the same direct TCP connection string and routes queries over Prisma's HTTP/WebSocket transport, so the seed runs anywhere the Prisma Client runs — but for a regular Node script, plain TCP without the adapter is one fewer dependency.

The bigger problem with both prisma db seed paths is the same one raw SQL has: the values are hand-written. When a migration adds accounts.organization_id NOT NULL, the seed breaks on the next run. Someone — usually whoever picked up the on-call rotation — has to fix it before the rest of the team can boot the app.

If that lifecycle sounds like every Monday morning on your team, Seedfast skips the seed file entirely — generating the dataset from the live schema on every run. The next section covers how it fits into a Prisma Postgres workflow.

Method 3: Schema-aware seeding with Seedfast

Point Seedfast at the Prisma Postgres direct URL, describe the dataset in plain English, and Seedfast reads the schema — tables, columns, constraints, foreign keys — and generates a valid, connected dataset that fits it. There's no seed.sql or seed.ts in the repo, because the schema is the seed plan.

npm install -g seedfast
# or: brew install argon-it/tap/seedfast

# Log in and connect — paste the direct (db.prisma.io) connection string
seedfast connect

# Generate data from the current schema
seedfast seed --scope "two organizations on the team plan, 8 active projects, varied task assignments"

The next migration adds a column? Seedfast reads it on the following run and generates the new field. The next migration adds a whole table? Same — picked up on the next run. Every row comes out valid and connected — including self-referencing tables and tables that reference each other — so there's no hand-written FK list to maintain.

The scope changes per environment, the command doesn't:

# Local dev loop
seedfast seed --scope "2 organizations, 4 users each, 5 projects between them"

# Preview branch for a PR that touches the billing flow
seedfast seed --scope "1 organization mid-trial with overdue invoice and 3 retried payments"

# Sales demo
seedfast seed --scope "500 organizations across 3 plan tiers, 6 months of project history"

Run prisma migrate deploy first to make sure the database is on the latest schema, then run Seedfast against the same direct URL. Prisma owns the structure; Seedfast fills it.

For production reference data — feature flags, country codes, admin roles — keep the versioned SQL or the Prisma seed. That data belongs to the application and should ship through migrations. Seedfast targets the larger, evolving development, CI, staging, and demo datasets that go stale every time the schema moves — not the dozen rows that go to prod.

Why this matters in regulated industries

For teams that can't copy production data into dev — fintech, healthcare, anyone under HIPAA, GDPR, or SOC 2 review — Seedfast removes the blocker that hand-written seeds and anonymization pipelines both leave in place:

Seedfast generates from the schema, not a snapshot. No production access, no PII pipeline, no anonymization step. Schema metadata (table and column shapes) is sent over the wire to Seedfast's generation service so the model knows what to build; row values are not. The result is realistic test data without ever pulling production rows out of their environment. For the wider workflow, see staging without production data and the HIPAA test data playbook.
Seedfast fills a database, not a column. Faker hands back random strings. Seedfast generates connected organizations, accounts, transactions, and audit rows with valid foreign keys, distributions that look like a real product, and values that pass a domain check.
Seedfast scales with one command. The same seedfast seed runs ten rows for a unit test and half a million for a load test — only the --scope changes.

The free plan covers small dev datasets with no credit card, so a Friday spike costs nothing — connect and run your first seed in about two minutes, or check pricing before sending it to your CTO.

Common Prisma Postgres seeding issues and how to fix them

`prepared statement "s1" already exists`

You're seeding through the pooled URL (pooled.db.prisma.io). The transaction-mode pooler discards prepared statements between transactions, and the driver tries to reuse one that's gone. Switch to the direct URL for the seed.

`cached plan must not change result type`

A server-side Postgres plan cache error: a prepared statement's result type changed underneath it, usually because a migration ran between two seed invocations on the same connection. Run migrations to completion before the seed, and use the direct URL — the pooler can mask this error in confusing ways.

`connection requires SSL`

Your connection string is missing sslmode=require. Prisma Postgres rejects non-SSL connections. Add the parameter, or set PGSSLMODE=require in the environment.

`the URL must start with the protocol postgresql:// or postgres://`

A prisma+postgres://... URL slipped into DIRECT_URL. The Prisma Console issues TCP strings (db.prisma.io / pooled.db.prisma.io) for Prisma Postgres — use those for psql, the pg driver, and prisma db seed without an adapter. The Prisma-protocol URL is not part of the standard seeding path.

`prisma db seed` does nothing during `prisma migrate dev`

This is the Prisma ORM v7 behavior change. migrate dev no longer auto-seeds. Either invoke prisma db seed explicitly, or chain it in a script: prisma migrate dev && prisma db seed.

`relation "users" does not exist`

The seed ran before the migration. The order is always migrate then seed , never the reverse. In CI, make prisma migrate deploy (or prisma migrate reset --force on ephemeral branches) a hard prerequisite of the seed step.

Seed succeeds locally, fails in CI

Two recurring causes: CI uses the pooled URL because that's what the app uses (switch to the direct URL for the seed step), or the CI environment hasn't run migrations against the freshly-created database (run prisma migrate deploy before the seed).

Manual SQL vs prisma db seed vs Seedfast

Aspect	Raw SQL (`psql -f`)	`prisma db seed`	Seedfast
Setup time	None	Already there if you use Prisma	`npm install -g seedfast`
External dependency	None — `psql` is everywhere	None — already in your stack	Separate CLI plus a network call to Seedfast's generation service
File you maintain	`seed.sql`	`seed.ts`	None — reads the live schema
FK order	Manual	Manual	Automatic
Survives migrations	No — manual updates on every change	No — manual updates on every change	Yes — regenerates from the live schema
Realistic volumes	Painful past ~50 rows	Works with Faker, still hand-written	Natural-language scope describes the dataset
Output stability	Deterministic — committed values	Deterministic if you use fixed seeds	Regenerated each run; pin a seed value when you need byte-stable fixtures
Reviewable in PRs	Yes — diff the SQL	Yes — diff the seed script	Indirectly — the scope is reviewable, individual rows are not
Good for static config (flags, codes)	Excellent — versioned, reviewed	Excellent — versioned, reviewed	Not the target
Good for dev / CI / staging datasets	Manual re-sync on every migration	Manual re-sync on every migration	Regenerates from the live schema
What leaves your environment	Nothing — runs locally	Nothing — runs locally	Schema metadata only (table and column shapes); no row data is transmitted

Pick by job, not by religion. Reference data — the dozen rows the app reads at boot — belongs in a committed SQL file or a small prisma db seed script that goes through code review with the schema change. Seedfast targets the larger, evolving dev / CI / staging datasets that go stale on every migration, where regenerating from the schema is faster than fixing the file. Free plan, no credit card — try it on your Prisma Postgres schema in about two minutes.

Using @prisma/adapter-neon with local Postgres for development

@prisma/adapter-neon is not required for local Postgres development. Use the default Prisma client with a regular DATABASE_URL, or @prisma/adapter-pg if you want a consistent driver-adapter API across environments.

The @prisma/adapter-neon package exists to solve one specific problem: edge runtimes like Vercel Edge Functions and Cloudflare Workers can't open raw TCP sockets, so Prisma can't talk to Postgres the normal way. The adapter wraps Neon's serverless driver, which routes queries over HTTP and WebSockets to a Neon-hosted endpoint. None of that machinery applies when your code runs in plain Node against a local Postgres container — Node has TCP, the standard Prisma client connects directly, and there's no Neon endpoint to point the adapter at.

For the common setup — Neon in production on the edge, plain Postgres in Docker locally — drop the adapter in development. Construct new PrismaClient() with no adapter argument, set DATABASE_URL to a regular postgresql://... string, and Prisma will talk to your local container over TCP. The Neon adapter only gets instantiated in production where it's actually needed.

The trade-offs between the three options:

Setup	Wire protocol	Where it runs	Use when
Default `new PrismaClient()`	TCP (Prisma's built-in driver)	Node only	Local dev, seed scripts, any Node server
`@prisma/adapter-pg` (docs)	TCP (wraps `node-postgres`)	Node only	You want the same driver-adapter API everywhere and locally use plain Postgres
`@prisma/adapter-neon`	HTTP / WebSocket (wraps Neon's serverless driver)	Edge (Vercel Edge, Cloudflare Workers)	You deploy to an edge runtime against Neon

If you'd rather keep one code path across environments — same constructor, same generic shape — use @prisma/adapter-pg locally. It wraps node-postgres (the standard pg driver) and presents the same driver-adapter API as the Neon variant, so the only thing that changes between local and production is which adapter you import. Driver adapters reached GA in Prisma 6.16; on 6.16+, the previewFeatures = ["driverAdapters"] flag in the generator client block is no longer required. On older versions, add it to schema.prisma.

Conditional adapter: @prisma/adapter-pg locally, @prisma/adapter-neon in production

Branch on NODE_ENV inside the factory that constructs PrismaClient: pass PrismaPg locally and PrismaNeon in production. The connection string and the query code stay identical; only the imported adapter changes.

// lib/prisma.ts
import { PrismaClient } from "@prisma/client";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaNeon } from "@prisma/adapter-neon";

function makeClient() {
  const connectionString = process.env.DATABASE_URL!;

  if (process.env.NODE_ENV === "production") {
    const adapter = new PrismaNeon({ connectionString });
    return new PrismaClient({ adapter });
  }

  const adapter = new PrismaPg({ connectionString });
  return new PrismaClient({ adapter });
}

export const prisma = makeClient();

schema.prisma needs the preview feature flag on Prisma versions where driver adapters aren't GA yet:

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

datasource db {
  provider = "postgresql"
  url = env("DATABASE_URL")
}

The simpler alternative is to skip the adapter locally entirely. new PrismaClient() with no adapter argument talks to local Postgres over plain TCP — nothing extra to install, configure, or fall out of sync.

DATABASE_URL format for local Postgres with Prisma

For local Postgres with Prisma, DATABASE_URL is postgresql://postgres:postgres@localhost:5432/postgres — plain TCP, no SSL, no sslmode=require. Replace user, password, and database name with whatever your container actually uses; both postgresql:// and postgres:// scheme prefixes work.

# .env.local
DATABASE_URL="postgresql://postgres:postgres@localhost:5432/postgres"

A few things that catch people:

@prisma/adapter-neon will not work against this URL. The adapter expects a Neon HTTP/WebSocket endpoint (something like postgresql://USER:PASS@ep-xxx.region.aws.neon.tech/dbname), and localhost resolves to a plain Postgres server that doesn't speak Neon's protocol. If you point the Neon adapter at localhost:5432, you'll get connection errors that look like protocol mismatches, not "wrong port" — because the bytes on the wire are different.
If schema.prisma declares directUrl, set it to the same local URL during development. Prisma uses directUrl for migrations and prisma db seed, so leaving it pointed at a production Neon endpoint while DATABASE_URL is local will run migrations against the wrong database the moment you forget which terminal you're in.
sslmode=require is for hosted Postgres (Neon, Prisma Postgres, Supabase, RDS). A local container doesn't have SSL configured by default, so leave sslmode off entirely or set it to disable if your client insists on a value.

For the seed step specifically, the local DATABASE_URL is what prisma db seed reads when it constructs the client. No adapter, no directUrl gymnastics — just point at the container and run. If the schema is past a dozen tables and the hand-written seed file is what's slowing the local loop down, Seedfast reads the live schema and generates the dataset directly against the local URL, so the development database fills up without a seed.ts to maintain.

Frequently asked questions

Do I need @prisma/adapter-neon for local Postgres development?

No. The Neon adapter exists for edge runtimes (Vercel Edge, Cloudflare Workers) that can't open TCP sockets. Local Node has TCP, so the default Prisma client connects to a local Postgres container directly. Use new PrismaClient() with no adapter argument and a regular DATABASE_URL=postgresql://postgres:postgres@localhost:5432/postgres.

Can I use @prisma/adapter-neon with localhost?

Not in any useful way. The adapter expects Neon's HTTP/WebSocket endpoint (*.neon.tech), and a local Postgres container doesn't speak that protocol — pointing the adapter at localhost:5432 produces protocol errors, not connection-refused errors. For local development, either drop the adapter and use the default client, or use @prisma/adapter-pg which wraps node-postgres and works against local TCP.

How do I switch between @prisma/adapter-pg locally and @prisma/adapter-neon in production?

Branch on NODE_ENV (or a dedicated env var) inside the factory that creates PrismaClient. Import both adapters, pick the right constructor based on environment, and pass the resulting adapter to new PrismaClient({ adapter }). The connection string can be the same env var (DATABASE_URL); only the adapter import differs. Driver adapters are GA on Prisma 6.16+; on older versions, add previewFeatures = ["driverAdapters"] to the generator client block in schema.prisma.

What DATABASE_URL should I use for local Postgres with Prisma?

A plain TCP URL with no SSL: postgresql://postgres:postgres@localhost:5432/postgres for the default Docker image, or whatever user, password, and database your container is configured with. Skip sslmode=require — that's only for hosted Postgres (Neon, Prisma Postgres, Supabase). If schema.prisma has a directUrl, point it at the same local URL during development so migrations and seeds don't accidentally hit production.

How do I seed a Prisma Postgres database from the command line?

Copy the direct connection string from the Prisma Console (the one with db.prisma.io, not pooled.db.prisma.io) and run psql "$DIRECT_URL" -f seed.sql. Make sure sslmode=require is on the URL. For Prisma projects, configure seed: "tsx prisma/seed.ts" in prisma.config.ts and run npx prisma db seed. For schemas with many tables, seedfast seed --scope "..." generates connected data without writing a seed file.

Should I use the pooled or direct Prisma Postgres URL for seeding?

Use the direct URL (db.prisma.io:5432). The pooled URL routes through a transaction-mode connection pooler that breaks prepared statements and can interrupt long seed transactions. Reserve the pooled URL for the application at runtime, where short, bursty queries benefit from the pool.

Can I seed Prisma Postgres from a Cloudflare Worker or Vercel Edge?

Yes — install @prisma/adapter-ppg, pass a PrismaPostgresAdapter instance to the PrismaClient constructor, and the seed runs over Prisma's HTTP/WebSocket transport. The adapter takes the same direct TCP connection string from the Prisma Console; you don't need a separate URL. For seeds running on Node (CI, locally), the plain TCP path with pg is simpler and one fewer dependency.

How do I seed Prisma Postgres in GitHub Actions?

Set DIRECT_URL as a repository secret pointing at the direct connection string. In the workflow, run npx prisma migrate deploy against DIRECT_URL, then run your seed step (either npx prisma db seed or seedfast seed --scope "..."). For application steps in the same job, use the pooled URL via DATABASE_URL. The general pattern is covered in CI/CD database seeding.

How do I seed Prisma Postgres without writing a seed file?

Connect Seedfast to the database (seedfast connect, paste the direct URL) and run seedfast seed --scope "<plain English description>". Seedfast reads the live schema and generates a valid, connected dataset every time, so when migrations add columns or tables, the next seed run reflects the new shape automatically — there's no file that goes stale. See database seeder tools compared for how this fits next to the framework-built-in seeders.

Can I use Prisma Postgres for staging without copying production data?

Yes — that's the typical setup for teams in regulated industries. Stand up a separate Prisma Postgres database for staging, run migrations against it, then seed it with realistic generated data. No production rows leave the production environment, and the dev/staging path doesn't depend on an anonymization pipeline. Schema metadata is the only thing that crosses the wire to Seedfast's generation service, so review the data path against your security policy the same way you would any new vendor. Staging without production data walks through the workflow.

Wrapping up

The short version: use db.prisma.io:5432 for seeds, never pooled.db.prisma.io:5432. Configure DIRECT_URL in prisma.config.ts and reach for @prisma/adapter-ppg only when the seed has to run on an edge runtime. Either pick a maintenance schedule for seed.ts or hand the job to a tool that reads the schema on every run — what doesn't work is pretending a hand-written seed will keep up with twenty migrations a quarter.

If a Friday afternoon spike on the seed file is what brought you here, the direct URL fixes today's break. The deeper fix is to stop hand-rolling the dataset every time the schema moves. Seedfast does that for Prisma Postgres in two minutes — connect to your database and run a seed before you close the laptop.

Related guides

How to seed a database: PostgreSQL practical guide — the framework-agnostic version of this article, covering raw SQL, Prisma, Drizzle, TypeORM, and node-postgres
Database seeding: methods and best practices — the conceptual companion covering reference vs test data, idempotency, and when seed files stop scaling
Database seeder tools compared — quick reference for Prisma, Drizzle, TypeORM, and standalone tools side by side
Seed file maintenance — why static seed files fall out of sync with the schema, and what to do about it
How to seed a Neon database — the Neon sibling to this guide, with the same pooler-vs-direct dance and a deep dive into branching
How to seed a Supabase database — the Supabase sibling, covering seed.sql, supabase db reset, and Supabase preview branches
Staging database without production data — the compliance-driven workflow for fintech, healthcare, and anyone who can't pipeline PII into staging
Get started with Seedfast — connect to your Prisma Postgres database and run your first schema-aware seed

Originally published at seedfa.st.

Synthetic Data vs Real Data? First Say What It's For

Mikhail Shytsko — Fri, 14 Aug 2026 21:24:49 +0000

You open the tool, and it wants a decision before you have said what the data is for. Generate the rows, or pull from what is real? Synthetic data vs real data gets framed as a head-to-head with one durable winner, so people go hunting for whichever source is generally safer or more faithful. No such winner exists. The comparison resolves only once you name the job the data is being hired for, because the property that makes a dataset excellent for one job barely registers for another.

Most of the muddle comes from treating real as the default and synthetic as the fallback. Production data carries real authority, since it actually happened, but it earns its keep on one class of work and means little on another. Sort the work into two lanes, and the stalemate breaks.

Synthetic data vs real data starts with the job

Almost everything anyone generates or copies a database for lands in one of two lanes, and the lanes want opposite things.

The first is statistical work, the training and evaluation of machine-learning models and the analytics beside them. Here the data stands in for a population, so what matters is that its overall shape matches reality closely enough. Correlations between columns hold, the numbers carry real skew, and a one-in-a-thousand case turns up about once in a thousand rows, and a few wrong rows in a set of millions do no damage, because the model reads the mass and not the exception.

The second lane is the application database, the one your software connects to while you build against it, test it, stage it, or demo it — a lane that answers to a schema rather than to a distribution. Every foreign key must land on a row that exists; miss a constraint or leave a required column empty, and the database refuses the write and the work halts. So, a real-world distribution becomes a bonus here; validity against the schema is what the lane demands.

The dividing line is error tolerance

Underneath the two lanes sits the reason they diverge - a training corpus and an application database forgive mistakes at wildly different rates. Give a model a batch in which a small percentage of rows are wrong, and it still learns the pattern that dominates; the bad rows blur into noise the training averages over, and if the data improves later, the model improves with it. However, an application database grants no such margin, and a single row whose foreign key points at a customer that was never inserted is not an error to be averaged away - the insert is refused, the load stops there, and every test that expected that row fails for a reason unrelated to the code under test. Moreover, a statistical workload can name a defect rate it tolerates and run below it and a relational one has no rate to name.

In the statistical lane, real data usually wins

For work that feeds a model or a dashboard, real data holds a genuine edge, because you are chasing fidelity and real data is fidelity by definition. It carries the correlations nobody thought to encode and the messy tail nobody would have invented. When you can get it, and are allowed to use it, real data is often the right call for lane one.

The catch lives in those two conditions: production data comes wrapped in access controls for good reason, and pointing a training pipeline at raw customer records is the exposure those controls exist to prevent, and it ages, since last quarter's snapshot describes a business that has moved on. Yes, it hands you only the cases that occurred, so a model needing thousands of examples of a rare fraud pattern gets the handful real data has and no more. Synthetic data is built for exactly that opening, producing as many rare cases as a model needs and carrying no real record. So, its weakness here is fidelity itself, the hard problem the fidelity-first synthesis platforms and open-source synthesizers spend their engineering on. The honest read on synthetic data vs production data in lane one is that synthetic wins when access is blocked or rare cases are scarce, and loses when its fidelity falls short of the real distribution.

The application lane flips the answer

Populate a database your application runs against and the calculus inverts. Fidelity stops being the point; correctness against the schema becomes the whole of it. You need a dataset where every reference resolves and every constraint holds, so the app boots and the tests run instead of dying on a bad insert. Real production data can clear that bar, but dragging a copy into a development or staging environment looks safe and is not. It brings the exposure problem back where the controls are weakest, a full clone is heavy enough to slow every reset, and it drifts from the schema the moment someone ships a migration the dump predates. It also cannot manufacture the case you need on demand, since production has the customers it has, not the five hundred with a canceled subscription and refunded order your edge-case test wants.

Synthetic data is the natural fit here, with one caveat that trips teams up. A generator filling each column from its own source of randomness hands you a users table and an orders table that look perfect alone and do not connect, order rows pointing at customer IDs that were never created, which a real foreign key rejects on the first insert. Why believable values are the easy half while making the rows connect is the hard half is the subject of how synthetic generation actually works end to end; the deeper version, where a reference resolves and still means nonsense, runs through what coherent data really demands. Some teams split the difference by masking a production copy, which trades one set of headaches for another and sits beside the other options in the seeding-tool landscape. Real data, for all its authenticity, is out of place in this lane, and naive synthetic data fits the lane while still breaking the schema — so the version worth having reads the schema first and generates rows that already connect.

The comparison, by job

Laid side by side, the two lanes ask nearly opposite things of the same two sources.

Question	Statistical lane (models, analytics)	Application lane (dev, test, staging, demos)
What the data stands in for	a population's distribution	a schema the software runs against
The bar for "good"	distributional fidelity	relational correctness
Cost of one wrong row	absorbed into a defect rate	halts the insert and every test after it
Real data's main liability	access limits, staleness, no rare cases on demand	exposure risk, size, drift, no edge cases on demand
Synthetic data's main liability	fidelity gaps against the real distribution	naive generators that ignore the schema
Usually the better source	real, or high-fidelity synthetic when access is blocked	synthetic that reads the schema

Read it down a column for your job, not across a row for a winner. If the data feeds a model or a report, start from real and turn to synthetic when access rules or missing rare cases force it, then judge what you generate on how faithfully it reproduces the distribution. If it populates an application's database, start from synthetic and hold it to one standard above the rest, that every row satisfies the schema. The synthetic vs real test data decision comes down to exactly that. Doing it well is its own discipline, the one database seeding covers, and the AI-driven options are ranked in the AI generator comparison.

Where Seedfast fits, and where it doesn't

Seedfast works one lane. It is data infrastructure for the application database, the development, testing, staging, and demo environments where relational correctness is the bar and a copy of production is a liability. It connects to a live PostgreSQL database, reads the current schema, and generates connected, referentially consistent rows that fit it, so a child row lands on a parent that exists rather than on an ID that was never created. Describe what you want in plain language and it fills the database in one command:

seedfast seed --scope "500 customers with orders, payments, and a realistic spread of account ages"

Because those rows are invented from the schema and never sampled from production, no production access is required and no production rows are involved, which is why a generated dev or staging database sidesteps the copy-of-production liability above. That is a consequence of generating rather than copying, not a compliance claim. Seedfast is not built for lane one; it does not train your models or feed an analytics warehouse a replica of production statistics, work that wants a different tool and a different definition of good.

Frequently asked questions

Is synthetic data as good as real data?

It depends entirely on the job. On statistical work, where fidelity to a real distribution is what counts, carefully sampled real data is the benchmark and synthetic is judged by how near it gets. On an application database, where all that matters is that every row satisfies the schema, a copy of production is more hazard than help and well-formed synthetic data wins outright.

Does synthetic data work for training machine-learning models?

Yes, and that is squarely lane one. A model learns the pattern that dominates its training set, so it tolerates a defect rate that would wreck an application database, which is why generated data can train a working model even when it is imperfect. Two things decide how well it works. One is how faithfully the synthetic set reproduces the real distribution, rare cases included. The other is whether you validated the result on held-out real data rather than on more synthetic data.

Can I just use a copy of production for my test database?

You can, and plenty of teams do, but it hides its costs. A production copy carries real customer data into environments with weaker controls, weighs enough to slow every reset, and goes stale the day someone ships a migration. Worse for testing, it holds only the cases that already happened, so the edge case your feature needs may not be there at all. The synthetic data vs production data trade-off usually favors generating the rows you need over importing the ones you have.

When is real data the right choice?

Whenever the job is statistical and you are cleared to use it. Model training, evaluation, and analytics all read the aggregate and absorb a few odd rows, so they benefit from the fidelity only real data carries for free. The two gates are permission and freshness; fail either and high-fidelity synthetic data steps in.

Match the source to the job

The reason "synthetic data vs real data" stays unsettled in the abstract is that it was never one question. It is at least two, wearing the same words. Sort your work into the statistical lane or the application lane, check which property decides quality there, and the choice that felt like a coin toss turns fairly obvious. For the application lane, where a single dangling reference takes down the whole run, Seedfast reads your live schema and generates connected data without touching production. Run your first seed in a couple of minutes, or read the pricing first, a free plan that asks for no card and then flat plans at $16 and $69 a month.

Originally published at seedfa.st.

Pytest Database Fixtures That Stay Fast and Honest

Mikhail Shytsko — Sat, 18 Jul 2026 00:00:00 +0000

A test that talks to Postgres has to get its data from somewhere, and most pytest suites make that call badly. Either every test rebuilds the whole database and the run crawls, or a pile of hand-written setup fixtures drifts away from the schema until one Tuesday migration turns a wall of green tests red. Pytest database fixtures are supposed to be the fix here, not the source of the pain. They're dependency injection for your tests — a way to declare "this test needs a database in this state" and let pytest wire it up. Things go wrong when a fixture quietly becomes the place you park slow I/O or a stack of hardcoded rows.

This guide walks the pieces that decide whether a database suite stays fast and trustworthy — what the fixture scopes actually cost, the transactional rollback pattern that spares a Postgres suite from truncating tables between tests, and where seeding a realistic baseline belongs so your fixtures stop hauling around data they were never meant to own. The code assumes SQLAlchemy 2.0 and pytest-django 4.x, the current stable lines of both.

Key Takeaways

A fixture is dependency injection, not a hook for slow setup. The moment a fixture starts rebuilding a database on every call, the scope you gave it is the whole cost of your suite.
Scope is the lever nobody adjusts. A session-scoped connection built once and reused is the difference between a suite that finishes in seconds and one that reconnects to Postgres a thousand times.
Roll back, don't truncate. Wrapping each test in a transaction and rolling it back at teardown gives you a clean slate without touching a single TRUNCATE, and SQLAlchemy 2.0 makes the recipe short.
Deferred foreign keys are the trap in that pattern — a constraint marked DEFERRABLE INITIALLY DEFERRED only fires at commit, and a rollback fixture never commits, so it can hide a broken row your production code would reject.
Seedfast seeds a realistic, schema-consistent baseline once — locally or as a step before the test job in CI — so pytest's own scoping and rollback handle isolation on top of it. There's no plugin to install into the fixture path and no per-test call out to a generator.

Why pytest database fixtures go wrong before they go slow

Speed is the symptom people notice, but correctness usually breaks first. A fixture that inserts user_id=42 and asserts against it works fine until another test deletes user 42, or CI starts its sequences from a different number, or two parallel tests both claim the same primary key. The failure message then reads expected 3, got 0 and points at application code that has nothing to do with the real problem, which is that the data the test assumed is gone.

Shared mutable state is the root of nearly all of it. When several tests read and write the same rows, order starts to matter, and a suite where order matters can't run in parallel. A tearDown that deletes what the test created looks tidy until one test raises halfway through, skips its cleanup, and leaves rows behind that quietly poison everything after it. What each test actually needs is a starting state it can trust and a guarantee that nothing it writes leaks into the next one — scope and transactional rollback are how you get both.

Fixture scopes: function, class, module, session

Every pytest fixture has a scope, and scope decides how often the setup and teardown code runs. The pytest docs list five values — function, class, module, package, and session — ordered from narrowest to broadest. A function fixture (the default) is torn down at the end of each test. A session fixture is built once when first requested and destroyed only when the whole run ends.

import pytest

@pytest.fixture(scope="function") # the default — rebuilt for every test
def payload():
    return {"email": "ada@example.com"}

@pytest.fixture(scope="module") # once per test file
def api_client():
    client = make_client()
    yield client
    client.close()

@pytest.fixture(scope="session") # once for the entire run
def engine():
    engine = create_engine(TEST_DATABASE_URL)
    yield engine
    engine.dispose()

For database work the choice is not cosmetic. Building a SQLAlchemy Engine and opening a Postgres connection is expensive enough to dominate the clock on any suite past a few dozen tests, so the engine and any read-only reference data belong at session scope, while the per-test transaction that isolates writes belongs at function scope. The table below is the trade each level makes.

Scope	Setup runs	Reuse is safe when	Reuse bites when
`function`	Every test	Always — full isolation, no shared state	Never a correctness risk, only a speed one
`class`	Once per test class	Tests in the class only read the shared value	A test mutates it and a later one depends on the original
`module`	Once per file	The fixture is a connection, engine, or read-only data	Tests in the file write to it expecting a clean slate
`session`	Once per run	The value is genuinely immutable or self-cleaning	Any test mutates it — the mutation leaks to every later test

The pattern that holds up keeps the expensive, stable things at a broad scope and layers a narrow-scoped fixture on top for isolation — the trick behind the rollback pattern below.

The transactional rollback pattern

Here is the single most useful pattern for a Postgres test suite, and the one thin tutorials skip. Instead of resetting the database between tests, you open a transaction before each test, run the test inside it, and roll it back afterward. Postgres then discards everything the test wrote and hands the next test the same baseline it started from, without truncating a single table. A rollback is far cheaper than a TRUNCATE ... CASCADE plus a reseed, so the suite stays fast as it grows.

SQLAlchemy: bind a Session to an outer transaction

The catch is that ORM code wants to call session.commit(), and a naive setup would commit straight through to the database and defeat the rollback, a problem SQLAlchemy heads off with SAVEPOINTs. You begin a real transaction on a raw connection, bind the Session to that connection, and tell the session to implement its own commit/rollback as SAVEPOINTs inside your outer transaction.

In SQLAlchemy 2.0 this is one parameter. The official recipe for joining a Session into an external transaction passes join_transaction_mode="create_savepoint", and the docs note that the event handlers older versions needed to reset the nested transaction are no longer required.

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

TEST_DATABASE_URL = "postgresql+psycopg2://app:app@localhost:5432/app_test"

@pytest.fixture(scope="session")
def engine():
    engine = create_engine(TEST_DATABASE_URL)
    yield engine
    engine.dispose()

@pytest.fixture
def db_session(engine):
    connection = engine.connect()
    transaction = connection.begin() # outer transaction, never committed
    session = Session(bind=connection, join_transaction_mode="create_savepoint")
    try:
        yield session
    finally:
        session.close()
        transaction.rollback() # discard everything the test wrote
        connection.close()

A test that uses db_session can insert rows, call session.commit() as many times as its code path needs, and read back exactly what committed — all against a SAVEPOINT. When the fixture tears down, the outer transaction.rollback() throws the whole thing away. Because the engine is session-scoped and only db_session is function-scoped, you pay for the connection a single time while every test still gets its own clean transaction.

pytest-django: the django_db marker

If you're on Django, pytest-django gives you the same isolation without wiring the transaction yourself. Mark a test with @pytest.mark.django_db and, per the pytest-django database docs, it runs inside a transaction that is rolled back at the end — the same mechanism Django's own TestCase uses.

import pytest
from django.contrib.auth import get_user_model

@pytest.mark.django_db
def test_new_user_is_active():
    User = get_user_model()
    user = User.objects.create_user("ada@example.com", password="pw")
    assert user.is_active

This marker has one important exception worth flagging. When your code relies on transaction.on_commit() hooks, or a test spins up the live_server fixture, or you specifically want to assert real commit-and-rollback behavior, the rollback wrapper works against you, because nothing ever commits and those paths never fire. For those cases pass transaction=True, which flushes the database between tests instead of wrapping them:

@pytest.mark.django_db(transaction=True)
def test_email_is_queued_on_commit():
    # on_commit hooks actually run here, because the test really commits
    register_user("ada@example.com")
    assert len(mail.outbox) == 1

The docs are blunt about the cost. Flushing the database between tests runs much slower than rolling a transaction back, so reach for transaction=True only when you're actually testing transaction behavior, and default to the plain marker everywhere else.

The deferred-constraint edge case

Rollback isolation has one failure mode worth knowing before it bites you in production. A foreign key declared DEFERRABLE INITIALLY DEFERRED is not checked when you run the INSERT; PostgreSQL's docs state plainly that "DEFERRED constraints are not checked until transaction commit." A rollback fixture never commits its outer transaction, and even the session.commit() calls inside a test only release SAVEPOINTs rather than committing the top-level transaction. So a row that dangles a deferred foreign key at nothing will sail through a rollback-isolated test and then blow up the first time real application code commits it.

When a test genuinely needs to exercise that deferred check, force it with SET CONSTRAINTS ALL IMMEDIATE at the point you want the constraint evaluated, or run that specific test with real commits via @pytest.mark.django_db(transaction=True) instead of the rollback path.

-- inside the test's transaction, before you expect the check to fire
SET CONSTRAINTS ALL IMMEDIATE;
-- a dangling deferred FK now raises here instead of hiding until commit

Most suites never hit this, because most schemas don't defer their constraints. If yours does — cyclic references between two tables are the usual reason — keep at least one commit-based test on that path so the rollback pattern isn't silently covering for a bug.

Seed the baseline once, not per test

Rollback keeps tests from stepping on each other, but it puts nothing in the database to begin with. Something still has to establish the realistic starting state your integration tests query against — the customers and their orders, the reference tables they join to — and the wrong place to do that is inside a per-test fixture that regenerates it every time.

Seed it once, ahead of the run — one CLI command before you invoke pytest locally, or a step after migrations and before the test job in CI. Seedfast reads your live schema and generates realistic, connected rows that satisfy its constraints, so a single seedfast seed fills the whole database with a baseline your tests can read without you hand-writing a row of it. Pytest's scoping and the rollback fixture take it from there, every test seeing the baseline and rolling back its own writes on top.

# conftest.py — assert the baseline is present, fail loud if it isn't
import pytest
from sqlalchemy import text

@pytest.fixture(scope="session", autouse=True)
def seeded_baseline(engine):
    with engine.connect() as conn:
        rows = conn.execute(text("SELECT count(*) FROM customers")).scalar_one()
    if rows == 0:
        pytest.exit(
            "Test database is empty. Seed a baseline first:\n"
            " seedfast seed --scope 'a few customers with orders and line items'",
            returncode=1,
        )

That autouse session fixture doesn't generate anything — it checks that the baseline seeded outside the test run actually landed and stops the suite with a useful message if someone forgot to seed. Compare that shape with the alternative some tools push, where every fixture calls out to a generation service at test time and couples your setup to a network call on the hot path. One seed step up front, described in plain English with the --scope flag, keeps generation out of the loop, and the getting-started guide covers seedfast connect and seedfast seed for the first run.

factory_boy vs. raw SQL fixtures vs. schema-aware seeding

These three approaches solve overlapping problems, and the honest answer is that a mature suite often uses more than one. The question is which job each is actually good at.

Approach	Best at	Cost on a schema change	Foreign keys & constraints
Raw SQL fixtures	Tiny, fixed reference data you rarely touch	Manual edit every migration; drifts silently	You order the inserts and satisfy every FK by hand
`factory_boy`	The one specific object a test asserts on	Edit the factory when its model changes	Coded per relationship via `SubFactory`
Schema-aware baseline (Seedfast)	The realistic bulk every test queries against	None — it re-reads the schema each run	Handled from the live schema; values aren't repeatable

Raw SQL fixtures are the fastest thing to write and the first to rot. A hand-maintained INSERT script freezes the table shape on the day you wrote it, and the next NOT NULL column turns it into a failing insert three layers removed from the test. factory_boy earns its place for the object under test — when a test needs "an admin user with a suspended subscription", a factory builds precisely that, with sequences and SubFactory relationships, and reads clearly at the call site.

import factory
from myapp.models import User

class UserFactory(factory.django.DjangoModelFactory):
    class Meta:
        model = User

    email = factory.Sequence(lambda n: f"user{n}@example.com")
    role = "member"
    is_active = True

# in a test: the exact object this test asserts on
user = UserFactory(role="admin", is_active=False)

Where factory_boy stops paying is the background. Populating fifty tables of realistic, foreign-key-valid data so a reporting query has something to run against means hand-defining a factory per table and keeping each in step with migrations — the volume a schema-aware baseline covers without the upkeep. The two are complementary rather than rival. Seed the connected bulk once, build the single asserted-on object with a factory inside the test, and treat it as test data management split by job rather than one tool doing both.

When to seed per-test vs. once-per-suite

Default to seeding once and isolating with rollback. It's the fast path, and it fits the large majority of integration tests, which read the baseline, write a little, assert, and don't care whether their neighbor's rows exist. Session-scoped baseline plus function-scoped transactional rollback is the shape to reach for first.

Re-seeding for an individual test earns its cost only when the test needs a genuinely different data shape than the baseline provides. An empty-state UI test wants a customer with zero orders. For pagination you might need one customer sitting on two hundred, and a permissions test often depends on a specific matrix of roles the baseline doesn't happen to contain. In each of those, build the exact rows with a factory inside a function-scoped fixture, still under the rollback so the special-case data disappears at teardown.

The trap is the middle ground where a suite re-seeds the whole database per test out of caution — that's how a five-minute run becomes forty. If a test queries for "a customer with at least one order" instead of assuming customer 42, it can share the baseline, and most tests can be written that way with a little discipline about not hardcoding IDs. The E2E test fixtures guide works the same principle from the browser-testing side.

Running it in CI

The CI story is short because most of it isn't pytest-specific. You bring up a Postgres service container, gate it on pg_isready so no step races a database that isn't listening, migrate, seed the baseline, then run pytest. The seed step lands last, immediately before the tests read the database. The YAML below uses Seedfast as that seed step; any seed script slots into the same position.

      - run: pip install -r requirements.txt
      - run: alembic upgrade head # migrate first
        env:
          DATABASE_URL: postgresql+psycopg2://app:app@localhost:5432/app_test

      - run: npx seedfast seed --scope "a few customers with orders and line items" --output json
        env:
          SEEDFAST_API_KEY: ${{ secrets.SEEDFAST_API_KEY }}
          SEEDFAST_DSN: postgresql://app:app@localhost:5432/app_test

      - run: pytest -q # then read the database
        env:
          TEST_DATABASE_URL: postgresql+psycopg2://app:app@localhost:5432/app_test

The full mechanics of the service container, the health-check gate, and the migrate-then-seed ordering get their own walkthrough in seeding a Postgres test database in GitHub Actions — there's no reason to repeat it here. The Seedfast-specific CI setup, including the API key and non-interactive scoping, lives in the CI/CD database seeding docs. Whichever way you seed, the running order is the load-bearing part, and this is database seeding applied to a pytest job like any other.

A complete conftest.py, end to end

The complete file below wires together a session-scoped engine, an autouse guard that fails fast when the baseline is missing, and the function-scoped pytest Postgres fixture (db_session) that every test depends on. Drop it at the root of your test tree and the whole pytest test database setup fits in one file: seed once before the run, and each test gets the baseline plus perfect isolation.

# conftest.py
import os
import pytest
from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

TEST_DATABASE_URL = os.environ.get(
    "TEST_DATABASE_URL",
    "postgresql+psycopg2://app:app@localhost:5432/app_test",
)

@pytest.fixture(scope="session")
def engine():
    """Built once for the whole run — opening connections is the expensive part."""
    engine = create_engine(TEST_DATABASE_URL)
    yield engine
    engine.dispose()

@pytest.fixture(scope="session", autouse=True)
def seeded_baseline(engine):
    """Assert the baseline seeded before pytest ran (locally via the CLI, or a
    prior CI step) is actually present. Fail loud rather than run empty tests."""
    with engine.connect() as conn:
        customers = conn.execute(
            text("SELECT count(*) FROM customers")
        ).scalar_one()
    if customers == 0:
        pytest.exit(
            "Test database is empty. Seed a baseline first:\n"
            " seedfast seed --scope 'a few customers with orders and line items'",
            returncode=1,
        )

@pytest.fixture
def db_session(engine):
    """Function-scoped session bound to an outer transaction that is always
    rolled back. Tests may call session.commit() freely — it commits to a
    SAVEPOINT, and the outer rollback discards everything at teardown."""
    connection = engine.connect()
    transaction = connection.begin()
    session = Session(bind=connection, join_transaction_mode="create_savepoint")
    try:
        yield session
    finally:
        session.close()
        transaction.rollback()
        connection.close()

A test then reads like plain application code, and the isolation is invisible:

def test_order_total_sums_line_items(db_session):
    customer = db_session.execute(
        text("SELECT id FROM customers LIMIT 1")
    ).scalar_one()

    db_session.execute(
        text(
            "INSERT INTO orders (customer_id, status) "
            "VALUES (:cid, 'open') RETURNING id"
        ),
        {"cid": customer},
    )
    db_session.commit() # commits to a SAVEPOINT, not the real database

    open_orders = db_session.execute(
        text("SELECT count(*) FROM orders WHERE status = 'open'")
    ).scalar_one()
    assert open_orders >= 1
    # teardown rolls the outer transaction back — the next test never sees this order

Run it, and the moment the fixture tears down, the order this test created is gone. There's nothing to clean up or truncate, and the baseline the whole suite reads against sits exactly as seedfast seed left it.

Frequently asked questions

Do I need a pytest plugin to seed a database?

No. Seeding and test isolation are separate jobs, and pytest already handles the isolation half natively through fixture scopes and transactional rollback. For the seeding half you only need something that fills the database before the run — a CLI command locally, a step in CI — not a plugin wired into your fixture path. Seedfast runs as that one seed step and leaves the fixtures to pytest, so there's nothing extra to install into the test process itself.

What's the difference between a pytest fixture and database seeding?

A fixture is a pytest construct that provides a test with what it needs and cleans up afterward — a connection, a session, an object under test. Seeding is the separate act of putting a realistic starting dataset into the database before any of that runs. A fixture can assume the seeded data exists and query it, but it shouldn't be regenerating that dataset on every test, which is what makes a suite slow.

Should I use transaction rollback or truncate tables between tests?

Prefer rollback. A rollback discards the test's writes in one step, while a truncate-and-reseed pays table-by-table I/O between every pair of tests — and with SQLAlchemy's join_transaction_mode="create_savepoint" your ORM code can still call commit() freely. Truncation is only the better tool when you must test real commit behavior — on_commit hooks, cross-connection visibility — which is exactly the case pytest-django covers with @pytest.mark.django_db(transaction=True).

Does factory_boy replace database seeding?

Not for the bulk of your data. factory_boy is excellent for building the specific object a test asserts on, with sequences and SubFactory relationships. It's a poor fit for populating fifty foreign-key-linked tables of realistic background data, because that means writing and maintaining a factory per table against every migration. Seed the connected volume once with a schema-aware step, and use factory_boy for the one-off object each test cares about — the two work together.

How do I seed a Postgres test database for pytest in CI?

Run the seed step after migrations and before pytest. Bring up a Postgres service container, gate it with pg_isready, apply your migrations, then run npx seedfast seed against the CI database with SEEDFAST_API_KEY and a connection string, and finally invoke pytest pointed at the same database. The GitHub Actions guide covers the container and health-gate mechanics, and the CI/CD database seeding docs cover the key and non-interactive scoping.

Where to take it from here

The shape that holds up is layered. At the bottom sit a session-scoped engine and a baseline seeded once; on top of them, function-scoped transactional rollback provides isolation while a factory handles the single object a given test asserts on. Get that stack right and your fixtures stop being a maintenance surface — they go back to being plain dependency injection, which is all they were ever meant to be.

If the "seed a realistic baseline once" step is the piece you're missing, point Seedfast at a local or CI database and let it read the schema and fill it. The getting-started guide takes you from seedfast connect to a seeded database, and the free plan doesn't ask for a card.

Originally published at seedfa.st.

Demo Data Looks Real Until Someone Clicks Into It

Mikhail Shytsko — Sat, 18 Jul 2026 00:00:00 +0000

The account looks lived-in — forty companies down the sidebar, avatars scattered across a team view, invoices going back six months, the kind of screen you'd happily drop into a pitch deck. Then the prospect clicks into one of those invoices on the call, and the customer it's billed to isn't anywhere in the customer list. Upstream, a demo data generator had filled every table with believable rows and never once checked whether a row in one table points at a row that exists in another.

That specific failure is what this page is about. Filling a database so it reads as real to a human is a different problem from producing a flat file of plausible values, and most tools that surface when you search for a demo data generator solve the second problem cleanly while leaving the first untouched. They're genuinely good at the flat file, and they run out of road the moment your demo spans tables that reference each other, which — for anything shaped like a real product — happens on the first click.

Key Takeaways

Demo data and test data are different jobs. Demo data has to look coherent to a person watching a screen; test data has to trip the edge cases a CI check reads, and no one will ever look at it.
The flat generators that rank for this term — Mockaroo, generatedata.com, Beeceptor — fill one table at a time, so foreign keys between tables don't line up. That's fine for a single CSV or a mock API, and a real problem once a demo spans related tables.
A convincing demo has shape: a few large accounts over a long tail of small ones, signup dates that precede activity, invoice totals that reconcile with their line items.
"Demo data for a SaaS" is not the same as a sales-demo-automation platform such as Reprise or Demostack. Those personalize the sales pitch itself and are typically a much larger purchase than a data generator.
Seedfast generates relational demo data straight against your application's live schema in a single run. The free plan takes no card.

What "demo data" actually means

Demo data is data you load into your own product's database so the thing looks real to whoever is watching it. A prospect clicking through a trial account, an investor sitting through a walkthrough, a new user poking at a sandbox on day one — each of them is judging the product partly on whether its data feels like a real company has been using it. Test data answers a narrower question. It exists to drive assertions, so it cares about the null field, the boundary value, and the one row that makes a CI check go red, and nobody ever admires how it looks.

The two overlap enough that people reach for the same tools for both, then get surprised when a generator tuned for one disappoints at the other. A dataset built to break a pagination bug rarely reads like a tidy sales demo, and one assembled to charm a prospect won't go hunting for the ugly corner your test suite lives for.

One disambiguation is worth making before we go further, because this term pulls two very different searchers. If what you want is software that assembles and personalizes the sales pitch itself — swapping in a prospect's logo, staging a guided product tour, tracking who watched what — that is a sales-demo-automation platform like Reprise, Demostack, or Saleo, and it's a much larger purchase aimed at revenue teams. This page is about the other job of getting realistic rows into your own application's database. If you landed here looking for the pitch-automation category, that's a different shelf entirely.

Why flat row generators break the moment tables relate

Give the incumbents their due first, because they earn it. Mockaroo will hand you a realistic CSV, JSON, or SQL file in under a minute with no install, and it doubles as a mock-API host (per mockaroo.com, as of July 2026). generatedata.com is open-source under the GPL and free to self-host, with roughly thirty data types and a stack of export formats (per the project's GitHub, benkeen/generatedata, as of July 2026). Beeceptor advertises 300-plus field generators and can turn what it produces into a live mock REST API on the spot (per beeceptor.com, as of July 2026). SingleStore ships a basic column-by-column dummy-data widget too (as of July 2026). For a quick single-table export or one faked API response, reaching past any of these would be overkill.

The shared limit is structural, and it isn't a bug in any of them. Each generates one table at a time, with the columns of that table drawn independently. There is no live database in the loop, so nothing connects the value in one table to a value in another.

Watch where that lands on a product. A SaaS demo usually needs organizations that own users, users who hold subscriptions, subscriptions that produce invoices, and invoices backed by usage_events. Generate those five tables in five independent passes and the foreign keys are decorative. An invoice.customer_id is a believable integer that happens to point at no customer you created. Every account ends up on the same subscription tier because nothing wired tier to account. Drill into a single record on screen and the illusion falls apart, which is precisely the interaction a demo is built to invite. The Mockaroo alternative write-up walks through this exact wall in more depth, and the test data generation rundown covers the same tools from the CI-suite angle.

psql makes it countable — in the sample above, eight of the ten invoices bill customers that don't exist, and the orphan count across the whole table comes to 174.

What a demo actually needs to look real

Valid foreign keys are only the floor, and a demo that clears referential integrity can still look obviously synthetic the second someone with domain sense looks at it, because real data carries a shape that random values don't.

Picture the customer list you'd actually want on screen. It would hold a handful of large accounts with dozens of seats and long histories sitting above a long tail of small accounts that signed up last week and barely logged in — not fifty identical companies each with the same three users. Signup dates would land before the first invoice rather than after it, and invoice totals would add up from their line items instead of floating free. None of that is exotic; it's the difference between rows generated with awareness of each other and rows that weren't.

This is the same coherence argument that realistic test data makes for a test suite, turned here to face the person watching. A test only fails when incoherence trips a check it happens to run. The demo has a harsher reviewer in the human watching it, who spots the seams fast, because a customer that predates its own signup or an order with no buyer is exactly the thing a skeptical prospect clicks straight toward.

Generating demo data against your own schema

The way out is to stop generating tables in isolation and start generating from the schema that already defines how they relate. Point a tool at the staging or demo database, let it read the live schema — every table, column, type, and foreign key — and have it write a connected dataset across all of them in one run, with no flat files left to reconcile by hand afterward.

That's the model Seedfast works in. It connects to your application's database, reads the current schema on every run, and generates rows that reference each other correctly, so an invoice lands on a customer who exists and a subscription belongs to an account that was created before it. You describe the demo you want in plain language and it fills the tables:

seedfast seed --scope "50 organizations with users, subscriptions, and 6 months of invoices"

There's no form to fill column by column and no per-table export to stitch back together afterward. Because it reads the schema fresh each time, the migration you ship next week gets picked up on the next run, with no saved definition to quietly break. It runs the same way as a CLI step against a staging connection string or as an MCP server inside an AI coding agent, so the agent building the demo can populate the database itself. There's a free plan that takes no card if you want to point it at a real schema before deciding anything.

Demo data generator options compared

The flat generators win the rows they were built to win — a single fast export, a hosted mock API — while a schema-aware tool takes over once those rows have to agree with each other.

Capability	Mockaroo	generatedata.com	Beeceptor	Seedfast
Reads your live database schema	No	No	No	Yes — on every run
Keeps foreign keys valid across tables	Manual — own web form only	Not documented	No	Yes — every child row points at a real parent
Multi-table demo in one run	No — one table at a time	No — one table at a time	No — one dataset at a time	Yes — all connected tables together
Runs without a live database	Yes — exports files	Yes	Yes	No — needs a connection string
Output	CSV, JSON, SQL, Excel	CSV, SQL, JSON, XML, and more	JSON, CSV, SQL, plus a live mock API	Rows written straight into your database
Price	Free ≤1,000 rows/file; paid $60–$7,500/yr	Free, open-source (GPL-3.0)	Free tier; no pricing on the tool page	Free plan, then flat monthly credits, no per-row metering
Best for	Fastest single flat file or a mock API	Free self-hosted flat exports	Turning generated rows into a mock API	Coherent multi-table demos against your own DB

Competitor facts verified as of July 2026: Mockaroo pricing per mockaroo.com/pricing; generatedata.com license and feature counts per the benkeen/generatedata GitHub repository; Beeceptor capabilities per beeceptor.com. In my read, all three are the right call when you need one flat table fast, though they become the wrong tool the moment a demo depends on tables referencing each other, which no amount of extra rows fixes.

The quickest check is a run against your own staging schema — that's what the free plan is for.

If you're weighing Mockaroo specifically, the Mockaroo alternative page has the full side-by-side.

Where this fits: trial accounts, staging, investor decks, and QA walkthroughs

The "make it look alive" problem shows up in more places than a sales call. A self-serve trial that greets a new user with an empty dashboard converts worse than one opening on a populated workspace. Investors want enough history behind the charts for them to read as a story, and a QA walkthrough of a new feature lands better against data that resembles a real tenant than against three rows named "Test User". What all of these share is the need for a database that looks used, without a real customer's information anywhere in it.

That last constraint is why a demo environment and a staging environment tend to converge. Both have to look real and neither should be a copy of production. Generating the data from the schema satisfies both at once. Nobody's personal data leaves production, because none of it was ever involved; what the generator does see is schema structure — table and column names — which a regulated team will want to review. And there's no anonymization script to keep in sync as the schema moves.

Frequently asked questions

What is demo data?

Demo data is fabricated data you load into your own application's database so the product looks real to whoever is watching the screen. The bar is visual coherence: names that read like a genuine customer base, dates in a sensible order, and records that connect when someone drills into them — down to the invoice that resolves to a real customer.

What's the difference between demo data and test data?

Test data exists to exercise your code — edge cases, null fields, boundary values, the exact rows an assertion checks — and no one ever looks at it. Demo data exists to be looked at, so it has to hold together as a believable picture even though no test will read it. One generator can serve both, but they pull in different directions, and a dataset tuned to break a pagination bug rarely resembles a clean sales demo. If you actually wanted software that assembles the pitch itself, that's a sales-demo-automation platform like Reprise or Demostack — a separate, pricier category from a data generator.

Can a demo data generator handle foreign keys between tables?

Some can; most can't. The flat generators that dominate this space produce one table at a time, so a foreign key column fills up with plausible integers that don't necessarily match any row you generated in the parent table. A schema-aware generator — Seedfast is built this way — reads the live database first and writes rows that reference real parents, so an invoice belongs to a customer who exists and a subscription belongs to an account created before it. That difference decides whether a demo survives someone clicking into it or only survives a screenshot.

Is Mockaroo good for generating demo data?

For a single flat table, yes — it's the fastest path to a realistic CSV or SQL export, and for mocking one API response it's hard to beat. The boundary appears when a demo needs several tables to agree with each other — Mockaroo generates each table independently and can only relate tables you rebuild by hand in its own web form, not the ones already defined in your database (as of July 2026, per mockaroo.com), so a multi-table demo turns into exporting each file and wiring the keys yourself.

How do I generate demo data for a SaaS product without using real customer data?

Generate it from your schema instead of copying a slice of production. A schema-aware tool like Seedfast connects to a staging or demo database, reads the tables and their relationships, and writes a fresh set of organizations, users, subscriptions, and activity that never belonged to anyone — no export of real customers, no anonymization pass to maintain. Because production rows never leave production, there's no PII in the demo to leak. The generator still reads schema metadata like table and column names, so a regulated team should glance at what those names reveal first.

Try it against your own schema

If your demos keep springing leaks on the first click, the fix isn't more realistic-looking columns — it's data that knows how your tables connect. Seedfast reads your live schema and writes a connected dataset across every related table, so the account you show someone holds together when they start clicking. The free plan takes no card, and there's no table or seed ceiling on it to trip over.

Related guides

Mockaroo alternative — the full side-by-side when your data has foreign keys
Realistic test data — the coherence argument, aimed at a test suite
Staging without production data — the same schema-first approach for staging environments
Test data generation: 7 methods compared — the toolbox when you need CI data, not a demo

Originally published at seedfa.st.

MCP Servers for Test Data: What Exists and What Each One Does

Mikhail Shytsko — Sat, 18 Jul 2026 00:00:00 +0000

Your coding agent is connected, the migrations have run, and the database behind them is empty. Ask around for an MCP server to fix that and the shelf looks crowded — mcpservers.org lists more than 870 in its database category alone (mcpservers.org/category/database) — but almost none of them do the job you're picturing. They hand the agent a way to run SQL against data that already exists. What you actually want, MCP test data, is the opposite errand, a server that puts realistic, connected rows into a schema that currently holds none.

This page surveys the servers that do that, and the much larger category that doesn't, so you can tell one from the other before wiring anything into an agent that can write to your database. It reflects a landscape where the "which MCP gives my agent test data" shelf is still surprisingly thin.

Access or generation? The line that splits the category

Here is the distinction that decides everything downstream. A database-access MCP lets an agent run SQL against data that already exists — it inspects tables, reads schemas, executes queries, and sometimes writes individual rows. A test-data MCP generates the realistic, relationally consistent data in the first place and writes it into the schema. Against an empty database the access kind is useless, since there's nothing there to query, and the generation kind is what fills it.

Most of those 870-plus database servers are the first kind. Point one at a fresh container or a just-migrated branch and it sits idle, because a query needs rows to return and there aren't any. You could skip the MCP entirely and ask the agent to write the INSERT statements itself, which works right up until the foreign keys have to line up across a dozen tables — the point where model-written seed SQL tends to come apart, walked through in the generate test data with AI playbook.

MCP test data servers at a glance

Four options cover almost everyone reading this, and the table below sorts them by what they actually put in your database, not by how they market it.

MCP server	What it does	Pricing model	Best for
Seedfast MCP	Generates connected, realistic rows and writes them into your own database in place, from one `seedfast_run` call	Monthly credits (free plan, then $16 / $69), no per-token meter	Seeding your own Postgres, Supabase, or Neon from an agent or CI
Tonic Fabricate MCP	Bridges Claude or Cursor to Fabricate's hosted generation agent, which synthesizes data across several engines	Plan + credit/token metering (Plus $29/mo, as of July 2026)	Interactive, multi-engine generation from the editor
SeedBase Test Data (OSS)	FK-consistent data generator, open source	Free (open source)	Self-hosted setups and OSS-only shops
Database-access MCPs (Oracle, Microsoft SQL, Cloudflare D1, DBmaestro, Salesforce)	Let an agent query and administer data that already exists; do not generate it	Free or bundled with the platform	Inspecting, querying, or operating a database that already has rows

Generation lives in the top two rows. Everything below only reads data that already exists, which is why that one split matters more than any feature comparison you could run inside either camp.

Seedfast MCP seeds your database in place

Seedfast runs as an MCP server that a coding agent launches over the Model Context Protocol and then calls with a single tool, seedfast_run. You describe the data you need in plain language, it reads your live schema, and it writes realistic rows into the database you're already connected to, with the foreign keys resolving so the rows genuinely hang together rather than pointing at records that were never created. Nothing gets shipped out to a hosted service and loaded back afterward; the data lands directly in your own Postgres, whether that's a Supabase branch, a Neon branch, an RDS instance, or a throwaway container spun up for a test run.

What sets its KIND of MCP apart from the rest of this list is where the work actually happens — it seeds in place, so the database you connect is the one that fills. Pricing stays flat too, a monthly plan with a free tier under it and no per-token meter so a CI seed costs the same as one you run by hand, and because it's CLI-first, the same call the agent makes also drops into a pipeline as a plain command after your migrations. The client config is the same npx block across Claude Code, Cursor, and VS Code; the MCP setup guide carries the per-client version, with focused walkthroughs for Claude Code and Google Antigravity.

Tonic Fabricate's MCP bridges to a hosted agent

Tonic launched a Fabricate MCP on July 1, 2026, and it is a real, capable server. Connect Claude or Cursor to it and you can generate synthetic data without leaving the editor, the same conversational flow Fabricate offers in the browser. Where it differs from an in-place seeder is the location of the work. The MCP hands your request to Fabricate's hosted generation agent, which is metered by credit and token, and the generated data comes back to be loaded, rather than the seed running against the database you're connected to.

Fabricate's reach is the genuine draw. It connects live databases and generates into Postgres, MySQL, Oracle, and Databricks, with exports to formats an in-place Postgres seeder doesn't touch. If your work spans several engines, that breadth is a real reason to pick it. The cost model is the trade to weigh against it. Generation is metered — a free tier carrying $5/month in credits for a personal email or $10/month with every model unlocked for a work email, a Plus plan at $29/month including $25 in credits, then pay-as-you-go around $0.17 a standard turn (tonic.ai/pricing; figures as of July 2026, worth re-checking before you budget). One practical note for anyone hunting the registries — as of July 2026 the Fabricate MCP wasn't listed on Glama, mcp.so, or Smithery, so you add it from Tonic's own docs rather than a one-click install. The longer head-to-head lives in Seedfast vs Tonic Fabricate.

Open-source and community servers

Both registries carry a long tail of community-built servers. Glama alone scans tens of thousands (glama.ai/mcp/servers) and groups the relevant ones under a Databases category, while mcpservers.org keeps a parallel list. Most of what you'll find there is the access kind, though a smaller number aim at generation — SeedBase Test Data, listed on Glama, describes itself as generating FK-consistent synthetic data for a database from an AI assistant (glama.ai/mcp/categories/databases).

For anyone who wants something self-hosted and open-source, the registries are the place to start. Two cautions travel with that choice, though. Quality and upkeep swing widely across a directory that size, so the scan score and the date of the last commit tell you more than the description does. And an agent with a write path into your database deserves the same scrutiny you'd give any dependency, community server or not.

The bigger category is database-access MCPs

The reason the shelf looks so full is that nearly every major data platform now ships an MCP, and they are overwhelmingly access servers. Oracle exposes its database to agents through SQLcl (oracle.com/mcp). Microsoft's SQL MCP Server lets an agent work with tables, views, and stored procedures (Microsoft Learn). From Cloudflare, the Bindings MCP reaches D1 databases along with the rest of the Workers stack (blog.cloudflare.com). DBmaestro's, announced in April 2026, puts release automation and CI/CD orchestration for database pipelines under natural-language control (InfoQ), while Salesforce's surfaces org data and metadata to an agent.

Every one of these earns its place, and not one of them generates test data. They let an agent read, query, or operate a database whose rows are already there. Aim any of them at an empty schema and it has nothing to act on; a generation server is what changes that. Plenty of teams keep one registered alongside an access server, because the day you spin up a blank schema is exactly when the query tools have nothing to say.

Which MCP server should you wire up?

Start from what you actually have in front of you. If the database already holds data and you want the agent to inspect it, query it, or run migrations against it, one of the access MCPs above is the whole answer, and bolting on a generator you'll never call is just clutter in the config.

If the schema is empty — a fresh container, a branch cloned thin, a demo environment with no rows — you need a generation server, and there the choice turns on interface and cost. For in-place seeding of your own Postgres from an agent or a CI step, on flat pricing, Seedfast is the one I reach for, and since I work on it you should read that as a stated interest rather than a neutral ruling. Fabricate's breadth is hard to beat when you're generating interactively across several database engines from a browser or editor. And if you'd rather vet a self-hosted option yourself, the open-source servers on the registries are worth a look. If you're cross-shopping beyond MCP servers specifically, the best database seeding tool roundup covers ORM seeders and standalone generators side by side.

Frequently asked questions

What is an MCP server for test data?

A test-data MCP server is one whose tools generate realistic, relationally consistent rows and write them into a database, so an AI coding agent can populate an empty schema by describing what it needs in plain language. It's distinct from the far more common database-access MCP, which only reads or queries data that already exists. Seedfast is an example of the generating kind; the Oracle, Microsoft SQL, and Cloudflare D1 servers are examples of the access kind.

Is there an MCP server that generates data instead of just querying it?

Yes, a few. Seedfast generates connected rows and writes them into your own database in place. Tonic's Fabricate MCP generates through a hosted, credit-metered agent. And a handful of open-source servers, such as SeedBase Test Data on Glama, aim at the same job for self-hosted setups. Most database MCP servers, by contrast, only run SQL against data that is already there, which does nothing for an empty schema.

Does Tonic Fabricate have an MCP server?

It does. Tonic launched a Fabricate MCP on July 1, 2026, and connecting Claude or Cursor to it lets you generate synthetic data from the editor. The request runs through Fabricate's hosted generation agent, metered by credit and token (tonic.ai/pricing), rather than seeding your connected database directly. Registry listings haven't caught up yet, so Tonic's own docs are still where you install it from.

Are there open-source MCP servers for test data?

There are. The Glama and mcpservers.org registries list community-built servers under their database categories, and a few of them, SeedBase Test Data among them, sit on the generating side of the split this page draws rather than the querying side (glama.ai). Coverage and maintenance vary a lot across a registry that large, so check each server's scan score and recent commit history before you connect it to a database it can write to.

Which MCP server is best for database seeding?

The best MCP server for database seeding depends on where your database lives and how you pay. For seeding your own Postgres in place from an agent or a CI pipeline on a flat plan, Seedfast fits that shape (a stated interest — I maintain it). For generating across multiple engines from a browser or editor, Tonic Fabricate's reach is the stronger fit despite the metered cost. If you want to inspect or query an existing database rather than fill an empty one, you don't want a seeding server at all — an access MCP does that job.

The short version

Strip the 870-server shelf down and it sorts into two piles. Almost everything is an access MCP that reads a database already holding rows. A handful generate the rows instead — Seedfast in place on your own database, Fabricate through its hosted agent, a few open-source servers if you'd rather self-host and vet the code. If the problem you keep hitting is the empty schema an agent stalls on, Seedfast's free plan is enough to register one config block and watch a database fill from a single prompt, without a card and without any production data touching the run.

Originally published at seedfa.st.