The Hook (The "Byte-Sized" Intro)
Database migrations are irreversible in production. A dropped column can't be un-dropped. A renamed table breaks every query that uses the old name. The Git workflow for migrations requires forward-only changes, rollback scripts, and deploy-then-clean ordering. Get the order wrong and your users see errors.
📖 What is the Migration Workflow?
A structured Git workflow for managing schema changes, data migrations, and system migrations that ensures safety through ordering, reversibility, and staged deployment.
Conceptual Clarity
Migration safety rules:
| Rule | Why |
|---|---|
| One migration per PR | Easy to review, revert, and sequence |
| Forward-compatible | Old code must work with new schema |
| Include rollback | Every up has a corresponding down |
| Deploy in phases | Schema first, code second, cleanup third |
| Never delete in same PR | Add column → deploy code → drop old column |
3-phase deployment:
| Phase | What | Example |
|---|---|---|
| 1. Expand | Add new (backward-compatible) | Add email_v2 column |
| 2. Migrate | Deploy code using new | App writes to email_v2 |
| 3. Contract | Remove old | Drop email column |
Real-Life Analogy
Database migration is like changing the plumbing while the water is running. You install the new pipes alongside the old ones (expand), redirect the water (migrate), then remove the old pipes (contract). Never cut first.
Visual Architecture
Why It Matters
- Zero downtime: Expand-migrate-contract avoids breaking production.
- Reversibility: Each phase can be rolled back independently.
- Safety: Old and new code work simultaneously during transition.
- Team coordination: Clear phases prevent race conditions between deploys.
Code
# ─── Phase 1: Expand (PR 1) ───
# Migration: Add new column (backward-compatible)
# SQL: ALTER TABLE users ADD COLUMN email_v2 VARCHAR(255);
git checkout -b migration/add-email-v2
git add db/migrations/20260213_add_email_v2.sql
git commit -m "migration: add email_v2 column"
# Deploy this first — old code still works!
# ─── Phase 2: Migrate (PR 2) ───
# Update app code to write to both columns
# Backfill existing data
git checkout -b feature/use-email-v2
git commit -m "feat: write to email_v2, backfill existing"
# Deploy code — both columns in use
# ─── Phase 3: Contract (PR 3) ───
# Remove old column after verification
git checkout -b migration/remove-old-email
# SQL: ALTER TABLE users DROP COLUMN email;
# Rename email_v2 → email if needed
git commit -m "migration: drop legacy email column"
# Deploy last — only after Phase 2 is verified
# ─── Migration file naming ───
# Use timestamps for ordering:
# db/migrations/
# 20260213010000_add_email_v2.sql
# 20260214010000_update_code.sql
# 20260215010000_drop_old_email.sqlKey Takeaways
- Use the expand-migrate-contract pattern for zero-downtime migrations.
- One migration per PR — keeps changes reviewable and revertible.
- Never drop and add in the same PR — deploy in phases.
- Timestamp-based file naming ensures correct migration ordering.
Interview Prep
-
Q: How do you handle a database migration without downtime? A: Use the expand-migrate-contract pattern: first add the new schema (backward-compatible), then deploy code that uses the new schema, finally remove the old schema. Each phase is a separate PR and deployment.
-
Q: Why should you never drop a column and add code changes in the same PR? A: Because during deployment, there's a window where old code instances still run. If the column is dropped before all instances are updated, those instances crash. Phased deployment ensures backward compatibility.
-
Q: How do you ensure migrations run in the correct order? A: Use timestamp-prefixed migration filenames (e.g.,
20260213010000_add_column.sql). Migration tools (Flyway, Knex, Alembic) process them sequentially. Version control ensures the order is preserved.