Isolate Test Fixtures Between Tests

Give each worker its own database from a migrated template, roll back per-test writes, and stop depending on execution order.

~35 mintestingpostgresqlisolation

Task

Make database-backed tests independent and parallel-safe without truncating shared tables between every case.

When to use this

Tests pass alone but fail together: one inserts a user, another counts users and expects zero, and the suite becomes order-dependent.

Prerequisites

PostgreSQL 13 or newer
A migration runner and permission to create databases
A test runner with per-test hooks (Vitest, Jest, node:test)

Steps

1. Build one migrated template database with schema but no data.

createdb app_template && DATABASE_URL=postgres://localhost/app_template pnpm migrate

Expected output: app_template contains every table and index, and a row count on users returns zero.

2. Create a database per worker from the template instead of sharing one.

const dbName = `app_test_${process.env.VITEST_POOL_ID ?? process.pid}`;
await admin.query(`DROP DATABASE IF EXISTS ${dbName}`);
await admin.query(`CREATE DATABASE ${dbName} TEMPLATE app_template`);

Expected output: Each worker gets an identical schema in about a second, with no tables shared between workers.

3. Open a transaction before each test and roll it back afterwards.

beforeEach(async () => {
  client = await pool.connect();
  await client.query('BEGIN');
});

afterEach(async () => {
  await client.query('ROLLBACK');
  client.release();
});

Expected output: Rows inserted by a test disappear at rollback, and no TRUNCATE is needed between cases.

4. Pass the transaction-bound client into repositories so the code under test shares the same transaction.

const repo = new OrderRepository(client);
await repo.create({ totalCents: 4200 });
// the rollback in afterEach removes this row

Expected output: Writes made through the repository are visible inside the test and gone after the hook runs.

5. Run the suite with parallelism enabled and confirm there is no cross-talk.

vitest run --pool=forks --poolOptions.forks.singleFork=false

Expected output: All tests pass in parallel with multiple workers, and repeating the run produces the same result.

Troubleshooting

Problem

Rollback does not undo the test's writes

Solution

The code under test acquired its own connection from the pool. Inject the transaction-bound client, or use per-test truncation for handlers you cannot adapt.

Problem

Primary key values keep increasing between tests

Solution

Sequences are not transactional. Never assert exact ids; assert that the returned id exists and use it in follow-up queries.

Problem

CREATE DATABASE fails because the template is in use

Solution

The template must have no active connections. Disconnect pools before creating workers, or mark the template with ALLOW_CONNECTIONS false.

Problem

Advisory locks leak into the next test

Solution

Session-level advisory locks survive rollback. Use pg_advisory_xact_lock, which is released when the transaction ends.

Limitations

Transaction rollback cannot isolate writes made on other connections, sequences, or session-level state.
Per-worker databases consume disk and connection slots; drop them in a global teardown.
Creating databases from a template requires no active sessions and adds a few seconds per worker.

Order dependence is a design smell

A suite that fails when tests run in a different order is telling you that tests share state. Usually the shared state is a database: one test leaves rows behind, another assumes a clean table, and the result depends on scheduling. Truncating every table before every test works but is slow and still fragile with parallel workers. The goal is a known starting point per test, enforced by the harness rather than by convention.

One schema per worker, one transaction per test

Create a template database once, migrate it, and clone it per worker. Cloning is a file-level copy inside PostgreSQL, so it is fast even for a large schema. Within a worker, wrap each test in a transaction and roll it back: no cleaning step, no leftover rows, and no interference with other tests in the same worker. If a test needs nested scopes, use savepoints inside the outer transaction.

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

The connection must be shared

The rollback only hides writes made on the same connection. If the handler calls pool.query() directly, it runs in a different transaction and commits independently, which defeats the isolation. Inject a request-scoped client into repositories, or run the handler with a client passed through context. For legacy code you cannot adapt, truncate the affected tables in afterEach instead and document why.

Do not assert generated values

Sequences advance even when the transaction rolls back, so an assertion like expect(order.id).toBe(1) will fail on the second run. Assert that an id exists, that it is greater than zero, or capture it and use it. The same applies to now() defaults; inject a fixed clock if the test depends on time.

Clean up the workers

Drop the per-worker databases in a global teardown, and terminate connections first. In CI, the container disappears anyway, but locally leftover databases accumulate and eventually hit disk or connection limits. Log the database name at startup so a failed run leaves an obvious breadcrumb.