migration-safety

Migration Safety

Rules

• Never run prisma migrate dev against production. Use prisma migrate deploy.
• Use prisma migrate diff to preview what Prisma will generate before applying.
• Use --create-only when you need to hand-edit the migration SQL before applying (partial indexes, CONCURRENTLY, constraint validation).
• New columns must be nullable or have a default. Adding a NOT NULL column without a default locks the table and fails on existing rows.
• One concern per migration. Don't combine schema changes with data backfills in the same migration file.

Workflow

1. Edit schema.prisma
2. prisma migrate dev --create-only    # generates SQL, does not apply
3. Review/edit the generated SQL        # add CONCURRENTLY, partial indexes, etc.
4. prisma migrate dev                   # apply locally
5. Test against seed data
6. prisma migrate deploy               # apply in staging/production

Safe Patterns

Adding a Required Column to an Existing Table

Do NOT add it as NOT NULL in one step. Prisma will generate a migration that fails on existing rows.

Step 1: Add as optional
  referenceNumber String? @map("reference_number")

Step 2: migrate dev --create-only, apply

Step 3: Backfill
  UPDATE matters SET reference_number = 'LEGACY-' || id WHERE reference_number IS NULL;

Step 4: Change to required
  referenceNumber String @map("reference_number")

Step 5: migrate dev --create-only, verify the generated ALTER uses SET NOT NULL

Adding an Index to a Large Table

Prisma generates CREATE INDEX, which locks the table. Edit the migration to use CONCURRENTLY:

-- Generated by Prisma (edit this):
-- CREATE INDEX "idx_matters_client_id" ON "matters"("client_id");

-- Replace with:
CREATE INDEX CONCURRENTLY "idx_matters_client_id" ON "matters"("client_id");

Note: CONCURRENTLY cannot run inside a transaction. Prisma wraps migrations in a transaction by default. You need to remove the transaction wrapper for this migration or split it into its own migration file with a blank -- migration.sql that runs outside the transaction.

Adding a Constraint Safely

-- In custom migration SQL:
ALTER TABLE matters ADD CONSTRAINT chk_total_positive CHECK (total >= 0) NOT VALID;
-- Separate migration or script:
ALTER TABLE matters VALIDATE CONSTRAINT chk_total_positive;

NOT VALID applies the constraint to new rows immediately without scanning existing rows. VALIDATE scans existing rows without holding an exclusive lock.

Dangerous Operations

Operation	Risk	Safe Alternative
Add NOT NULL column without default	Table lock, fails on existing rows	Add nullable, backfill, then set NOT NULL
CREATE INDEX on large table	Table lock for duration of build	CREATE INDEX CONCURRENTLY via --create-only
Drop column	Breaks running code during deploy	Stop reading column in code first, deploy, then drop in next migration
Rename column	Breaks running code	Add new column, copy data, update code, drop old column across multiple deploys
Add constraint	Full table scan with lock	NOT VALID then VALIDATE separately

Rollback Strategy

• Prisma has no built-in rollback command. Every migration that makes a destructive change should have a corresponding rollback SQL file kept alongside it (e.g. rollback.sql in the migration folder).
• For additive-only migrations (new tables, new nullable columns), rollback is rarely needed.
• For column drops or type changes, keep a backup of the column data before applying.

Correct

migrations/
  20260515120000_add_reference_number/
    migration.sql          # ALTER TABLE ADD COLUMN reference_number VARCHAR
    rollback.sql           # ALTER TABLE DROP COLUMN reference_number
  20260515120001_backfill_reference_number/
    migration.sql          # UPDATE matters SET reference_number = ...
  20260515120002_require_reference_number/
    migration.sql          # ALTER TABLE ALTER COLUMN reference_number SET NOT NULL

Incorrect

migrations/
  20260515120000_add_reference_number/
    migration.sql
    # Contains: ALTER TABLE ADD COLUMN reference_number VARCHAR NOT NULL DEFAULT ''
    # Locks table, writes empty string to every row, no rollback plan