Migration Safety Pro

Run ALTER TABLE on a 10-million-row table without locking writes, losing data, or spending 3 hours writing rollback scripts.

The Problem

Your PM asks to rename the email column to email_normalized across 4 services using Prisma, TypeORM, Sequelize, and Alembic. You run ALTER TABLE users RENAME COLUMN email TO email_normalized on a table with 12 million rows. The migration locks the table for 8 minutes. Every user-facing write query queues up, your connection pool exhausts, and the app returns 500s to every customer. You didn't write a rollback script because you didn't think a column rename needed one. Now you can't undo it without restoring from backup.

What You Get

• Framework detection engine — auto-detects Prisma, TypeORM, Sequelize, Knex, Alembic, Django, Rails, golang-migrate, or Liquibase from lockfiles, config files, and migration directory structure
• 4 zero-downtime migration strategies — Expand-Contract (add column, backfill, drop old), Background Copy with Dual-Write, Ghost Table (online schema change), and Feature Flag Migration, each with step-by-step implementation for your detected framework
• ORM-specific migration patterns — production-ready code snippets for each of 9 ORMs showing safe column addition, batch backfill, index creation with CONCURRENTLY, and NOT NULL constraint application
• Pre/post-migration safety checklists — 24-point mandatory checklist covering backup verification, staging deployment, rollback plan, lock timeout, monitoring setup, and data integrity verification
• Rollback procedures — immediate rollback (migration failed), partial rollback (code broke), and data corruption recovery procedures with concrete commands for each database type
• Anti-pattern prevention — 10 documented "NEVER do this" patterns (ADD COLUMN NOT NULL, DROP without deprecation, synchronize:true in production) with safe alternatives

Why This Beats Prompting It Yourself

When you ask an agent to "write a database migration," it generates the DDL but rarely considers row-level locking, backfill batching, or rollback strategy. It may write ALTER TABLE ADD COLUMN email_lower VARCHAR(255) NOT NULL — which locks the entire table during backfill. This skill forces the 3-step safe pattern: add nullable column → batch backfill (500 rows at a time) → set NOT NULL, with a down migration tested at every step. It also requires feature flag integration so code changes and schema changes are decoupled.

Example Session

You: We need to add a normalized_email column to the users table (8M rows) using Prisma. Zero downtime.

Agent: Detected stack: Prisma + PostgreSQL. Strategy: Expand-Contract. Phase 1 (Expand): ALTER TABLE users ADD COLUMN normalized_email VARCHAR(255); — nullable, no lock. Deploy code that writes to BOTH email and normalized_email. Phase 2 (Backfill): Batch update 500 rows/commit with UPDATE users SET normalized_email = LOWER(email) WHERE normalized_email IS NULL LIMIT 500; — estimated 4.5 hours for 8M rows. Phase 3 (Contract): After backfill verified (0 nulls), ALTER TABLE users ALTER COLUMN normalized_email SET NOT NULL; CREATE UNIQUE INDEX CONCURRENTLY ix_users_normalized_email ON users(normalized_email); — deploy code reading from normalized_email only. Remove old writes. Feature flag cleanup after 72h validation. Rollback: ALTER TABLE users DROP COLUMN normalized_email; — instant, zero data loss. Pre-deployment checklist: ✅ Backup verified, ✅ Staging tested with 8M rows, ✅ Rollback plan tested, ✅ On-call notified, ✅ Monitoring alerts configured.

Use Cases

• Adding a new required column to a production table with millions of rows without downtime
• Renaming a column across a monorepo where multiple ORMs connect to the same database
• Implementing a soft-delete pattern with Prisma client extensions and a zero-downtime migration
• Converting a single-tenant schema to multi-tenant with a new tenantId column and composite unique constraints
• Setting up a pre-deployment CI gate that validates migration safety before production deployment

Known Limitations

Online schema change tools like gh-ost (MySQL) and pg_repack (PostgreSQL) require database administrator access and may not be available in managed database services. For tables over 100M rows, even batched migrations may require coordination with your DBA team for maintenance window scheduling.

---

Tags: database migration devops zero-downtime postgresql prisma typeorm production-safety

Version: 1.0.0