Resetting Local Database State Between Test Runs

A test that passes when you run it alone fails the moment another test inserts a row first: you hit a duplicate key value violates unique constraint error, an assertion on SELECT count(*) returns 9 instead of 3, or an auto-increment id that was 1 yesterday is 47 today. That is state leaking across runs. This guide is part of database seeding and fixture parity within the environment sync, secrets and CI parity baseline. It shows how to reset a local database to a known-good state deterministically between test runs — fast, with per-test transactional rollback, or fully, with TRUNCATE, template databases, or volume recreation — so the outcome of a run never depends on what ran before it.

Diagnostic

The signature of leaked state is order-dependence: the suite passes in one order and fails in another, or passes once and fails on a second invocation without any code change. Reproduce it by running the same suite twice against a database you never reset:

#!/usr/bin/env bash
set -euo pipefail

# Row count grows every run because writes are committed and never cleaned up.
count() { docker compose exec -T db psql -U app -d app_test -tAc "SELECT count(*) FROM orders;"; }

echo "before run 1: $(count)"
npm test --silent >/dev/null 2>&1 || true
echo "after  run 1: $(count)"
npm test --silent >/dev/null 2>&1 || true
echo "after  run 2: $(count)"

Expected BAD output — the count climbs instead of returning to its seeded baseline, and the second run fails on a uniqueness collision:

before run 1: 3
after  run 1: 7
after  run 2: 11

A second confirming signal is sequence drift. Even after you delete rows by hand, the identity counter keeps advancing, so a test that asserts id = 1 breaks:

#!/usr/bin/env bash
set -euo pipefail
docker compose exec -T db psql -U app -d app_test -tAc \
  "SELECT last_value FROM orders_id_seq;"

47

The rows may be gone, but last_value is 47 — the counter is committed state too. Any test that pins a primary key, a foreign key, or an ordering by id is now non-deterministic.

One more diagnostic tells you whether the leak is per-connection or persisted to disk. Open a fresh psql session and query the tables directly; if a row your test inserted is visible from an unrelated connection, it was committed and lives in the volume, not merely cached inside your ORM's pooled session. That distinction decides your fix: cached-session leakage is solved by rolling back the transaction, while persisted rows require an active truncate or recreate. Run the check twice — once mid-suite and once after the process exits — and note that a committed row survives the process, which is the definition of leaked state.

Root cause

Committed writes are durable by design — that is the property a database exists to provide, and it does not stop being true because the caller happens to be a test. When a test issues INSERT ... COMMIT, the database persists the row and its sequence advance to the data files inside the container's volume, and nothing removes them at the end of the run. The next run starts from wherever the last one stopped. There is no isolation boundary around a run unless you create one. Three facts compound the problem: writes survive process exit because they live in the volume, not the test process; identity sequences advance monotonically and are never rolled back by a DELETE; and an ORM's connection pool may hold a transaction open across tests, so what one test wrote is visible to the next through the same session. Determinism requires an explicit reset boundary — either you never commit (roll every test back), or you actively return the schema to a known baseline (truncate, re-clone, or recreate the volume) before the next run reads it. The right boundary depends on how much you are willing to trade setup speed for isolation strength.

Resolution

Pick the strongest isolation you can afford at each level. Per-test rollback is the fastest and belongs on unit-style tests; truncation and template clones reset whole runs; volume recreation is the sledgehammer you reach for when schema or extension state has drifted. The steps below layer them so a fast inner loop coexists with a guaranteed-clean outer boundary. The layering matters because the strategies are complementary, not competing: rollback keeps the millisecond-scale unit loop clean, truncation resets the handful of integration tests that must commit, and the volume recreate runs once at the start of a CI job to guarantee the schema everyone builds on is identical. Reaching for the heaviest tool everywhere wastes minutes per run; reaching for the lightest tool everywhere lets committed state slip through. Match the boundary to the test.

1. Wrap every individual test in a transaction and roll it back. Open a transaction in beforeEach, hand the same connection to the code under test, and ROLLBACK in afterEach. Nothing ever commits, so nothing leaks, and rollback is far cheaper than deleting rows.

// db-txn.test-helper.ts
import { Client } from 'pg';

export async function withRollback(fn: (c: Client) => Promise<void>): Promise<void> {
  const client = new Client({ connectionString: process.env.DATABASE_URL });
  await client.connect();
  await client.query('BEGIN');
  try {
    await fn(client);          // the test runs inside this transaction
  } finally {
    await client.query('ROLLBACK'); // discard every write, including sequence use
    await client.end();
  }
}

2. For tests that must commit — anything exercising its own transaction boundaries, COMMIT, or LISTEN/NOTIFY — reset the whole run with TRUNCATE ... RESTART IDENTITY CASCADE. It empties the tables and rewinds every identity sequence in one statement, and it is dramatically faster than DELETE because it does not scan rows.

#!/usr/bin/env bash
set -euo pipefail
# reset-tables.sh — run before each suite that commits
docker compose exec -T db psql -U app -d app_test -v ON_ERROR_STOP=1 <<'SQL'
TRUNCATE TABLE orders, line_items, customers RESTART IDENTITY CASCADE;
SQL
echo "tables truncated, identities restarted"

3. When per-run setup dominates the clock, clone from a template database instead of re-seeding. Seed a pristine app_template once, then create a throwaway copy for each run — Postgres copies the template's files directly, which is much faster than replaying inserts.

#!/usr/bin/env bash
set -euo pipefail
# clone-from-template.sh — one fresh database per run, seeded instantly
DB="app_test_$$"                       # unique per process
docker compose exec -T db psql -U app -d postgres -v ON_ERROR_STOP=1 <<SQL
CREATE DATABASE ${DB} TEMPLATE app_template;
SQL
export DATABASE_URL="postgres://app@localhost:5432/${DB}"
npm test
docker compose exec -T db psql -U app -d postgres -c "DROP DATABASE ${DB};"

4. When the schema itself has drifted — a failed migration, a stray extension, a corrupt index — recreate the volume so the container reinitializes from scratch. Back the test database with a named volume you can destroy, and let the entrypoint replay your seed scripts on first boot.

# docker-compose.yml
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_USER: app
      POSTGRES_DB: app_test
      POSTGRES_HOST_AUTH_METHOD: trust
    volumes:
      - db_test_data:/var/lib/postgresql/data
      - ./seed:/docker-entrypoint-initdb.d:ro
    ports:
      - "5432:5432"
    tmpfs:
      - /tmp
volumes:
  db_test_data:

#!/usr/bin/env bash
set -euo pipefail
# recreate-volume.sh — full deterministic reset from an empty data directory
docker compose down -v          # -v removes the named volume
docker compose up -d db
# wait until Postgres accepts connections before seeding/testing
until docker compose exec -T db pg_isready -U app -d app_test >/dev/null 2>&1; do
  sleep 0.5
done
echo "volume recreated, seed scripts replayed"

Expected output

With per-test rollback on unit tests and a TRUNCATE ... RESTART IDENTITY before any committing suite, the row count returns to its seeded baseline every run and the sequence rewinds to 1:

$ ./reset-tables.sh
tables truncated, identities restarted
$ npm test --silent
  orders › creates an order          (id = 1)  ✓
  orders › lists seeded orders       (count = 3) ✓
$ docker compose exec -T db psql -U app -d app_test -tAc "SELECT count(*) FROM orders;"
3
$ docker compose exec -T db psql -U app -d app_test -tAc "SELECT last_value FROM orders_id_seq;"
1

Running the suite a second time — or in a shuffled order — produces byte-identical results because every run starts from the same three seeded rows with identity at 1. That is the property you were missing: the outcome no longer depends on history. Prove it by deliberately randomizing test order with your runner's shuffle flag and confirming the assertions still hold; an order-independent green suite is the strongest evidence that the reset boundary is doing its job.

The cost of each strategy is what decides where you apply it. The chart below shows representative per-run reset times for the same 50-table schema on one machine; rollback is effectively free, truncation is cheap, a template clone beats re-seeding, and a full volume recreate pays the entire init cost.

Prevention

1. Make the reset a first-class command, not a thing people remember to do. Add a make test target that always resets before it runs, so an unclean database can never masquerade as a passing suite.

# Makefile
.PHONY: test db-reset
db-reset:
	./reset-tables.sh
test: db-reset
	npm test

2. Enforce isolation in CI so a leak fails the pipeline rather than the next engineer's laptop. Run the suite twice in the same job; if the second pass diverges, state is leaking. This pairs naturally with reproducing CI-only test failures locally with act.

# .github/workflows/db-isolation.yml
name: db-isolation
on: [push]
jobs:
  double-run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: docker compose up -d db
      - run: make test   # run 1
      - run: make test   # run 2 — must match; a diff means state leaked

3. Keep the seed baseline versioned and deterministic so "reset" always means the same thing on every machine. Load fixtures from committed SQL or a generator with a fixed seed, and validate that required configuration is present before boot with catching missing env vars before container startup.

Platform caveats

macOS (Docker Desktop): the Postgres data directory on a bind mount goes through the virtualization layer and makes docker compose down -v && up noticeably slow. Keep db_test_data a named volume (as above) rather than a host bind mount, and prefer truncation or template clones over volume recreation in the inner loop. WSL2: run the compose project from the Linux filesystem (for example ~/code), not /mnt/c; a data directory under /mnt/c suffers cross-boundary I/O that slows every reset and can leave pg_isready flapping during startup. Apple Silicon (ARM64): pull the multi-arch postgres:16 tag so you get the native arm64 image; an emulated amd64 Postgres inflates every truncate and clone, distorting the reset-time numbers above and slowing the suite under emulation.

Rollback

If a reset script itself misbehaves — a bad TRUNCATE list or a template that drifted — fall back to the guaranteed-clean path and rebuild the database from an empty data directory and your committed seed scripts:

#!/usr/bin/env bash
set -euo pipefail
docker compose down -v
docker compose up -d db
until docker compose exec -T db pg_isready -U app -d app_test >/dev/null 2>&1; do sleep 0.5; done
echo "database rebuilt from seed scripts"

Frequently Asked Questions

Related

• Generate deterministic seed data for local development
• Keep fixtures in sync with schema migrations
• Reproduce CI-only test failures locally with act
• Block startup on missing environment variables