Prisma Migrations in Production: Zero-Downtime Schema Changes

Running prisma migrate deploy on a live database is risky. Schema changes that lock tables, drop columns, or rename things can take down production. Here's how to migrate safely.

The Dangerous Patterns

// DANGEROUS: Locks table, blocks reads/writes on large tables
model User {
  name String  // Adding NOT NULL without default on existing table
}

// DANGEROUS: Data loss if column has data
// model User { oldColumn removed }

// DANGEROUS: Renames translate to DROP + ADD in Prisma
model User {
  displayName String  // Was 'name' — Prisma drops 'name', adds 'displayName'
}

Safe Migration Pattern: Expand-Contract

Step 1: Expand (add new, keep old)

model User {
  name        String   // Old column — keep it
  displayName String?  // New column — nullable first
}

Step 2: Backfill data

// Run this as a one-time script, not in migration
await prisma.$executeRaw`
  UPDATE users SET display_name = name WHERE display_name IS NULL
`;

Step 3: Deploy code that writes to both columns

Step 4: Make new column required

model User {
  name        String  // Still keeping old
  displayName String  // Now NOT NULL — safe, all rows have data
}

Step 5: Deploy code that reads only new column

Step 6: Contract (remove old)

model User {
  displayName String  // Old 'name' column removed
}

Adding Index Without Locking (PostgreSQL)

-- Prisma generates: CREATE INDEX — locks table
-- Better: CREATE INDEX CONCURRENTLY — no lock

-- Edit the generated migration file before running:
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Shadow Database for CI

// schema.prisma
datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL") // CI only
}

Migration Checklist

Before running any migration on production:

• [ ] Test on a copy of production data first
• [ ] Check estimated lock duration on large tables
• [ ] Add columns as nullable (not NOT NULL) initially
• [ ] Use CONCURRENTLY for index creation
• [ ] Have a rollback plan
• [ ] Run during low-traffic window

Useful Commands

npx prisma migrate dev          # Dev: auto-apply
npx prisma migrate deploy       # Prod: apply pending migrations
npx prisma migrate status       # Check which migrations are applied
npx prisma migrate resolve      # Mark failed migration as resolved
npx prisma db push              # Push schema without migration (prototyping only)

Database migration patterns are included in the Ship Fast Skill Pack — /db skill generates safe migration strategies for your schema changes. $49 at whoffagents.com.