patternjavascriptprismaMajor
Migration rollbacks: expand-contract pattern for zero-downtime schema changes
Viewed 0 times
migration rollbackexpand-contractdown migrationzero downtimeschema changePrismaDrizzlecolumn rename
Problem
ORM migration tools (Prisma, Drizzle, Knex) do not auto-generate rollback/down migrations. Deploying a breaking schema change with no rollback plan means a failed deployment leaves the database in an inconsistent state.
Solution
Write explicit down migrations as SQL files. For Prisma, maintain a custom rollback SQL file alongside each migration. Practice expand-contract: add new columns before removing old ones, keeping both compatible with old and new code during the transition.
Why
Expand-contract enables zero-downtime deployments: first deploy the migration adding new columns (expand), then deploy the code using them, then deploy the migration removing old columns (contract). Each step is independently reversible.
Gotchas
- Dropping a column is not reversible without a backup — always make columns nullable before dropping
- Renaming a column is a two-step process: add new column, copy data, update code, drop old column
- Prisma does not track manually applied SQL — mark custom SQL migrations as applied with prisma migrate resolve
- Test rollback SQL in staging before deploying to production
Code Snippets
Expand-contract pattern for a column rename with safe rollback
-- Step 1: EXPAND — add new column (backward compatible, can roll back)
ALTER TABLE users ADD COLUMN username TEXT;
UPDATE users SET username = name WHERE username IS NULL;
-- Step 2: Deploy app code that reads username (falls back to name if null)
-- Step 3: Deploy app code that writes to both columns
-- Step 4: Verify no reads of old column in production logs
-- Step 5: CONTRACT — drop old column after confirming no reads
ALTER TABLE users DROP COLUMN name;
-- Rollback for step 1 (safe — only drops the new column)
ALTER TABLE users DROP COLUMN username;Revisions (0)
No revisions yet.