Running database migrations safely in CI/CD

A bad deploy rolls back in seconds: you start the previous image again. A bad migration doesn't. Once a column is dropped or a table is locked behind a long query, there's no previous image to go back to. That's why the migration step deserves more care than anything else in a pipeline.

These are the rules I follow for running migrations from CI/CD, with PostgreSQL examples. The ideas apply to Prisma, TypeORM, Knex or plain SQL files alike.

Part of my guide to deploying Node.js to production.

TL;DR#

• Run migrations once, as their own step, before the new code goes live. Not on app startup.
• Make every migration work with the code that's already running: expand first, contract in a later release.
• Know which changes lock tables, and use the safe versions: CREATE INDEX CONCURRENTLY, constraints added as NOT VALID.
• Set a lock_timeout, so a blocked migration fails fast instead of freezing the app.
• Test migrations in CI against a real database, from an empty schema.
• Run them from inside your network with the new image, and keep backups you've actually restored.

Rule 1: run migrations once, before the new code#

It's tempting to run migrations when the app starts. It causes three problems:

• Every replica runs them. Two instances starting together race to apply the same migration.
• A slow migration delays startup, and health checks fail while it runs.
• A failed migration crash-loops the app, taking the old version's capacity down with it during a rolling deploy.

Make the migration a separate step in the release instead, after the image is built and before traffic moves to it:

build image → run migrations (once) → start new version → health check → switch traffic

If the migration fails, the release stops there, and the version already running is untouched.

Rule 2: every migration must work with the old code#

During a release, the old version keeps serving traffic after the migration has run, for a few seconds in a blue-green deploy and longer in a rolling one. So each migration has to work with both versions of the code.

The pattern is expand, then contract: add the new structure first, release code that uses it, and only remove the old structure in a later release, once nothing running depends on it. Renaming a column, for example, takes three releases:

1. Expand: add full_name, write to both name and full_name, and backfill the existing rows.
2. Switch: read from full_name only.
3. Contract: drop name.

Which changes are safe to run while the old code serves traffic, in PostgreSQL:

Rule 3: avoid long locks#

Most schema changes need a brief exclusive lock on the table. The danger isn't the change itself; it's the wait. If a slow query holds the table, your ALTER TABLE waits for it, and every query that arrives after the ALTER TABLE queues behind it. A change that takes milliseconds can stall the whole app.

Set a lock timeout at the start of each migration, so it gives up instead of building a queue:

SET lock_timeout = '5s';

ALTER TABLE orders ADD COLUMN shipped_at timestamptz;

If it times out, nothing has changed. Run the release again when the database is quieter.

Two changes need their safe versions every time.

Indexes. A plain CREATE INDEX blocks writes to the table until it finishes. Build it concurrently:

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

CONCURRENTLY can't run inside a transaction, and most migration tools wrap each migration in one, so check how yours lets a single migration opt out.

NOT NULL on an existing column. SET NOT NULL normally scans the whole table while holding its lock. Add the rule as an unvalidated CHECK constraint first, validate it without blocking writes, and PostgreSQL skips the scan when you set NOT NULL:

ALTER TABLE users ADD CONSTRAINT users_email_not_null CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

Big backfills don't belong in one UPDATE either. Update in batches, so no single transaction holds locks for long:

-- Run repeatedly until it updates 0 rows. (Skipping rows where name is NULL too,
-- or they'd match forever and the loop would never finish.)
UPDATE users SET full_name = name
WHERE id IN (
  SELECT id FROM users WHERE full_name IS NULL AND name IS NOT NULL LIMIT 1000
);

Rule 4: test migrations in CI#

A migration that only ever ran on your laptop's database will meet production's for the first time during a release. Run the whole migration history against a real PostgreSQL in CI, then the tests on top of it. GitHub Actions starts one as a service container:

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 5s
          --health-timeout 5s
          --health-retries 10
    env:
      DATABASE_URL: postgres://postgres:postgres@localhost:5432/postgres
    steps:
      - uses: actions/checkout@v7
      - uses: actions/setup-node@v7
        with:
          node-version: 22
          cache: npm
      - run: npm ci
      - run: npx prisma migrate deploy # or: npx knex migrate:latest, npx typeorm migration:run -d ...
      - run: npm test

Use the same PostgreSQL major version as production. For large tables, also try risky migrations against a restored copy of production data, because a change that's instant on an empty table can take minutes on fifty million rows.

Rule 5: run them from inside your network#

Your production database shouldn't accept connections from the internet, which means a GitHub-hosted runner can't reach it. Run the migration where the database is reachable: on the server, with the image you're about to release. In the blue-green release script, that's one line between pulling the image and starting the new colour:

docker pull "$IMAGE"

# Apply migrations with the new image. If this fails, set -e stops the release
# here, and the version already running keeps serving traffic.
docker run --rm --env-file /srv/api/.env "$IMAGE" npx prisma migrate deploy

docker run -d --name "api-$NEXT" ... "$IMAGE"

One catch: the migration CLI has to be in the production image. prisma is often a dev dependency and gets pruned from the runtime image, so move it to dependencies or build a separate migration target. TypeORM and Knex ship their CLIs in the main package.

Two releases must never migrate at the same time. A concurrency group in the workflow queues releases, and most migration tools also take a lock in the database while they run.

Rule 6: plan your way back#

• Prefer fixing forward. Down migrations are rarely tested, and many can't restore data a step deleted. A new migration that repairs the problem is usually safer.
• Back up before destructive steps. Take a snapshot before a migration that drops or rewrites data, and know how long a restore takes, because you've done one.
• Delay the contract step. Drop old columns a release or two after the code stopped using them, so the previous version is still a safe rollback target.

Checklist#

• Migrations run once, as their own release step, before the new code takes traffic
• Every migration works with the code that's currently running
• Renames and drops go through expand and contract
• Indexes are created CONCURRENTLY; NOT NULL goes through a validated CHECK
• Each migration sets a lock_timeout
• CI applies every migration to a real database before the tests
• Migrations run from inside the network, with the release image
• Backups exist, and a restore has been tested

FAQ#

Should database migrations run when the app starts?#

Usually not. With several instances, each one tries to run them at startup, a slow migration fails health checks, and a failing one crash-loops the app. Run migrations once as a separate step of the release, before the new version starts taking traffic.

How do I run database migrations in GitHub Actions?#

In CI, start a PostgreSQL service container, apply all migrations to it, then run the tests. For production, run the migration step from a place that can reach the database, usually the server itself, with the newly built image, before switching traffic to the new version.

How do I roll back a database migration?#

Prefer a new forward migration that fixes the problem, because down migrations are rarely tested and can't bring back deleted data. Keep migrations backward-compatible, so that rolling back the application code alone is enough, and take a backup before any destructive change.

How do zero-downtime database migrations work?#

They use expand and contract. Each migration only adds things the old code can ignore. New code then starts using them, and old columns or tables are removed in a later release, once no running version depends on them. Long locks are avoided with concurrent index builds, NOT VALID constraints and a lock_timeout.

Related reading#

• Deploying Node.js to production: the complete guide
• Zero-downtime deploys for a Node.js API with Docker, Nginx and GitHub Actions
• GitHub Actions CI/CD: build, test and deploy to EC2
• NestJS Dockerfile: multi-stage builds for production
• Bash scripts to automate Postgres backups to S3, for a verified backup before risky migrations
• Building a scalable SaaS backend with NestJS and PostgreSQL, where migrations fit in the wider backend
• How I made legacy queries 30% faster with EXPLAIN ANALYZE, for the indexes these migrations ship