Database Seeding and Fixture Parity Across Environments

A test that passes on your laptop and fails on the runner is almost never a code bug — it is a data bug. The seed script that populated your local database ran at a different time, in a different order, or with a different random seed than the fixtures the environment sync and CI parity baseline loads on the build runner, so two rows that should be identical are not. This guide makes local seed data deterministic, binds it to the exact same fixtures CI consumes, and resets database state cleanly between runs so a suite behaves the same way on the first invocation and the thousandth.

Seeding drift is insidious because it hides behind green checkmarks. A query that returns rows in insertion order works locally where you seeded users alphabetically, then returns a different order on CI where the fixture inserted them by signup date; a test that asserts results[0].name == "Ada" passes in one place and fails in the other. The cost is a wasted feedback cycle and, worse, an erosion of trust in the suite — engineers start re-running failed jobs instead of reading them. The remedy is to treat fixtures as versioned artifacts with a single source of truth, to strip every source of nondeterminism out of the seed path, and to prove parity with a checksum rather than a hope.

Prerequisites

This guide assumes PostgreSQL 16 as the primary example, though the determinism principles apply unchanged to MySQL 8, MariaDB, and SQLite. You need Docker Engine 26+ with the Compose v2 plugin (docker compose version should report v2.x), a POSIX shell, and whichever migration tool your project already uses — the examples use raw psql and plain SQL so they are portable, but the same fixtures feed Rails, Django, Prisma, Flyway, or Alembic without change.

Fixtures live in the repository under db/fixtures/ as ordered SQL files, and the schema is owned by your migration tool. Keep the two strictly separate: migrations define structure, fixtures define data. Mixing seed INSERTs into migration files couples reference data to schema history and makes a clean reset impossible. Before you start, confirm your local database and your CI database run the same major and minor version — a fixture that relies on a collation or a gen_random_uuid() behavior present in one version but not another will drift no matter how deterministic your script is. Pin the image digest in Compose and in the CI service definition so both sides pull byte-for-byte the same server, exactly as described for CI/CD pipeline parity checks.

Section 1 - Establish one canonical fixture source

The root cause of most seeding drift is that two environments seed from two different scripts. Local developers run an ergonomic rake db:seed or a dev-seed.sh that inserts "enough to click around," while CI loads a terse fixture file tuned for assertions. They diverge the moment either is edited. The fix is a single canonical fixture directory that both environments load, in the same order, with no environment-specific branches.

Structure the fixtures as numbered, ordered SQL files so load order is lexicographic and explicit. Foreign-key dependencies dictate the numbering: reference tables first, then entities that depend on them.

-- db/fixtures/010_tenants.sql
INSERT INTO tenants (id, slug, name, created_at) VALUES
  ('00000000-0000-0000-0000-000000000001', 'acme',  'Acme Corp',   '2024-01-01T00:00:00Z'),
  ('00000000-0000-0000-0000-000000000002', 'globex','Globex Inc',  '2024-01-01T00:00:00Z');

-- db/fixtures/020_users.sql
INSERT INTO users (id, tenant_id, email, display_name, created_at) VALUES
  ('00000000-0000-0000-0000-0000000000a1', '00000000-0000-0000-0000-000000000001', '[email protected]',   'Ada Lovelace',  '2024-01-02T09:00:00Z'),
  ('00000000-0000-0000-0000-0000000000a2', '00000000-0000-0000-0000-000000000001', '[email protected]', 'Grace Hopper',  '2024-01-02T09:05:00Z'),
  ('00000000-0000-0000-0000-0000000000b1', '00000000-0000-0000-0000-000000000002', '[email protected]','Alan Turing',   '2024-01-02T09:10:00Z');

A single loader script applies every file in order. Both the local make seed target and the CI job call this exact script — never a divergent copy.

#!/usr/bin/env bash
# db/load-fixtures.sh — the ONLY seed entry point for local and CI
set -euo pipefail

: "${DATABASE_URL:?DATABASE_URL must be set}"
FIXTURE_DIR="${FIXTURE_DIR:-db/fixtures}"

echo "Loading fixtures from ${FIXTURE_DIR} into ${DATABASE_URL%%\?*}"

# Wrap the whole load in one transaction: all fixtures apply or none do.
{
  echo "BEGIN;"
  echo "SET session_replication_role = 'replica';"  # defer FK checks within the txn
  for f in "${FIXTURE_DIR}"/[0-9]*.sql; do
    echo "\\echo Loading $(basename "$f")"
    cat "$f"
  done
  echo "SET session_replication_role = 'origin';"
  echo "COMMIT;"
} | psql "${DATABASE_URL}" --single-transaction --set ON_ERROR_STOP=1

The two guarantees here matter. ON_ERROR_STOP=1 turns any failed statement into a non-zero exit so a broken fixture fails the job loudly instead of leaving a half-seeded database. --single-transaction plus the explicit BEGIN/COMMIT means a mid-load error rolls back to an empty state rather than a partial one, which is the difference between a clean retry and a poisoned database that every subsequent test inherits. When both environments run this identical script against an identically versioned server, the loaded rows are byte-for-byte the same.

A word on the numbering scheme, because it is doing real work. Gaps of ten between file prefixes (010, 020, 090) leave room to insert a new dependency later without renumbering everything downstream, and the lexicographic sort in the loader guarantees that a table is populated only after everything it references. This is the same discipline migration tools apply to schema changes, applied to data: order is explicit, encoded in the filename, and identical on every machine. Resist the temptation to let the loader discover files in directory-iteration order — filesystem enumeration order is not guaranteed to be lexicographic on every platform, which is exactly the kind of hidden nondeterminism this guide exists to remove. The explicit glob [0-9]*.sql piped through sort makes the order a property of the data, not of the filesystem.

Keeping fixtures data-only also means a single fixture file can be reviewed like code. A pull request that changes seed data shows up as a readable diff of INSERT rows, so a reviewer can see that a new tenant was added or an email changed without running anything. When seed data hides inside an imperative script that loops and calls a generator, the diff is a code change and the actual data effect is invisible until someone runs it — which is precisely when it diverges between a reviewer's machine and the author's. Plain declarative SQL keeps the data itself under review.

To detect drift, compare the canonical source against whatever is actually loaded. This command dumps the data-only contents of the seeded tables and hashes them — run it locally and on CI, and the hashes must match:

#!/usr/bin/env bash
set -euo pipefail
pg_dump "${DATABASE_URL}" --data-only --no-owner --no-privileges \
  --table='tenants' --table='users' \
  | grep -v '^--' | sort | sha256sum

Section 2 - Strip nondeterminism out of the seed path

Even with one source, a fixture can produce different rows on each run if it lets the database or the clock decide values. Three sources of nondeterminism cause almost every seeding drift, and each has a deterministic replacement.

1. Auto-generated primary keys. A SERIAL or IDENTITY column assigns IDs by insertion, so any change in order or any concurrent test shifts every downstream foreign key. Assign explicit, stable IDs in the fixture instead — literal UUIDs (as in Section 1) or fixed integers. Then reset the sequence past your highest fixed value so application-created rows do not collide:

-- db/fixtures/090_reset_sequences.sql
SELECT setval(pg_get_serial_sequence('users', 'id'),
              (SELECT COALESCE(MAX(id), 0) FROM users) + 1, false);

2. Wall-clock timestamps. A fixture that inserts created_at = now() bakes the seed time into the data, so a test asserting "created within the last hour" passes when you seed and fails on a runner that seeded four minutes earlier under a slow build. Freeze every timestamp to a literal in the fixture, and if application code reads "now," inject a fixed clock through configuration rather than calling the system clock. The fixtures in Section 1 use 2024-01-02T09:00:00Z literals precisely for this reason.

The clock deserves a second look because it hides in more places than created_at. Default values defined in the schema (DEFAULT now()), triggers that stamp an updated_at, and application-level callbacks that set a timestamp on insert all reintroduce the live clock even when your fixture supplies a literal. The reliable pattern is to supply every timestamp column explicitly in the fixture so no default fires, and to route the application's notion of "now" through an injectable clock — a single function or configuration value the test harness can freeze — rather than scattering direct calls to the system clock through the codebase. Once "now" is a value you control, a test that reasons about elapsed time becomes deterministic instead of racing the build.

3. Unordered generation and random data. Faker-style generators and ORDER BY random() are the loudest offenders. If you must generate volume data, seed the generator with a constant so the sequence is reproducible.

#!/usr/bin/env python3
"""Generate a deterministic fixture: same output on every machine."""
import hashlib

SEED = "fixture-v3"  # bump to intentionally change the dataset

def stable_uuid(namespace: str, n: int) -> str:
    digest = hashlib.sha256(f"{SEED}:{namespace}:{n}".encode()).hexdigest()
    return f"{digest[0:8]}-{digest[8:12]}-{digest[12:16]}-{digest[16:20]}-{digest[20:32]}"

rows = []
for i in range(1, 51):
    uid = stable_uuid("orders", i)
    tenant = "00000000-0000-0000-0000-000000000001"
    total = (i * 137) % 900 + 100          # deterministic, not random
    rows.append(
        f"('{uid}', '{tenant}', {total}, '2024-02-{(i % 28) + 1:02d}T12:00:00Z')"
    )

print("INSERT INTO orders (id, tenant_id, total_cents, created_at) VALUES")
print(",\n".join(rows) + ";")

Because the IDs derive from a hash of a fixed seed and the values from arithmetic on the index, this generator emits the identical 50 rows on your laptop, on a colleague's ARM64 Mac, and on the CI runner. There is no clock, no PRNG state, and no locale in the path. Commit the generated .sql output alongside the generator so reviewers diff the data, and regenerate only when you deliberately bump SEED.

Run this diagnostic to catch a timestamp that leaked the current clock into your seed — it flags any seeded row whose created_at is suspiciously close to the load time:

#!/usr/bin/env bash
set -euo pipefail
psql "${DATABASE_URL}" -tAc "
  SELECT count(*) FROM users
  WHERE created_at > now() - interval '10 minutes';" \
| { read -r n; [ "$n" -eq 0 ] || { echo "FAIL: $n rows carry a live clock"; exit 1; }; }
echo "OK: all seed timestamps are frozen literals"

Section 3 - Reset database state reliably between runs

Deterministic fixtures are worthless if run N leaves residue that run N+1 inherits. A suite must start from a known-empty schema every time, and the reset must be fast enough to run between test files without dominating wall-clock time. There are three viable strategies, each with a different cost and blast radius.

The cheapest per-test reset is a transaction rollback: begin a transaction before each test and roll it back after, so nothing ever commits. It is nearly free but breaks the moment code under test issues its own COMMIT or the suite spans multiple connections. The most thorough is drop and recreate the database, which guarantees a pristine schema but costs hundreds of milliseconds per cycle. The pragmatic middle ground for a shared, already-migrated database is truncate every table and reload fixtures — fast, connection-agnostic, and correct as long as you restart identity sequences.

#!/usr/bin/env bash
# db/reset.sh — truncate all data tables, then reload fixtures deterministically
set -euo pipefail
: "${DATABASE_URL:?DATABASE_URL must be set}"

# Truncate every base table except schema_migrations, cascading FKs and
# restarting identity sequences so IDs are stable on the next load.
psql "${DATABASE_URL}" --set ON_ERROR_STOP=1 <<'SQL'
DO $$
DECLARE
  stmt text;
BEGIN
  SELECT 'TRUNCATE TABLE '
       || string_agg(format('%I.%I', schemaname, tablename), ', ')
       || ' RESTART IDENTITY CASCADE'
    INTO stmt
    FROM pg_tables
   WHERE schemaname = 'public'
     AND tablename <> 'schema_migrations';
  IF stmt IS NOT NULL THEN
    EXECUTE stmt;
  END IF;
END $$;
SQL

FIXTURE_DIR="${FIXTURE_DIR:-db/fixtures}" ./db/load-fixtures.sh
echo "Database reset to canonical fixture state."

RESTART IDENTITY is the load-bearing clause: without it, truncation clears rows but leaves sequence counters advanced, so the next auto-generated key differs from run to run and reintroduces exactly the drift Section 2 eliminated. CASCADE lets you truncate parent tables without ordering the list by foreign key.

The transaction-rollback strategy is worth understanding even if you settle on truncation, because many test frameworks default to it. The harness opens a transaction in a setup hook, the test runs its inserts and queries inside that transaction, and a teardown hook issues ROLLBACK so nothing is ever committed. It is the fastest possible reset — no rows are actually written to disk — but it carries two sharp constraints. First, the code under test must not issue its own COMMIT or BEGIN, because a nested commit inside the outer transaction either errors or, worse on some drivers, silently persists. Second, every database connection the test touches must be the same connection that holds the open transaction; a test that spawns a background worker on a second pooled connection will not see the uncommitted fixture rows and will behave as though the database is empty. For suites that exercise connection pools, async jobs, or multiple services against one database, truncation is the safer default precisely because it commits a known state that every connection can see.

Whichever strategy you pick, keep the schema-migrations table out of the truncation set. That table records which migrations have run; wiping it convinces your migration tool the database is unmigrated and triggers a full re-migration on the next boot, which is both slow and a source of subtle version skew if the runner and the laptop replay migrations at different points. The WHERE tablename <> 'schema_migrations' filter in the reset script preserves schema state while clearing only data.

For a suite that resets between hundreds of tests, drop-and-recreate is too slow but truncate can still add up. PostgreSQL offers a faster path: seed a template database once, then create each test database from it with a metadata-only copy. The first seed pays the full fixture cost; every subsequent reset is a cheap CREATE DATABASE ... TEMPLATE.

#!/usr/bin/env bash
set -euo pipefail
: "${PGHOST:?}" "${PGUSER:?}"

# One-time: build a fully seeded template.
createdb app_template
DATABASE_URL="postgres://${PGUSER}@${PGHOST}/app_template" ./db/reset.sh
psql -c "UPDATE pg_database SET datistemplate = true WHERE datname = 'app_template';"

# Per suite: a fresh copy in tens of milliseconds, no re-seeding.
dropdb --if-exists app_test
createdb app_test --template app_template

The three strategies differ sharply in per-reset cost. The chart below shows representative timings for a 40-table schema with roughly 2,000 fixture rows on a local SSD; measure your own, but the ranking is stable across machines.

Section 4 - Bind local seeds to CI fixtures with a checksum

The point of a canonical source is provable parity, and the proof is a checksum. If the fixture content that loads locally hashes to the same value as the content CI loads, drift is impossible; if the hashes differ, the job fails before a single test runs and the diff tells you exactly which file changed. Compute the checksum over the fixture files themselves, not the loaded rows, so the check is fast and needs no database.

#!/usr/bin/env bash
# db/fixture-checksum.sh — deterministic hash of the canonical fixture set
set -euo pipefail
FIXTURE_DIR="${FIXTURE_DIR:-db/fixtures}"

# Sort by name so ordering is stable; hash content, not filesystem metadata.
find "${FIXTURE_DIR}" -name '[0-9]*.sql' -print0 \
  | sort -z \
  | xargs -0 sha256sum \
  | sha256sum \
  | cut -d' ' -f1

Commit the expected checksum to the repository and gate CI on it. Because the same script runs in both places, a developer who edits a fixture without regenerating the committed hash gets a red build with a one-line explanation, which routes the fix to the author instead of surfacing as a mysterious assertion failure three jobs downstream. This is the data-layer twin of the env-var contract enforced in environment variable validation with schema contracts.

#!/usr/bin/env bash
# Run identically in the local pre-push hook and in the CI parity stage.
set -euo pipefail
EXPECTED="$(cat db/fixtures.sha256)"
ACTUAL="$(./db/fixture-checksum.sh)"
if [ "${EXPECTED}" != "${ACTUAL}" ]; then
  echo "Fixture drift detected."
  echo "  expected: ${EXPECTED}"
  echo "  actual:   ${ACTUAL}"
  echo "Regenerate with: ./db/fixture-checksum.sh > db/fixtures.sha256"
  exit 1
fi
echo "Fixtures match the committed checksum."

Hashing the files rather than the loaded rows is a deliberate choice. A file checksum is instantaneous, needs no running database, and pins the exact input; a checksum of loaded rows would additionally catch server-version or collation drift but costs a full seed and dump on every check. Use the fast file checksum as the pre-push and CI gate, and reserve the slower row-level pg_dump hash from Section 1 for a nightly job or a one-off investigation when a test fails despite matching file hashes — that is your signal the divergence is in the server, not the fixtures. The two checks together localize any drift to either the data you wrote or the environment that loaded it.

Wire this into the broader drift suite so seed parity is checked alongside container and environment parity in one command, as catalogued in the CI parity validation reference. A parity gate that covers images and env vars but ignores seed data leaves the most common "works on my machine" failure unguarded.

Section 5 - Wire seeding into Compose and CI

The final concern is that local and CI database services are configured identically, so the deterministic fixtures land in an identical server. Define the database once in Compose with a pinned digest, an init mount, and a healthcheck so nothing seeds before the server accepts connections. This dovetails with the durable-storage patterns in volume mounting and hot-reload optimization.

# docker-compose.yml — pinned, health-gated database for local and CI
services:
  db:
    image: postgres:16.4@sha256:0000000000000000000000000000000000000000000000000000000000000000
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: local-only-not-a-secret
      POSTGRES_DB: app_test
      # Deterministic collation: same sort order on every host.
      LANG: C.UTF-8
      LC_ALL: C.UTF-8
    command: ["postgres", "-c", "fsync=off", "-c", "synchronous_commit=off"]
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d app_test"]
      interval: 2s
      timeout: 3s
      retries: 30

volumes:
  db-data:

The LANG/LC_ALL pin to C.UTF-8 is easy to miss and expensive to debug: string sort order is locale-dependent, so a query with ORDER BY name returns one order under en_US.UTF-8 and another under C, and if your laptop and the runner disagree on locale your ordered assertions drift even with identical rows. Pinning the collation removes the last hidden nondeterminism. Turning off fsync and synchronous_commit is safe for disposable test data and roughly halves reset time.

Seed after the healthcheck passes, never during it. In CI, wait for health, run migrations, then load fixtures through the same shared script — the same three commands a developer runs locally.

#!/usr/bin/env bash
# ci/db-up.sh — bring up, migrate, and seed exactly as a developer does locally
set -euo pipefail
export DATABASE_URL="postgres://app:local-only-not-a-secret@localhost:5432/app_test"

docker compose up -d db

# Block until the server is actually accepting connections.
until docker compose exec -T db pg_isready -U app -d app_test >/dev/null 2>&1; do
  sleep 1
done

./bin/migrate up            # your migration tool owns the schema
./db/fixture-checksum.sh > /tmp/actual.sha256
diff -q db/fixtures.sha256 /tmp/actual.sha256   # fail fast on fixture drift
./db/reset.sh               # truncate + load canonical fixtures
echo "Database is migrated, seeded, and verified."

Because the local make seed target and ci/db-up.sh call the identical migration, checksum, and reset scripts against an identically pinned and identically localized server, the database a test sees on the runner is indistinguishable from the one it sees on your machine. That is fixture parity: not "similar enough," but provably the same bytes, in the same order, reset to the same known state before every run. The same discipline that keeps dotenv configuration in parity across environments applies to the data layer — declare it once, load it identically, and verify it with a checksum rather than trust.

Platform caveats

macOS (Docker Desktop): The database volume lives inside the Docker Desktop VM, so fsync=off speeds resets even more than on native Linux because it avoids the VirtioFS round trip. Bind-mounting fixture files into the container is fine, but never bind-mount /var/lib/postgresql/data to a macOS host path — the filesystem semantics differ and corrupt the cluster. Keep it a named volume as shown above.

WSL2: Run the project from the Linux filesystem (~/project), not /mnt/c. A fixtures directory on the Windows drive is read through the 9p bridge, which is slow enough that the fixture checksum and reload noticeably lag; worse, line-ending translation can alter .sql bytes and change the checksum, producing phantom drift between a WSL2 developer and a Linux runner. Set git config core.autocrlf false for the repo.

Apple Silicon (ARM64): Pin a multi-arch or ARM64 Postgres digest so Rosetta emulation is never invoked — an emulated x86 database server is several times slower to seed and can differ subtly in floating-point and collation behavior. Verify with docker compose exec db uname -m and confirm it reports aarch64, matching an ARM64 runner or an explicitly linux/amd64-pinned one on both sides.

Rollback and recovery

If a fixture change breaks the suite and you need the previous known-good state immediately, the recovery is a single reset against the prior fixtures. Because fixtures are versioned in Git, checking out the last green commit and reloading restores the exact data every test expected.

#!/usr/bin/env bash
set -euo pipefail
git checkout HEAD~1 -- db/fixtures db/fixtures.sha256
./db/reset.sh
./db/fixture-checksum.sh   # confirm it matches the restored db/fixtures.sha256

If a bad seed committed real damage to a shared local volume, drop the volume and rebuild from scratch — the fixtures are the source of truth, so nothing of value is lost: docker compose down -v && docker compose up -d db && ./ci/db-up.sh.

Frequently Asked Questions

Related

• Sync seed data across CI/CD pipeline parity checks
• Run a consolidated parity validation suite
• Enforce environment variable contracts with schema validation
• Persist database volumes for hot-reload development