Database Migrations in CI: Testing Schema Changes Early

Application code gets tested before it deploys; database migrations too often do not. A migration that runs fine in a developer's local staging but breaks against production data is a classic deploy-time surprise, and it is entirely avoidable with the right CI discipline. The goal is that migrations are exercised — against a real database, in order, with a rollback path — every time they change, not just on release night.

Migrations are code and deserve a test ring

Treat each migration as a unit with a contract: it must apply cleanly from the current schema, and it must be reversible where required. CI should apply the entire migration history from an empty database, then apply it again against a snapshot that mirrors production, so deviations from how the team has been developing are caught early.

The lowest-effort gate is a job that spins up a fresh database and applies all pending migrations in order, failing on any conflict:

name: migrate-check
on:
  pull_request:
jobs:
  migrate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: test
        ports:
          - 5432:5432
    steps:
      - uses: actions/checkout@v4
      - run: psql "$TEST_DATABASE_URL" -f db/bootstrap.sql
      - run: npx prisma migrate deploy
      - run: npx prisma migrate status

By running the real migration tool against a real temporary database, the job exercises the same SQL that will run in production rather than a slightly different local approximation.

Test migrations against a production-shaped dataset

An empty schema validates syntax but not behavior. The migration that is slow or fails against millions of rows behaves fine on an empty table. The higher-value test runs the migration against a sanitized copy or a generated dataset that approximates production volume and distribution. This surfaces lock-timeouts, missing indexes, and long-running ALTERs before they hit real traffic.

Run the migration twice, up and down, where the tool supports reversions, and assert the down migration restores compatibility:

-- a migration pair that CI runs forward, then back
BEGIN;
ALTER TABLE users ADD COLUMN timezone VARCHAR(64) DEFAULT 'Asia/Kolkata';
-- fast on empty table, potentially slow at prod volume
COMMIT;

-- down: drop the column again
BEGIN;
ALTER TABLE users DROP COLUMN timezone;
COMMIT;

The inspect step checks column lists before and after, so a broken down-migration is found in CI, not at rollback time.

Deploy order: code versus schema

The painful classic is the migration that renames a column while the running old application still reads the old name. The safe pattern is to sequence changes so every step is compatible with both the previous and next app version — add a column first, backfill, deploy code that uses it, then drop the old column in a later migration. CI should enforce the ordering rule by rejecting migrations that drop columns or rename tables in the same release as code that could still depend on the old shape. Tooling like expanding-contract checkers helps automate this guard.

Evidence over confidence

A migration is only done when the database is verified, not when the tool reports success. Add a verification step that inspects the resulting schema — indexes present, columns typed correctly, expected row counts — and fail if reality differs from intent. That inspection is the difference between "the tool ran" and "the schema is right," and production deserves the latter.

Takeaway: test migrations in CI on real databases, against production-shaped data, with up-and-down verification, and sequence schema changes carefully. Put your application and its database on a Netbay VPS from Lucknow DC01, reprovisionable in under 60 seconds at netbayhosts.in, so migration failures are cheap and repeatable to rehearse.