Two years ago I watched a prisma migrate deploy step take our checkout API offline for four minutes during peak traffic. The migration itself was innocent-looking: ALTER TABLE orders ALTER COLUMN status SET NOT NULL. On a table with 40 million rows, Postgres took an ACCESS EXCLUSIVE lock to rewrite the column, every read and write queued behind it, and our connection pool saturated in under thirty seconds. Pods started failing health checks, ECS cycled them, and the retry storm made everything worse. Nobody touched the schema again without a plan after that.
If you’ve shipped a Node.js API against a real production database, you’ve probably had a version of this incident, or you will. The fix isn’t “be more careful.” It’s treating schema migrations as a distinct engineering problem from app deploys, with its own patterns, its own CI step, and its own rollback story. That’s what this post walks through, with Prisma and Postgres as the concrete stack, but the patterns apply to TypeORM, Knex, or raw SQL just as well.
Why App Deploys and Schema Migrations Need to Be Decoupled
The core mistake is coupling “deploy new code” and “change the schema” into one atomic event. In reality you have two independent systems moving at different speeds:
- Your application fleet — multiple pods/tasks running old and new code simultaneously during a rolling deploy.
- Your database schema — a single, shared, stateful resource every version of your app talks to.
During a rolling deploy on ECS or Kubernetes, old and new pods coexist for anywhere from 30 seconds to several minutes. If your migration and your deploy are wired together (e.g., a migrate deploy && node dist/server.js entrypoint script), you get one of two failure modes:
- New schema, old code — the old pods are still running while the migration has already dropped a column or renamed a table they depend on. They start throwing 500s.
- Old schema, new code — the new pods start before migrations finish, and they query columns that don’t exist yet.
The fix is to run migrations as a separate pipeline step, before the deploy, and to make sure every migration is compatible with both the old and new application code running at the same time. That second part is the real discipline — it’s not enough to move the migration earlier, the migration itself has to be safe for a mixed-version fleet.
CI/CD pipeline
├── 1. Run tests
├── 2. Build & push image
├── 3. Run migration (separate job, own IAM role, own logs)
├── 4. Health-check migration result
└── 5. Deploy new image (rolling update, old + new code coexist)
Once migrations are decoupled like this, the question becomes: how do you write a migration that’s safe for both code versions to see at once? That’s what the expand/contract pattern solves.
Expand/Contract Pattern for Backward-Compatible Schema Changes
The expand/contract pattern splits every “breaking” schema change into multiple non-breaking migrations spread across multiple deploys. You expand the schema to support both old and new code, deploy the app change, let it bake, then contract by removing what’s no longer needed.
Let’s walk through a real example: renaming orders.status to orders.order_status because "status" was ambiguous with three other tables.
Step 1 — Expand: add the new column, don’t touch the old one
-- migrations/20260810120000_add_order_status_column/migration.sql
-- EXPAND: add the new column as nullable, no lock contention on writes
ALTER TABLE orders ADD COLUMN order_status TEXT;
-- Backfill in batches, not a single UPDATE (see "long-running migrations" below)
At this point, old code (reading/writing status) and new code don't exist yet — this is purely additive, so it's safe to run against a live fleet with zero coordination.
Step 2 — Dual-write: app code writes to both columns
Ship an app deploy where the code writes to both columns and reads from the old one:
// src/services/orderService.ts
export async function updateOrderStatus(orderId: string, status: OrderStatus) {
return prisma.order.update({
where: { id: orderId },
data: {
status, // legacy column, still the source of truth
order_status: status, // new column, being populated in parallel
},
});
}
Backfill existing rows with a batched script (not a migration file — this runs against live traffic and needs to be resumable):
// scripts/backfill-order-status.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
const BATCH_SIZE = 5_000;
async function backfill() {
let cursor: string | undefined; while (true) {
const rows = await prisma.order.findMany({
where: { order_status: null },
take: BATCH_SIZE,
orderBy: { id: 'asc' },
...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
select: { id: true, status: true },
});if (rows.length === 0) break;
await prisma.$transaction(
rows.map((row) =>
prisma.order.update({
where: { id: row.id },
data: { order_status: row.status },
}),
),
);
cursor = rows[rows.length - 1].id;
console.log(`backfilled through ${cursor}`);
await new Promise((r) => setTimeout(r, 100)); // breathing room for the DB
}
}
backfill().finally(() => prisma.$disconnect());
Step 3 — Cut over: app code reads from the new column
Once the backfill is verified complete (SELECT count(*) FROM orders WHERE order_status IS NULL returns 0), ship a deploy that reads from order_status and stops writing to status:
export async function updateOrderStatus(orderId: string, status: OrderStatus) {
return prisma.order.update({
where: { id: orderId },
data: { order_status: status }, // status column is now dead weight
});
}Step 4 — Contract: drop the old column
Only after the cutover deploy has been stable for at least one full deploy cycle (so you’re certain no old pod is still running) do you drop the legacy column:
-- migrations/20260824090000_drop_legacy_status_column/migration.sql
-- CONTRACT: safe now that no running code references `status`
ALTER TABLE orders DROP COLUMN status;
The same pattern applies to renaming tables, changing a column’s type, or splitting a table — always: add new, dual-write, cut over, remove old, each as its own deploy. It feels slow the first time you do it. After the third production incident it feels like the only sane way to work.
Running Migrations Safely in CI/CD
Migrations should run as their own pipeline stage with its own failure semantics — not baked into the container’s entrypoint. Here’s the pattern I use with GitHub Actions deploying to ECS, with Prisma as the migration tool.
# .github/workflows/deploy.yml
name: Deploy API
on:
push:
branches: [main]
jobs:
build:
runs-on: ubuntu-latest
outputs:
image-tag: ${{ steps.build.outputs.tag }}
steps:
- uses: actions/checkout@v4
- name: Build and push image
id: build
run: |
TAG=${{ github.sha }}
docker build -t $ECR_REPO:$TAG .
docker push $ECR_REPO:$TAG
echo "tag=$TAG" >> "$GITHUB_OUTPUT"
migrate:
needs: build
runs-on: ubuntu-latest
environment: production
steps:
- uses: actions/checkout@v4
- uses: actions/setup-node@v4
with:
node-version: 20
- run: npm ci
- name: Run Prisma migrations against prod
env:
DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}
run: |
npx prisma migrate deploy
timeout-minutes: 10
- name: Verify schema matches migrations
env:
DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}
run: npx prisma migrate status
deploy:
needs: [build, migrate]
runs-on: ubuntu-latest
steps:
- name: Deploy new task definition to ECS
run: |
aws ecs update-service \
--cluster prod-cluster \
--service api-service \
--task-definition api-task:${{ needs.build.outputs.image-tag }} \
--force-new-deployment
A few things that matter here and are easy to overlook:
- migrate has its own job, its own timeout, and its own credentials — a dedicated DB user with ALTER/CREATE grants that your app's runtime user doesn't have. If the app container's role could alter tables, a compromised app pod could alter your schema.
- prisma migrate deploy, not prisma migrate dev — dev can drop and recreate the database if it detects drift; deploy only applies pending migration files and never touches data outside them.
- deploy depends on migrate succeeding. If the migration job fails, GitHub Actions won't even attempt the ECS deployment, so you never end up running new code against an unmigrated (or half-migrated) schema.
- Statement timeout is set at the connection level, not just the job timeout, so a lock-holding statement gets killed instead of holding the table hostage. Append ?statement_timeout=15000 (15s) to the migration job's DATABASE_URL. Any single statement that would lock longer than that gets killed and the migration fails loudly in CI instead of quietly starving prod traffic.
Handling Long-Running Migrations Without Locking Tables
Postgres takes different lock levels depending on the operation. ADD COLUMN ... DEFAULT used to rewrite the whole table under ACCESS EXCLUSIVE (fixed in PG 11+ for constant defaults, but not for volatile ones like now() or a subquery). CREATE INDEX locks out writes for the duration unless you use CONCURRENTLY. Know which category your change falls into before you run it.
Indexes: always concurrent
-- BAD: locks the table against writes until the index build finishes
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- GOOD: builds without blocking writes (takes longer, but prod stays up)
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);
By default Prisma wraps each migration file in a transaction, and CREATE INDEX CONCURRENTLY can't run inside one — Postgres will reject it outright. Prisma's migration engine actually detects this: when a migration.sql file contains CONCURRENTLY, it automatically applies that file outside a transaction. You don't need any special directive, just write the SQL correctly. Generate the migration with --create-only so you can hand-edit it before it applies:
npx prisma migrate dev --create-only --name add_customer_id_index
-- migrations/20260815140000_add_customer_id_index/migration.sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id ON orders (customer_id);
One catch: because this file runs outside a transaction, a failed CREATE INDEX CONCURRENTLY can leave behind an invalid index instead of rolling back cleanly. Always follow a failure with DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_id before retrying.
Backfills: batch, never a single UPDATE
A single UPDATE orders SET order_status = status on 40 million rows holds row locks for the entire statement duration and bloats your WAL, which can trigger replication lag or even disk pressure on a busy primary. Batch it (as shown in the backfill script above), and run it as a standalone job — not inside the CI migration step, which has a tight timeout. Kick it off manually or via a one-off ECS task after the expand migration lands:
// scripts/run-backfill.ts — invoked via `aws ecs run-task` with a one-off task definition,
// decoupled from the deploy pipeline entirely
import { backfill } from './backfill-order-status';
backfill()
.then(() => console.log('backfill complete'))
.catch((err) => {
console.error('backfill failed, safe to re-run — it resumes from NULLs', err);
process.exit(1);
});
NOT NULL constraints: validate separately
Adding NOT NULL directly requires a full table scan under lock. Postgres 12+ lets you split this into two cheap steps:
-- Step 1: add as a NOT VALID constraint — instant, no table scan
ALTER TABLE orders ADD CONSTRAINT order_status_not_null
CHECK (order_status IS NOT NULL) NOT VALID;
-- Step 2: validate separately — still scans the table, but takes only a SHARE UPDATE
-- EXCLUSIVE lock, which does not block reads or writes, only other schema changes
ALTER TABLE orders VALIDATE CONSTRAINT order_status_not_null;
Run step 2 as its own migration file, ideally during low-traffic hours, and only after you’re confident the backfill finished.
Rollback Strategy When a Migration Fails Mid-Deploy
Expand/contract mostly protects you from needing a schema rollback, because expand-phase migrations are additive and safe to leave in place even if the app deploy that was supposed to use them gets rolled back. But you still need a plan for the failure case: a migration partially applies, or applies cleanly but the following deploy is broken for unrelated reasons.
Rule one: never write a migration you can’t reverse without data loss. For every migration file, know in advance what the rollback looks like — write it down, even if Prisma doesn’t auto-generate down migrations.
-- migrations/20260810120000_add_order_status_column/rollback.sql
-- Keep this alongside the migration file (Prisma doesn't manage down-migrations,
-- so we track them ourselves in a rollback/ sibling directory)
ALTER TABLE orders DROP COLUMN IF EXISTS order_status;
For app-code rollbacks (reverting to the previous ECS task definition), the key invariant is: the previous app version must still work against the current schema. That’s only true if you followed expand/contract — the old code never referenced order_status, so rolling back the deploy is safe regardless of whether the column exists.
Here’s a rollback script I keep in the repo for the rare case a migration needs to be manually reversed after deploy:
// scripts/rollback-migration.ts
import { execSync } from 'node:child_process';
import { readFileSync, existsSync } from 'node:fs';
import { Client } from 'pg';
async function rollbackMigration(migrationName: string) {
const rollbackPath = `prisma/migrations/${migrationName}/rollback.sql`; if (!existsSync(rollbackPath)) {
throw new Error(
`No rollback.sql found for ${migrationName}. ` +
`Every migration must ship with a rollback file — refusing to guess.`,
);
}const sql = readFileSync(rollbackPath, 'utf-8');
const client = new Client({ connectionString: process.env.DATABASE_URL });
await client.connect();
try {
await client.query('BEGIN');
await client.query(sql);
// Remove the migration from Prisma's tracking table so `migrate status`
// reports it as pending again, not applied.
await client.query(
`DELETE FROM _prisma_migrations WHERE migration_name = $1`,
[migrationName],
);
await client.query('COMMIT');
console.log(`Rolled back ${migrationName}`);
} catch (err) {
await client.query('ROLLBACK');
console.error(`Rollback failed, transaction reverted:`, err);
process.exit(1);
} finally {
await client.end();
}
}const migrationName = process.argv[2];
if (!migrationName) {
console.error('Usage: ts-node rollback-migration.ts <migration_name>');
process.exit(1);
}
rollbackMigration(migrationName);
Run it manually and deliberately — npx ts-node scripts/rollback-migration.ts 20260810120000_add_order_status_column — never as an automated step in CI. A schema rollback is a decision that should have a human's name attached to it, because unlike app deploys, it can be genuinely destructive if the migration already backfilled data that the rollback SQL doesn't account for.
Testing Migrations Against a Production-Like Dataset
The orders migration that took down our checkout flow passed every test in CI, because our test database had 200 rows. Table size and index bloat, not correctness, is what causes migration-related outages, so your test environment needs to reflect that.
I keep a nightly job that restores a scrubbed prod snapshot into a staging RDS instance, and every migration PR runs against it before merge.
# .github/workflows/migration-check.yml
name: Migration dry-run against staging snapshot
on:
pull_request:
paths:
- 'prisma/migrations/**'
jobs:
dry-run:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: actions/setup-node@v4
with:
node-version: 20
- run: npm ci
- name: Restore latest scrubbed prod snapshot
run: |
aws rds restore-db-instance-from-db-snapshot \
--db-instance-identifier migration-check-${{ github.event.pull_request.number }} \
--db-snapshot-identifier prod-nightly-scrubbed-latest \
--db-instance-class db.t3.large
- name: Wait for instance availability
run: |
aws rds wait db-instance-available \
--db-instance-identifier migration-check-${{ github.event.pull_request.number }}
- name: Time the migration
env:
DATABASE_URL: ${{ secrets.STAGING_SNAPSHOT_URL }}
run: |
START=$(date +%s)
npx prisma migrate deploy
END=$(date +%s)
echo "Migration took $((END - START)) seconds"
if [ $((END - START)) -gt 120 ]; then
echo "::error::Migration exceeded 2-minute budget — split it or add CONCURRENTLY"
exit 1
fi
- name: Check for lock contention during apply
env:
DATABASE_URL: ${{ secrets.STAGING_SNAPSHOT_URL }}
run: |
psql "$DATABASE_URL" -c "
SELECT relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;
" | tee lock-report.txt
if grep -q "false" lock-report.txt; then
echo "::warning::Ungranted locks detected during migration window"
fi
- name: Tear down snapshot instance
if: always()
run: |
aws rds delete-db-instance \
--db-instance-identifier migration-check-${{ github.event.pull_request.number }} \
--skip-final-snapshot
The scrub script that produces prod-nightly-scrubbed-latest overwrites PII after a raw snapshot restore, before it gets tagged as the shared fixture:
-- scripts/scrub-snapshot.sql — run once against the nightly restore before tagging it
UPDATE users SET
email = 'user' || id || '@example.test',
phone = NULL,
full_name = 'Test User ' || id;
UPDATE orders SET
shipping_address = '123 Test St',
billing_last4 = '0000';
Two minutes as a timeout budget is arbitrary — set yours based on your deploy window and how long you’re willing to hold a lock. The point is having any real-size dataset in the loop before merge, not the specific number.
Checklist: Migration Review Before Merge
I pin this as a PR template comment on any file touching prisma/migrations/:
## Migration review checklist
- [ ] Migration is additive only (no DROP/RENAME) — or this is an explicit
contract-phase PR referencing the expand PR it follows
- [ ] No `ALTER TABLE ... ADD COLUMN ... DEFAULT <volatile expr>` on a large table
- [ ] Any new index uses `CREATE INDEX CONCURRENTLY`
- [ ] Any new `NOT NULL` constraint uses the `NOT VALID` + `VALIDATE CONSTRAINT` split
- [ ] Backfills are batched scripts, not raw `UPDATE` statements in the migration file
- [ ] `rollback.sql` exists alongside the migration and has been tested locally
- [ ] Dry-run against the staging snapshot completed in under the timeout budget
- [ ] Old application code (currently in prod) still works if this migration
runs but the corresponding app deploy is rolled back
- [ ] New application code does not depend on this migration having run —
or the deploy pipeline explicitly gates on migration success
- [ ] Migration owner is on-call or reachable during the deploy window
That last line matters more than it looks. Every migration-caused outage I’ve been part of happened when the person who wrote the migration was asleep, in a meeting, or on a plane when it ran.
Wrapping Up
The pattern underneath all of this is simple even though the mechanics take some getting used to: never let a single migration require the old and new versions of your app to disagree about the schema. Decouple the migration step from the deploy step in your pipeline, default every schema change to expand/contract instead of a single destructive statement, and treat lock-heavy operations (CREATE INDEX, NOT NULL, large backfills) as their own category requiring CONCURRENTLY, NOT VALID, or batching.
If you’re starting from a codebase where migrations currently run inside the app’s entrypoint script, that’s the first thing to fix — split it into its own CI job today, even before you touch expand/contract. Then pick your next schema change, no matter how small, and write it as two migrations instead of one. It’ll feel like overhead the first time. It’s a lot less overhead than a four-minute prod outage during checkout.
