Customizing Prisma Migrations for Zero Downtime

Prisma Migrate generates SQL from changes to schema.prisma, and by default prisma migrate dev generates and applies it in one step. That is convenient until the generated SQL is the wrong SQL for production: a renamed field becomes DROP COLUMN plus ADD COLUMN, a new required field becomes ADD COLUMN ... NOT NULL that fails on existing rows or needs a default Prisma then keeps, a new relation becomes a foreign key validated under lock, and every @@index becomes a blocking CREATE INDEX. The answer is not to stop using Migrate but to insert a review-and-edit step between generating and applying: prisma migrate dev --create-only. This guide shows how to use it to turn each risky generated change into its zero-downtime form, and how to keep the edited migration consistent with the schema. It is a core technique in Prisma Migration Strategies.

Generate, Edit, Then Apply Five steps. Change schema.prisma; run prisma migrate dev --create-only to write migration.sql without applying it; edit the SQL for renames, staged NOT NULL, NOT VALID constraints; apply locally with prisma migrate dev; deploy with prisma migrate deploy. Generate, Edit, Then Apply STEP 1 Edit schema.prisma model change STEP 2 --create-only migration.sql written, not run STEP 3 Edit the SQL rename, staging, online DDL STEP 4 migrate dev applies locally, checks drift STEP 5 migrate deploy pipeline, once
--create-only gives you a window between generating SQL and running it — that window is where zero-downtime edits happen.

Symptom / Error Signatures

Generated Prisma SQL needs editing when you see any of these in prisma/migrations/<timestamp>_<name>/migration.sql:

-- Prisma warning in the CLI: "You are about to drop the column `phone` on the `Customer` table, which still contains 1834 non-null values."
ALTER TABLE "Customer" DROP COLUMN "phone",
ADD COLUMN     "phoneNumber" TEXT;

Or the CLI stops with Added the required column region to the Order table without a default value. There are 40211 rows in this table, it is not possible to execute this step. Other red flags: CREATE INDEX "Order_customerRef_idx" ON "Order"("customerRef"); on a large table, and ADD CONSTRAINT ... FOREIGN KEY ... ON DELETE RESTRICT ON UPDATE CASCADE on tables with millions of rows.

Root Cause Analysis

Prisma computes a migration by diffing the schema implied by existing migrations (replayed on the shadow database) against schema.prisma. The diff produces the most direct SQL for the end state. Like every diff engine, it cannot distinguish a rename from a drop and add, it does not know table sizes, and it emits the standard blocking forms of index and constraint DDL. It also cannot sequence changes across releases.

Prisma supports hand-edited migrations explicitly. After --create-only, you may change migration.sql freely before it is applied anywhere; migrate dev then applies your edited SQL and, on subsequent runs, checks that the schema the migrations produce still matches schema.prisma. That drift check is your safety net: if an edit changes the end state, Prisma reports it rather than silently diverging.

Generated SQL Problem Edited form
DROP COLUMN phone, ADD COLUMN phoneNumber data loss ALTER TABLE ... RENAME COLUMN (or expand/contract)
ADD COLUMN region TEXT NOT NULL fails on existing rows / old code add nullable, backfill, tighten in a later migration
CREATE INDEX blocks writes CREATE INDEX CONCURRENTLY IF NOT EXISTS in its own migration
ADD CONSTRAINT ... FOREIGN KEY validates under lock ... NOT VALID, then VALIDATE CONSTRAINT in the next migration
ALTER COLUMN ... SET DATA TYPE table rewrite new column, backfill, swap
Generated vs Edited migration.sql for a New Required Field Two panels. Generated: one statement adding region as NOT NULL, which fails or needs a default and breaks old code. Edited: migration A adds region nullable; code writes it; a backfill fills old rows; migration B adds a CHECK constraint NOT VALID and validates it, then sets NOT NULL. Generated vs Edited migration.sql for a New Required Field Generated ALTER TABLE "Order" ADD COLUMN "region" TEXT NOT NULL fails on existing rows Edited across two migrations A: ADD COLUMN "region" TEXT (nullable) backfill in batches (job) B: CHECK ("region" IS NOT NULL) NOT VALID; VALIDATE B: SET NOT NULL (no scan, PG 12+) online, backward compatible
The schema ends up identical; the edited path never blocks and never breaks the version that is still running.

Immediate Mitigation

1. Stop letting migrate dev apply generated SQL unseen. Generate with --create-only, then read the file.

# Shell · developer workstation · local dev database and shadow database
npx prisma migrate dev --create-only --name rename_customer_phone
cat prisma/migrations/*_rename_customer_phone/migration.sql

2. Replace drop-and-add renames with a real rename — or stage it. If the application can switch names in one release with no overlap, a rename is enough; for rolling deploys, use @map so the field name changes in Prisma while the column name stays, which requires no SQL at all.

-- PostgreSQL · prisma/migrations/20260918120000_rename_customer_phone/migration.sql
-- WARNING: running instances of the previous release still query "phone"; use only without overlap.
ALTER TABLE "Customer" RENAME COLUMN "phone" TO "phoneNumber";
-- ROLLBACK PATH: ALTER TABLE "Customer" RENAME COLUMN "phoneNumber" TO "phone";
// Prisma schema · zero-DDL alternative: rename the field, keep the column
model Customer {
  id          Int     @id @default(autoincrement())
  phoneNumber String? @map("phone")
}

3. Split required fields and constraints into safe steps. Edit the first migration to add the column nullable; after the backfill, a second --create-only migration adds a NOT VALID check, validates it, and sets NOT NULL. The mechanics are in adding NOT NULL via a CHECK constraint.

-- PostgreSQL 12+ · second migration, after the backfill has completed
SET lock_timeout = '3s';
ALTER TABLE "Order" ADD CONSTRAINT "Order_region_nn" CHECK ("region" IS NOT NULL) NOT VALID;
ALTER TABLE "Order" VALIDATE CONSTRAINT "Order_region_nn";
ALTER TABLE "Order" ALTER COLUMN "region" SET NOT NULL;
ALTER TABLE "Order" DROP CONSTRAINT "Order_region_nn";
-- ROLLBACK PATH: ALTER TABLE "Order" ALTER COLUMN "region" DROP NOT NULL;

4. Put concurrent index builds in their own single-statement migration. Prisma runs a migration file as written; a file containing only CREATE INDEX CONCURRENTLY IF NOT EXISTS ... avoids combining it with other statements in one transaction. Verify on your Prisma version in staging, and check for invalid indexes afterwards.

Permanent Fix / Long-Term Pattern

Make --create-only the team default and review migration.sql, not schema.prisma, in pull requests. A lightweight CI script can flag the generated patterns in the table above and require either an edit or an explicit acknowledgement. Keep every edited migration consistent with the schema — prisma migrate dev and prisma migrate diff will report drift if an edit changes the end state — and never edit a migration after it has been applied in a shared environment; write a new one instead, or you will hit the checksum problems covered in resolving Prisma migrate P3009 failed migrations.

Deploy with prisma migrate deploy as a single pipeline step using a direct (non-pooled) connection with a lock timeout, before the application rolls out, as covered in resolving Prisma connection pool timeouts. Destructive steps — the final drop of an old column after an expand-and-contract rename — ship in their own later migration once no running version reads the column, following Expand and Contract Methodology.

Prisma Migration Review Gates Pipeline. Generate with --create-only; a gate scans migration.sql for DROP COLUMN, NOT NULL additions, plain CREATE INDEX and validated foreign keys; developer edits; migrate dev confirms no drift; migrate deploy runs once in the pipeline. Prisma Migration Review Gates --create-only migration.sql scan risky SQL? migrate dev no drift review SQL approved? migrate deploy once, direct URL edit before merge request changes fail
The scan does not need to understand Prisma — it only needs to recognise five dangerous SQL shapes in the generated file.

Verification Checklist

Frequently Asked Questions

Is editing Prisma-generated SQL supported? Yes. Prisma documents customizing migrations with --create-only. You can edit the SQL before the migration is applied; Prisma records a checksum when it applies the file, so edits must happen before that point.

Will Prisma notice if my edits change the end schema? Yes. prisma migrate dev compares the schema produced by the migration history with schema.prisma and reports drift, prompting a new migration. That keeps edited migrations honest.

How do I rename a field without SQL? Use @map("old_column") on the renamed field (and @@map for models). The Prisma client uses the new name while the database column keeps the old one, so no migration and no deploy risk are involved.

Can Prisma create indexes concurrently? Not from @@index directly. Write CREATE INDEX CONCURRENTLY IF NOT EXISTS by hand in a migration of its own, keep the @@index in the schema so the model matches, and confirm in staging that your Prisma version runs the single-statement migration successfully.

Related Articles