Database Migrations in CI/CD

The plain-language idea

Every other kind of deploy this track has covered ships one thing: application code. A database migration ships a second thing at the same time — the shape of the data itself — and that second thing has a property application code doesn't: it's usually much harder to roll back. You can redeploy an old container image in seconds. Un-dropping a column, or reversing a data backfill that already ran against production traffic, is a different order of problem.

The specific hazard shows up during a rolling deploy. A rolling deploy doesn't flip every server from old code to new code atomically — it replaces instances one at a time, so for some window, old app code and new app code are both running, both hitting the same database, at the same time. If your migration changes the schema in a way the old code doesn't understand — renames a column, drops one the old code still selects, adds a NOT NULL column the old code never populates — the old instances start failing requests the moment the migration runs, even though their own code never changed. The rollout, not a bug in either version of the code, is what breaks production.

See it in a real thing: a backward-compatible rename

Say you're renaming a column from email to email_address. The unsafe way is one migration that does it directly:

-- UNSAFE: old app code still SELECTs `email` — this breaks it immediately
ALTER TABLE users RENAME COLUMN email TO email_address;

The safe way splits it into multiple deploys, each one compatible with the version before and after it — the "expand and contract" pattern:

-- Migration 1 (deploy N): expand — add the new column, keep the old one
ALTER TABLE users ADD COLUMN email_address TEXT;
UPDATE users SET email_address = email;  -- backfill existing rows

-- App code deploy N: write to BOTH columns, read from the OLD one.
-- Old instances (pre-migration) still work — `email` still exists and is still correct.

-- Migration 2 (deploy N+1, after N is fully rolled out): app now reads/writes
-- `email_address` only. `email` is now unused by any running code.

-- Migration 3 (deploy N+2, a full deploy cycle later): contract — safe to drop
ALTER TABLE users DROP COLUMN email;

Three migrations instead of one, spread across separate deploys — but at every single point in that sequence, both the old and new app code that could plausibly be running at once can talk to the schema that exists. That's the rule: a migration should be backward-compatible with the previous app version, not just forward-compatible with the version being deployed alongside it.

Running the migration as its own pipeline step, before the app deploy step, makes the ordering explicit and reviewable instead of implicit in someone's deploy runbook:

jobs:
  migrate:
    runs-on: ubuntu-latest
    environment: production
    steps:
      - uses: actions/checkout@v4
      - run: pnpm install --frozen-lockfile
      - run: pnpm exec node-pg-migrate up
        env:
          DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}

  deploy:
    needs: migrate
    runs-on: ubuntu-latest
    steps:
      - run: ./deploy.sh production

deploy declares needs: migrate, so the schema change is guaranteed to land before any new application code that depends on it starts rolling out — and if the migration step fails, the whole pipeline stops before touching the running app at all.

What people usually miss

Watch out

• A NOT NULL column with no default breaks old code the instant it's added. Old instances that INSERT rows without knowing about the new column will fail immediately — add it nullable first, backfill, then tighten the constraint in a later migration once every instance is guaranteed to populate it.
• "It worked in staging" doesn't prove it's safe. Staging usually deploys atomically with no traffic during the switch — the mixed-version window that causes real incidents only exists under a genuine rolling deploy with live traffic, which staging often doesn't replicate.
• Migrations need to be idempotent and re-runnable, not just correct once. A pipeline can retry a failed step, or the migration job can get re-triggered — a migration that isn't safe to run twice (e.g. an INSERT instead of INSERT ... ON CONFLICT) can corrupt data on a retry that was only meant to recover from a transient failure.

The genealogy: this builds on deployment strategies

The mixed-version window that makes migrations dangerous is the exact same rolling-deploy mechanic covered in the deployment-strategies lesson — old and new instances coexisting briefly — just applied to the database instead of the app servers.