Database Migrations
A migration is a versioned, repeatable script that changes your database schema (or data) and travels with your code. Migrations turn ad-hoc ALTER TABLE commands into reviewable, ordered, reproducible changes — so every environment converges to the same schema.
The real challenge isn't writing the change; it's applying it safely against live production data without locking tables or breaking the currently-running app version. Optimize for safety and repeatability, not cleverness.
TL;DR
- Version-control schema changes; apply them in order via a migrations table.
- Never edit an applied migration — write a new one.
- Use expand/contract for zero-downtime changes.
- Backfill in batches; avoid long-running, table-locking statements.
Quick Example
The safe way to add a column — nullable with a default avoids rewriting/locking the whole table:
Core Concepts
- Versioned scripts — each migration is a timestamped/numbered file applied once, tracked in a migrations table.
- Up / down — apply and (optionally) rollback steps.
- Tools — Prisma, Knex, TypeORM (Node); Alembic, Django (Python); Active Record (Ruby); Flyway, Liquibase, golang-migrate, sqitch (cross-language).
Zero-Downtime: Expand / Contract
Never change a column out from under a running app. Stage it so old and new code both work:
For a NOT NULL column on a big table, do it in three steps rather than one locking statement:
Best Practices
A migration should be:
- ✅ Tested on empty and production-sized data.
- ✅ Aware of its lock/scan behavior (the thing that causes outages).
- ✅ Backed by a rollback or forward-fix plan.
- ✅ Backed up before applying, and observable (metrics/logs for long backfills).
Common Mistakes
Adding a NOT NULL column without a default
Dropping a column too early
FAQ
Should migrations always be reversible?
Ideally schema migrations have a down, but in production a "rollback" can lose data (a dropped column can't be un-dropped). For production incidents, prefer a forward fix (a new migration) over relying on down migrations.
How do I run a migration with zero downtime?
Use expand/contract: add new structures without removing old ones, deploy code that writes to both, backfill existing rows in batches, switch reads to the new structure, then clean up. Each step is backward-compatible with the running app.
How do I backfill a huge table safely?
In batches, as an application/job-runner loop (not one giant UPDATE), so you avoid long locks and WAL bloat. Monitor duration, rows processed, and replication lag, and pause if lag grows.
Can I edit a migration that's already been applied?
No. Once applied somewhere, a migration is immutable — editing it makes environments diverge. Write a new migration to make further changes.
Related Topics
- PostgreSQL — Lock behavior of DDL
- DevOps — Deploying migrations safely
- Database Backups — Back up before you migrate
- Supabase — Migrations in a Supabase workflow
- Data Engineering — Larger data pipelines