Running Prisma migrations safely in production

Schema migrations are distributed systems problems dressed up as SQL files. Prisma gives you a workflow (migrate dev, migrate deploy, migrate diff) but cannot decide business risk for you: locks, backfills, and rollback strategy still belong to the team.

This post is a field guide for Postgres + Prisma in services that cannot afford long write outages.

The golden rules#

1. Never rewrite applied migrations in production. Append a new migration; history is append-only.
2. Prefer many small migrations over one “big bang” change—easier to bisect when something breaks.
3. Run prisma migrate deploy in the release pipeline, not from a laptop with migrate dev.

Expand / contract pattern#

For breaking changes (rename column, change type, split table), use two phases:

Phase A — expand#

• Add the new column/table nullable or with a safe default.
• Deploy application code that writes to both old and new (dual-write) or only new if reads still use old.
• Backfill historical rows in batches (LIMIT loops) to avoid one giant transaction.

Phase B — contract#

• Switch reads to the new shape.
• Remove dual-write and drop old columns in a later migration after metrics show no old clients.

Skipping expand/contract often means ACCESS EXCLUSIVE locks on large tables—users see timeouts.

Nullable → NOT NULL safely#

-- Migration 1: add nullable column
ALTER TABLE users ADD COLUMN display_name text;

-- App backfills display_name from legacy `name`

-- Migration 2: enforce NOT NULL after backfill verified
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;

Between migrations, add a CHECK or monitor null counts in metrics.

Shadow database#

Prisma compares your migration history against a shadow database to detect drift. In CI:

• Spin up ephemeral Postgres (Docker service or Neon branch).
• Run prisma migrate diff / migrate deploy against it from clean state.

This catches “works on my machine” migration ordering bugs.

Prisma CLI commands (where to use each)#

Command	Typical environment	Purpose
prisma migrate dev	Local developer machine	Create migration from schema diff; apply to dev DB
prisma migrate deploy	Staging / production CI	Apply pending migrations only
prisma migrate resolve	Production recovery	Mark migration as rolled back or applied after manual intervention
prisma db execute	Controlled automation	Run reviewed .sql against a target (avoid ad hoc prod usage)
prisma migrate diff	CI / local debugging	Compare two schemas or DBs and emit SQL

Rule: migrate dev never targets production. Production only ever sees migrate deploy from a locked pipeline.

Keep DDL separate from bulk data fixes#

Prisma migration SQL should focus on schema (tables, columns, indexes, constraints). If you need to rewrite millions of rows:

1. Ship the schema change that makes new columns available.
2. Run a worker or batched script that updates rows in chunks (WHERE id > $cursor ORDER BY id LIMIT 5000).
3. Add constraints (NOT NULL, FOREIGN KEY) only after the data is clean—VALIDATE CONSTRAINT in Postgres can defer enforcement cost.

Huge single-transaction UPDATEs inside a migration file risk long locks, replication lag, and failed deploys that are hard to resume.

Foreign keys and ordering#

Adding FKs can validate the whole child table against the parent. On large tables:

• Create the FK NOT VALID in one migration (fast), then VALIDATE CONSTRAINT in a follow-up migration or maintenance window.
• Or add indexes on referencing columns before the FK so validation stays bounded.

Indexes concurrently#

Postgres supports CREATE INDEX CONCURRENTLY to avoid blocking writes. Prisma raw SQL migrations (prisma migrate diff → SQL file) are appropriate here because the high-level schema DSL may not express every performance knob.

Rollbacks#

Prisma does not auto-generate down migrations. Operational rollback is often redeploy previous app version + forward-only database fix, not migrate down (which Prisma does not ship for deploy safety).

Document runbooks: if migration N fails, what SQL do operators run? What app version pairs with which schema?

Observability#

During deploy:

• Watch lock waits and statement timeouts in Postgres logs.
• Alert on error rate spikes correlated with migration timestamp.

Summary#

Prisma automates the mechanics of applying SQL migrations; expand/contract, batch backfills, and concurrent indexes handle scale. Treat migrations as code-reviewed, CI-tested, and observable operations—not afterthoughts at the end of a sprint.