Matching Local Seed Data to CI Fixtures

A test asserts that the seeded catalog holds exactly twelve products, passes on your laptop, and fails in CI with expected 12, received 9. This guide is part of database seeding and fixture parity within the environment sync, secrets and CI parity baseline, and it removes the divergence at its root: it makes local seeds and CI fixtures load from one shared source so a row that exists on your machine exists on the runner, byte for byte.

The failure is rarely a bug in the test. It is two independent copies of "the starting data" that drifted apart — a seed.sql a developer hand-edited months ago and a fixtures.json the CI job loads that nobody kept in step. Every assertion that counts rows, checks an ID, or reads a timestamp becomes a coin flip on which copy the environment happened to load. The fix is not to loosen the assertion; it is to delete one of the copies. Below you build a single fixture source, a loader that both docker compose and the CI job invoke identically, and a drift check that fails the moment the two environments would disagree.

Diagnostic

Reproduce the split by counting rows in each environment after its own seed runs. Do it locally first, against the Compose database:

#!/usr/bin/env bash
set -euo pipefail
docker compose up -d db
until docker compose exec -T db pg_isready -U app -d appdb >/dev/null 2>&1; do sleep 1; done
docker compose exec -T db psql -U app -d appdb -c "SELECT count(*) AS products FROM products;"

Local, freshly seeded, prints the number the test expects:

 products
----------
       12
(1 row)

Now run the same count the way CI does — from whatever fixture file the pipeline loads — and watch it disagree:

#!/usr/bin/env bash
set -euo pipefail
psql "$DATABASE_URL" -f ci/fixtures.sql
psql "$DATABASE_URL" -c "SELECT count(*) AS products FROM products;"

Expected BAD output — the CI fixture is a stale copy that never received the three rows added to the local seed:

 products
----------
        9
(1 row)

Nine versus twelve is the whole story. The two seeds are different files with different contents, so any test that depends on the row set is validating the environment's luck, not the code. Confirm the files really are the source of the drift by diffing their normalized contents rather than trusting timestamps:

#!/usr/bin/env bash
set -euo pipefail
diff <(sort db/seed.sql) <(sort ci/fixtures.sql) && echo "seeds identical" || echo "seeds have drifted"

If that prints seeds have drifted, you have two sources of truth. That is the defect to eliminate — not the count in the test.

Root cause

The drift is structural, not accidental. When a project seeds the local database from one artifact and the CI database from another, nothing forces the two to stay equal. A developer adds three products to the local seed.sql to build a feature, the local tests pass, the pull request merges, and the CI fixture — a separate file in a separate directory — never learns about the change. From that commit onward the two environments start from different states, and every row-sensitive test is one edit away from disagreeing. The environments are not running the same code path to build their data, so there is no mechanism that could keep them in sync.

The reason this survives review is that both copies are individually valid SQL that loads without error. Neither file is broken; they are simply different. A reviewer reading the diff sees three rows added to one file and has no signal that a second file exists and should have changed too. The only durable fix is to collapse the two artifacts into one shared source that both environments load through the same loader, so "add a row" is a single edit that both the laptop and the runner pick up automatically.

Resolution

The strategy is one fixture source, one loader, two identical invocations. You move the data into a single directory, write a loader that reads it, and call that same loader from Compose and from CI so neither environment can construct a different starting state.

1. Put the canonical data in one version-controlled directory and delete the duplicate. Keep the fixtures in a format that is trivial to diff and order-stable — one file per table, sorted by primary key, so a review diff reads cleanly.

#!/usr/bin/env bash
set -euo pipefail
mkdir -p fixtures
git mv db/seed.sql fixtures/products.sql
git rm ci/fixtures.sql
echo "fixtures/ is now the single source of truth"

2. Write one loader that applies every fixture file in a deterministic order. Sorting the file list makes the load order reproducible regardless of the filesystem's readdir order, which differs between macOS and Linux runners.

#!/usr/bin/env bash
# scripts/load-fixtures.sh — the ONLY thing that seeds a database
set -euo pipefail

: "${DATABASE_URL:?DATABASE_URL is required}"
FIXTURE_DIR="${FIXTURE_DIR:-fixtures}"

echo "Loading fixtures from ${FIXTURE_DIR} into ${DATABASE_URL%%\?*}"
for file in $(find "${FIXTURE_DIR}" -name '*.sql' | sort); do
  echo "  applying ${file}"
  psql "${DATABASE_URL}" -v ON_ERROR_STOP=1 -f "${file}"
done
echo "fixtures loaded"

3. Have Compose seed the local database by running that exact script. A one-shot seed service depends on a healthy database and invokes the shared loader — no inline SQL, no second copy of the data.

# docker-compose.yml
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
      POSTGRES_DB: appdb
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d appdb"]
      interval: 2s
      timeout: 3s
      retries: 15
  seed:
    image: postgres:16
    depends_on:
      db:
        condition: service_healthy
    environment:
      DATABASE_URL: postgres://app:app@db:5432/appdb
    volumes:
      - ./fixtures:/fixtures:ro
      - ./scripts:/scripts:ro
    entrypoint: ["/scripts/load-fixtures.sh"]

4. Have CI seed its database by running the identical script against its own DATABASE_URL. Because both call scripts/load-fixtures.sh over the same fixtures/ directory, the runner cannot end up with a different row set than the laptop.

# .github/workflows/test.yml
jobs:
  test:
    runs-on: ubuntu-latest
    services:
      db:
        image: postgres:16
        env:
          POSTGRES_USER: app
          POSTGRES_PASSWORD: app
          POSTGRES_DB: appdb
        ports: ["5432:5432"]
        options: >-
          --health-cmd "pg_isready -U app -d appdb"
          --health-interval 2s --health-timeout 3s --health-retries 15
    env:
      DATABASE_URL: postgres://app:app@localhost:5432/appdb
    steps:
      - uses: actions/checkout@v4
      - run: ./scripts/load-fixtures.sh
      - run: ./run-tests.sh

Choosing the fixture format

The shared source can be raw SQL, a language-neutral data file, or your ORM's fixture format — but the choice has consequences for parity, and one rule dominates: whatever the format, both environments must load it through the same code. Raw .sql is the most portable because psql exists on every runner and no application code has to be booted to seed; a structured format like YAML or JSON is easier to diff and lets a factory library fill derived columns, at the cost of requiring the loader to run inside your app's runtime. The trap to avoid is a format that only one environment can read — an ORM seed task that runs locally but not in the minimal CI image, for instance — because that reintroduces two code paths and the drift comes straight back.

If you seed through an application runtime, the same principle applies to type coercion: a value that reads as a string locally but an integer in CI produces exactly the row-level mismatches described in fixing boolean and number env coercion bugs. Keep the loader and its interpreter identical across environments and the coercion stays identical too.

Expected output

After the change, seed both environments and confirm the counts match. Locally:

$ docker compose run --rm seed
  applying /fixtures/products.sql
fixtures loaded
$ docker compose exec -T db psql -U app -d appdb -c "SELECT count(*) FROM products;"
 count
-------
    12
(1 row)

And the CI log now reports the same twelve rows because it loaded the same file:

Run ./scripts/load-fixtures.sh
Loading fixtures from fixtures into postgres://app:app@localhost:5432/appdb
  applying fixtures/products.sql
fixtures loaded
Run ./run-tests.sh
  products count assertion: expected 12, received 12 — PASS

The two environments print the same number because there is no longer a second file that could hold a different number. The assertion that used to flip is now deterministic.

Prevention

1. Add a drift guard that fails CI if any duplicate seed file reappears, so nobody can reintroduce a second source of truth. The check greps for the old paths and for stray inline INSERT statements outside fixtures/.

#!/usr/bin/env bash
# scripts/check-single-source.sh
set -euo pipefail
if git ls-files 'db/seed.sql' 'ci/fixtures*.sql' | grep -q .; then
  echo "FATAL: a duplicate seed file reappeared — fixtures/ must be the only source" >&2
  exit 1
fi
echo "single fixture source confirmed"

2. Add a parity smoke test that seeds a throwaway database with the shared loader and asserts the row counts the suite depends on, so a bad fixture edit fails fast rather than deep inside an unrelated test.

#!/usr/bin/env bash
# scripts/verify-fixture-counts.sh
set -euo pipefail
: "${DATABASE_URL:?DATABASE_URL is required}"
./scripts/load-fixtures.sh
count=$(psql "${DATABASE_URL}" -tAc "SELECT count(*) FROM products;")
[ "${count}" = "12" ] || { echo "FATAL: expected 12 products, got ${count}" >&2; exit 1; }
echo "fixture counts verified"

3. Run both guards as pre-commit hooks and as the first CI step so drift is caught before push and before the expensive test job. The same loader feeds a single consolidated check across environments — see run one consolidated parity check.

The payoff is measurable in how often the row-count assertion flips between passing and failing across a sample of runs. The chart contrasts a two-copy seed with the single shared source.

Platform caveats

WSL2: keep the fixtures/ directory on the Linux filesystem (~/project), not /mnt/c. A .sql fixture edited by a Windows editor on the mounted drive can carry \r\n line endings that survive into a COPY ... FROM stdin block and corrupt the last column of each row, producing a count that matches but data that does not. macOS (Docker Desktop): the loader's find ... | sort is what guarantees order parity — macOS returns directory entries in a different order than a Linux runner, so relying on unsorted find output would apply fixtures in a different sequence locally and let a foreign-key ordering bug appear only in CI. Apple Silicon (ARM64): pin the database image by tag (postgres:16, not postgres:latest) so the ARM64 image your laptop pulls and the amd64 image the runner pulls are the same major version; a collation change between major versions can reorder SELECT results and break an assertion that reads the "first" row without an explicit ORDER BY.

Rollback

The change is additive and safe to unwind file by file. If the shared loader misbehaves, restore the previous per-environment seeds from git without losing the consolidated fixtures: git revert --no-commit HEAD && git checkout HEAD -- fixtures/. To seed a single environment the old way for one debugging session, apply a fixture directly — psql "$DATABASE_URL" -f fixtures/products.sql — which uses the same file the loader would, so you are not reintroducing a second copy. Once the loader is fixed, drop the manual step and let Compose and CI call scripts/load-fixtures.sh again so parity is restored automatically.

Frequently Asked Questions

Related

• Reset database state between test runs
• Generate deterministic fake data with a fixed seed
• Fix boolean and number coercion in seeded values
• Run one consolidated parity check