Running Schema Migrations Identically in Local and CI

A pull request passes locally and fails in CI with ERROR: column "discount_code" of relation "orders" does not exist; the developer's database had the column because they added it by hand while prototyping and later wrote a migration that assumed it. Elsewhere, CI loads a schema.sql snapshot while production applies migrations one by one, and the two have quietly diverged — an index exists in one and not the other. Schema drift comes from applying changes by different routes in different places. This page makes every environment reach its schema by the same route and proves they agree, as part of database seeding and fixture parity.

The target: laptops, CI and production all get their schema by running the same migrations in the same order, and a committed schema dump makes any difference visible in review.

Diagnostic

Compare the schema your local database has with the schema migrations produce from an empty database:

#!/usr/bin/env bash
set -euo pipefail
docker compose exec -T db pg_dump -U postgres --schema-only --no-owner shop > /tmp/local-schema.sql
docker compose exec -T db dropdb -U postgres --if-exists shop_fresh
docker compose exec -T db createdb -U postgres shop_fresh
DATABASE_URL=postgres://postgres:postgres@localhost:5432/shop_fresh npm run migrate >/dev/null
docker compose exec -T db pg_dump -U postgres --schema-only --no-owner shop_fresh > /tmp/fresh-schema.sql
diff -u /tmp/fresh-schema.sql /tmp/local-schema.sql | grep -E '^[+-][^+-]' | head -8

Expected bad output:

+    discount_code text,
+CREATE INDEX orders_customer_created_idx ON public.orders USING btree (customer_id, created_at);
-ALTER TABLE ONLY public.payments ADD CONSTRAINT payments_order_fk FOREIGN KEY (order_id) REFERENCES public.orders(id);

The local database has a column and an index that no migration creates, and lacks a foreign key a migration adds — it was built partly by hand and partly from an old migration history.

Root cause

A schema can be reached by several routes: applying migrations incrementally over months, loading a schema snapshot, running an ORM's "sync" or "auto-migrate" feature, or typing ALTER TABLE into a console. Each route can produce a different result from the same intent. Developers' local databases accumulate manual changes and edits to already-applied migrations (which the migration tool skips because they are recorded as applied). CI often shortcuts by loading a snapshot or using ORM sync for speed. Production applies migrations incrementally. When the routes differ, a migration can pass in one environment and fail in another, and nobody notices until a deploy. The fix is to make the migration files the only route, test them from zero in CI, and keep a committed snapshot purely as a reviewable output — never as an input.

Resolution

1. Migrate from zero in CI on a fresh database, using the same command developers and production use:

jobs:
  migrations:
    runs-on: ubuntu-24.04
    services:
      db:
        image: postgres:16.4
        env: { POSTGRES_PASSWORD: postgres, POSTGRES_DB: shop }
        ports: ['5432:5432']
        options: >-
          --health-cmd "pg_isready -U postgres" --health-interval 2s --health-retries 30
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with: { node-version-file: .tool-versions, cache: npm }
      - run: npm ci
      - run: npm run migrate
        env: { DATABASE_URL: postgres://postgres:postgres@localhost:5432/shop }
      - run: pg_dump --schema-only --no-owner "postgres://postgres:postgres@localhost:5432/shop" | ./scripts/normalise-schema.sh > /tmp/schema.sql
      - run: diff -u db/schema.sql /tmp/schema.sql

2. Commit a normalised schema dump as an output, regenerated whenever migrations change, so reviewers see the schema effect of each migration:

#!/usr/bin/env bash
set -euo pipefail
sed -E -e '/^--/d' -e '/^SET /d' -e '/^SELECT pg_catalog/d' -e '/^\s*$/d' | sort -u

Save as scripts/normalise-schema.sh. It strips comments, session settings and ordering noise so the diff shows only structural changes. The CI step fails if a pull request changes migrations without updating db/schema.sql, or if the two disagree.

3. Test the down path or the forward-only rule. If the team supports rollbacks, run migrate down then migrate up in CI; if migrations are forward-only, add a CI check that already-merged migration files are never modified:

#!/usr/bin/env bash
set -euo pipefail
changed=$(git diff --name-only --diff-filter=M origin/main...HEAD -- db/migrations/)
[ -z "$changed" ] && echo "no applied migrations modified" || { echo "modified existing migrations:"; echo "$changed"; exit 1; }

4. Reset local databases from migrations, not by hand, and make it one command so developers stop patching their schema manually:

#!/usr/bin/env bash
set -euo pipefail
docker compose exec -T db dropdb -U postgres --if-exists shop
docker compose exec -T db createdb -U postgres shop
npm run migrate
npm run db:seed

Expected output

$ npm run migrate && pg_dump --schema-only --no-owner "$DATABASE_URL" | ./scripts/normalise-schema.sh > /tmp/schema.sql
$ diff -u db/schema.sql /tmp/schema.sql && echo "schema matches committed dump"
schema matches committed dump
$ git diff --name-only --diff-filter=M origin/main...HEAD -- db/migrations/
$

A database built from migrations matches the committed schema, no existing migration was edited, and the pull request's schema changes are visible as a diff to db/schema.sql.

Reviewers benefit most. A migration file shows how the schema changes; the db/schema.sql diff shows what the resulting schema is, including effects that are easy to miss in migration code — a default value that changed, an index dropped by a column rename, a constraint that a data migration silently removed. Both are now in the same pull request.

Prevention

1. Make the migrations job required on every pull request that touches db/ or ORM model files.

2. Disable ORM auto-sync in every environment, including tests, so the migration files are the only way the schema changes.

3. Warn locally on drift by adding the diagnostic comparison to make doctor; a local database that differs from a fresh migration gets a clear "reset your database" message.

Platform caveats

Postgres version: pg_dump output varies between major versions. Use the same server image locally and in CI, and run pg_dump from the server container rather than a host client of a different version.

Apple Silicon (ARM64): schema dumps are architecture-independent; the same db/schema.sql works on every platform.

Other databases: MySQL (mysqldump --no-data) and SQLite (.schema) follow the same pattern; normalise away auto-increment counters and table options before diffing.

Rollback

Remove the CI job and committed dump; migrations keep working as before:

#!/usr/bin/env bash
set -euo pipefail
git rm -q db/schema.sql scripts/normalise-schema.sh
git checkout HEAD~1 -- .github/workflows/migrations.yml

Frequently Asked Questions

Related

• Match local seed data to CI fixtures
• Reset local database state between test runs
• Run one consolidated parity check