Build a known, referentially valid baseline in a disposable database before Vitest integration tests run. Ordering, idempotency, per-worker isolation, and fast reseeding.

Seeding a Test Database for Integration Tests

Integration tests earn their keep by exercising real query paths against a real database, but that value evaporates the moment the database’s starting state is uncertain. If a test assumes three users exist and only finds them because a previous test happened to create them, it is not testing anything reproducibly — it is reading tea leaves. This guide is for engineers running Vitest 3.x integration suites against Postgres or a comparable SQL database in TypeScript who need a seeding workflow that produces a known, referentially valid baseline every run, in every worker, without leaking rows or serialising the whole suite. We cover ordering by dependency, idempotent seed scripts, per-worker isolation, and reseeding fast enough that it does not dominate the pipeline. The seed we build reuses the same factories described in factory functions vs. fixtures in Vitest, so unit and integration data share one definition.

Root Cause Analysis

Unreliable integration tests almost always trace back to an ambient database — one whose contents are a side effect of whatever ran before, rather than a deliberate baseline established by the test setup. Ambient state produces the two classic failures: a test that passes only because of rows a sibling left behind (a false green that will break the instant the order changes), and a test that fails because a sibling’s leftover row violates its assumption of an empty table. Both are order-dependence, and both are invisible until the suite is parallelised or shuffled, at which point they surface as maddening intermittency. The cure is to make the baseline explicit and self-contained: the setup, not history, decides what exists.

There is a further, quieter cost to ambient state: it makes failures non-reproducible. When a test’s outcome depends on accumulated history, a red build cannot be recreated locally, because your machine has a different history than CI did at that moment. Engineers then chase ghosts, re-running until the build goes green and concluding the failure was “just flaky”, when in fact the suite never had a defined baseline to fail against. A deliberately seeded, self-contained baseline turns every failure into something you can reproduce on demand simply by running the same seed, which is the whole point of an integration test: a red result must mean a real defect, not an accident of ordering.

The second root cause is referential invalidity. A relational schema encodes invariants — a post must have an author, an order must have a customer — as foreign keys. A seed that inserts a post before its author, or forgets the author entirely, either fails on a constraint or, worse, only works because constraints are disabled in the test database. Disabling constraints to make seeding easier is a false economy: it lets the seed create states the production schema would reject, so tests pass against data that could never exist in production. A correct seed respects the dependency graph and inserts in topological order, which is exactly why factory composition matters — building a post through the post factory pulls in a valid author automatically. Getting this right also underpins realistic API simulation, because the rows you seed should match the shapes you return when intercepting requests with MSW.

Topological seed order respecting foreign keys Users are seeded first, then posts that reference them, then comments that reference posts, so every foreign key resolves at insert time. Insert in dependency order users no dependencies posts FK author_id comments FK post_id reverse this order when truncating, so no FK is ever left dangling
Seed parents before children; truncate children before parents.

Reproducible Setup

Provision a disposable database and point Vitest at it. Use a container in CI and a throwaway local instance in development; never seed a shared staging database.

npm install -D vitest pg
docker run -d --name test-db -e POSTGRES_PASSWORD=test -p 5433:5432 postgres:16
export TEST_DATABASE_URL="postgres://postgres:test@localhost:5433/postgres"
Disposable per-job database versus shared staging Each CI job gets its own throwaway database so runs never contend, in contrast to the anti-pattern of many jobs seeding one shared staging database. Correct — one disposable database per job job A → db A job B → db B job C → db C no contention Anti-pattern — many jobs, one shared staging job A job B shared staging db flaky cross-talk
Give every CI job its own disposable database; never seed a shared staging instance.
// src/db.ts
import { Pool } from 'pg';

export const pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });

export async function migrate(): Promise<void> {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS users (
      id text PRIMARY KEY, email text UNIQUE NOT NULL, role text NOT NULL,
      created_at timestamptz NOT NULL
    );
    CREATE TABLE IF NOT EXISTS posts (
      id text PRIMARY KEY, author_id text NOT NULL REFERENCES users(id),
      title text NOT NULL, published boolean NOT NULL
    );
  `);
}

Implementation

Step 1 — Write an idempotent, ordered seed built from factories. The seed truncates in reverse dependency order, then inserts in forward order. Reusing the factories keeps the seeded shapes identical to unit-test shapes.

// tests/seed.ts
import { pool } from '../src/db';
import { makeUser } from '../factories/user';
import { makePost } from '../factories/post';

export async function seed(): Promise<void> {
  await pool.query('TRUNCATE posts, users RESTART IDENTITY CASCADE');

  const alice = makeUser({ id: 'seed-alice', email: 'alice@example.test' });
  const bob = makeUser({ id: 'seed-bob', email: 'bob@example.test', role: 'admin' });
  for (const u of [alice, bob]) {
    await pool.query(
      'INSERT INTO users (id, email, role, created_at) VALUES ($1,$2,$3,$4)',
      [u.id, u.email, u.role, u.createdAt],
    );
  }

  const post = makePost({ id: 'seed-post-1', title: 'Hello', published: true }, alice);
  await pool.query(
    'INSERT INTO posts (id, author_id, title, published) VALUES ($1,$2,$3,$4)',
    [post.id, post.authorId, post.title, post.published],
  );
}

Step 2 — Run migration and seed once per suite in a global setup. A Vitest globalSetup runs before any worker starts, so schema and baseline exist before the first test.

// tests/global-setup.ts
import { migrate, pool } from '../src/db';
import { seed } from './seed';

export async function setup(): Promise<void> {
  await migrate();
  await seed();
}

export async function teardown(): Promise<void> {
  await pool.end();
}
// vitest.config.ts
import { defineConfig } from 'vitest/config';

export default defineConfig({
  test: {
    globalSetup: ['./tests/global-setup.ts'],
    pool: 'forks',
  },
});

Step 3 — Isolate each test’s writes with a transaction. The baseline is seeded once; each test runs inside a transaction that rolls back, so no test sees another’s writes and the baseline is never eroded. This is the fast path detailed in resetting state between tests without slowing CI.

// tests/setup.ts
import { beforeEach, afterEach } from 'vitest';
import { pool } from '../src/db';

beforeEach(async () => { await pool.query('BEGIN'); });
afterEach(async () => { await pool.query('ROLLBACK'); });

Step 4 — Give each worker its own schema when parallelism matters. A shared connection serialises transactional tests. To parallelise, create a schema per worker keyed on Vitest’s worker id and set the search path so each worker seeds and mutates independently.

// tests/per-worker.ts
import { pool } from '../src/db';

export async function useWorkerSchema(): Promise<string> {
  const id = process.env.VITEST_POOL_ID ?? '0';
  const schema = `test_w${id}`;
  await pool.query(`CREATE SCHEMA IF NOT EXISTS ${schema}`);
  await pool.query(`SET search_path TO ${schema}`);
  return schema;
}

Step 5 — Add extra rows per test through the factory, never by editing the seed. A test that needs a fourth user builds one inside its transaction; because the transaction rolls back, the addition is invisible to every other test.

// tests/reports.test.ts
import { expect, it } from 'vitest';
import { pool } from '../src/db';
import { makeUser } from '../factories/user';

it('counts admins in the baseline plus a local addition', async () => {
  const extra = makeUser({ id: 'local-admin', email: 'x@example.test', role: 'admin' });
  await pool.query(
    'INSERT INTO users (id, email, role, created_at) VALUES ($1,$2,$3,$4)',
    [extra.id, extra.email, extra.role, extra.createdAt],
  );
  const { rows } = await pool.query("SELECT count(*)::int AS n FROM users WHERE role = 'admin'");
  expect(rows[0].n).toBe(2); // bob from the seed + the local admin
});
Seed-once, transaction-per-test lifecycle Global setup migrates and seeds a baseline once; each test opens a transaction, mutates, asserts, then rolls back to the baseline. Global setup migrate + seed once per test BEGIN → mutate → assert ROLLBACK to baseline Baseline intact next test starts clean
Seed the baseline once; each test borrows it inside a transaction and returns it on rollback.

Verification

Confirm the baseline is exactly what the seed declares and that it survives a full run unchanged. A baseline probe asserts the seeded counts; a run-order check proves independence.

// tests/baseline.test.ts
import { expect, it } from 'vitest';
import { pool } from '../src/db';

it('baseline has exactly the seeded rows', async () => {
  const users = await pool.query('SELECT count(*)::int AS n FROM users');
  const posts = await pool.query('SELECT count(*)::int AS n FROM posts');
  expect(users.rows[0].n).toBe(2);
  expect(posts.rows[0].n).toBe(1);
});

Run npx vitest run twice and confirm the baseline probe reports the same counts both times — proof that no test’s writes escaped their transaction. Then run with --sequence.shuffle; identical results across orders confirm the seed, not history, defines the world. Finally, add --pool.forks.maxForks=4 and verify the per-worker schema path keeps each worker’s counts correct in parallel.

Troubleshooting

Symptom: insert or update violates foreign key constraint. Diagnosis: the seed inserts a child before its parent, or a factory produced a reference to an id that was never persisted. Fix: insert in topological order (parents first) and build children through composing factories so their foreign keys point at rows you actually seed; truncate in the reverse order with CASCADE.

Symptom: counts drift upward across runs. Diagnosis: a test commits instead of rolling back, or opens a second connection outside the wrapped transaction, so its writes persist into the baseline. Fix: ensure the code under test uses the same pooled connection the transaction wraps, and confirm no test issues an explicit COMMIT; the baseline probe should catch this immediately.

Symptom: parallel workers deadlock or see each other’s rows. Diagnosis: every worker points at the same schema, so their transactions contend and their reads overlap. Fix: give each worker its own schema keyed on VITEST_POOL_ID and set search_path, or set fileParallelism: false if a single schema is acceptable for a small suite.

FAQ

Should I seed with raw SQL or with my ORM?

Use whichever your production code uses to write, so the seed exercises the same path your application does and cannot drift from real behaviour. If your app writes through an ORM, seeding through the same ORM ensures column defaults, hooks, and validations apply identically. Raw SQL is faster and fine for large volumes, but keep the shapes defined by your factories so the two paths agree on structure.

How much data should the baseline contain?

Only the minimum that most tests share — a couple of users, one post, enough to exercise joins. Tests that need more should add rows locally inside their transaction rather than bloating the shared baseline, because a large baseline slows every test and couples them to data most do not use. A lean baseline plus per-test additions keeps setup cheap and intent clear.

Can I reuse the production seed script for tests?

Not directly, because a production seed usually creates demo content sized for a running app, which is far more than a test needs and often not referentially minimal. Share the factories between them so the object shapes match, but keep the test seed small and deterministic. The production seed can grow; the test baseline should stay tight and stable.

Why transactions instead of truncating between tests?

Transaction rollback never writes to disk the way a truncate-and-reseed cycle does, so it is typically an order of magnitude faster while giving the same isolation for a single-connection test. Truncation is still the right tool when a test needs to commit or spans multiple connections. The trade-off is analysed fully in resetting state between tests without slowing CI.