Database Migrations
Evolve your schema safely with versioned, reversible migration scripts.
What you'll learn
- What migrations are and why manual schema changes are dangerous
- The up/down pattern for reversible migrations
- Versioning and ordering migrations sequentially
- Running migrations in CI/CD and production
- Common migration pitfalls and how to avoid them
Concept
Why Migrations?
In development, you can drop and recreate a database freely. In production, the database has real data that cannot be lost. Migrations are versioned, incremental scripts that evolve the schema over time — adding a column here, creating a table there — without losing data. They are the database equivalent of version control for your schema.
The Up/Down Pattern
Each migration has two parts: up (apply the change) and down (revert it). The up migration for "add email column to users" is ALTER TABLE users ADD COLUMN email VARCHAR(255). The down migration is ALTER TABLE users DROP COLUMN email. Not all migrations are reversible (you cannot un-delete a dropped table's data), but striving for reversibility lets you roll back a bad deployment.
Versioning and Ordering
Migrations are numbered sequentially or timestamped (e.g. 20250115_120000_add_email_to_users.sql). The migration tool tracks which migrations have been applied in a schema_migrations table. On deploy, it runs all pending migrations in order. This ensures every environment has the same schema state.
Migration Tools
Every major framework has a migration tool: Rails (db:migrate), Django (makemigrations / migrate), Prisma (prisma migrate), Drizzle (drizzle-kit migrate), Flyway, Liquibase. They handle versioning, ordering, and tracking. Use the tool that integrates with your stack — do not roll your own.
Pitfalls
- Long-running migrations on large tables can lock the database. Break them into smaller steps or use online schema change tools (pt-online-schema-change, gh-ost).
- Destructive changes (DROP COLUMN, DROP TABLE) should be done in two phases: first stop using the column, then remove it in a later migration after a grace period.
- Data migrations (moving or transforming data, not just schema) should be separate from schema migrations and tested carefully.
Example
-- Migration 001: create users table
-- File: 001_create_users.up.sql
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- File: 001_create_users.down.sql
DROP TABLE IF EXISTS users;
-- Migration 002: add email column
-- File: 002_add_email_to_users.up.sql
ALTER TABLE users ADD COLUMN email VARCHAR(255);
CREATE UNIQUE INDEX idx_users_email ON users (email) WHERE email IS NOT NULL;
-- File: 002_add_email_to_users.down.sql
DROP INDEX IF EXISTS idx_users_email;
ALTER TABLE users DROP COLUMN email;
-- Migration 003: add orders table with FK
-- File: 003_create_orders.up.sql
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
total DECIMAL(10, 2) NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- File: 003_create_orders.down.sql
DROP TABLE IF EXISTS orders;
Three migrations with up and down: creating the users table, adding an email column with a partial unique index, and creating the orders table with a foreign key.
Try it
- SQL Formatter
Format migration scripts for readability and review.
- Code Diff
Compare up and down migrations to verify reversibility.
Common mistakes
The mistake
Writing irreversible migrations (no down script)
The fix
Always write a down migration when possible. For truly irreversible changes (like dropping a table), document why and make the down migration a no-op with a comment.
The mistake
Running a migration that locks a large table for too long
The fix
For large tables, break migrations into steps: add column (nullable), backfill data in batches, add constraints, remove old column. Use online schema change tools for huge tables.
Practice Database Migrations
3 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.