Database Migrations

A database migration is a versioned, trackable change to our database schema. Instead of manually running ALTER TABLE on production (please don’t), we write migration files that describe exactly what changed and in what order.

Think of it like git for our database schema. Each migration is a commit — we can see the history, roll forward, and roll back.

Why We Need Migrations

Without migrations, schema changes become chaos:

• “Did you run the ALTER TABLE on production?”
• “Which version of the schema does this server have?”
• “Someone added a column but didn’t tell the team.”
• “The staging database has a different schema than production.”

Migrations solve all of this by making schema changes code. They live in git, get reviewed in PRs, and run in order.

Up and Down Migrations

Every migration has two parts:

• Up — applies the change (add column, create table, create index)
• Down — reverses the change (drop column, drop table, drop index)

-- Migration: 001_create_users_table.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Migration: 001_create_users_table.down.sql
DROP TABLE IF EXISTS users;

-- Migration: 002_add_phone_to_users.up.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Migration: 002_add_phone_to_users.down.sql
ALTER TABLE users DROP COLUMN phone;

The migration tool tracks which migrations have been applied (usually in a schema_migrations table) and only runs the ones that haven’t been applied yet.

How Migration Tools Work

The migration tool:

1. Checks the schema_migrations table to see what’s been run
2. Finds new migration files that haven’t been applied
3. Runs them in order
4. Records each successful migration

Popular Migration Tools

-- Example: Knex.js migration file
-- exports.up = function(knex) {
--   return knex.schema.createTable('users', (table) => {
--     table.increments('id');
--     table.string('name').notNullable();
--     table.string('email').unique().notNullable();
--     table.timestamps(true, true);
--   });
-- };
--
-- exports.down = function(knex) {
--   return knex.schema.dropTable('users');
-- };

Zero-Downtime Migrations

The hardest part of migrations isn’t writing them — it’s running them on a production database with live traffic and zero downtime.

The Expand-Contract Pattern

This is the gold standard for zero-downtime schema changes. It works in three phases:

Phase 1: Expand — add the new structure without removing the old one

-- We want to rename "name" to "full_name"
-- DON'T: ALTER TABLE users RENAME COLUMN name TO full_name;
-- (This breaks all queries using "name" instantly)

-- DO: Add the new column alongside the old one
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);

-- Backfill existing data
UPDATE users SET full_name = name WHERE full_name IS NULL;

Phase 2: Migrate — update application code to use the new column. Deploy the app.

Phase 3: Contract — once all code uses the new column, remove the old one.

-- Only after the app is fully migrated:
ALTER TABLE users DROP COLUMN name;

Adding a Column

Adding a nullable column is usually safe. Adding a NOT NULL column or a column with a DEFAULT can lock the table in some databases.

-- Safe: add a nullable column (instant in PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;

-- Dangerous in older databases: adding with DEFAULT
-- This rewrites every row in the table!
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- Safe alternative: add nullable first, then backfill
ALTER TABLE users ADD COLUMN status VARCHAR(20);
UPDATE users SET status = 'active' WHERE status IS NULL;
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

Adding an Index

Creating an index on a large table can lock it for minutes or hours. Use CONCURRENTLY:

-- Blocks reads/writes during creation (bad!)
CREATE INDEX idx_users_email ON users(email);

-- Doesn't block other operations (good! But slower to build)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Common Pitfalls

1. Dropping a Column That’s Still Being Read

If the old code is still running when we drop a column, queries break. Always deploy the code change first, then drop the column in a later migration.

2. Renaming a Table or Column

A rename is effectively a drop + create. The old name stops working instantly. Use the expand-contract pattern instead.

3. Not Testing Migrations on a Copy of Production

A migration that works on a dev database with 100 rows might lock a production table with 50 million rows for 30 minutes. Always test with production-like data volumes.

4. Making Migrations Non-Idempotent

A migration should be safe to run twice. Use IF NOT EXISTS and IF EXISTS:

-- Idempotent: safe to run multiple times
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20);

DROP INDEX IF EXISTS idx_users_email;

5. Mixing Schema Changes and Data Changes

Keep schema migrations and data migrations separate. Schema changes are usually fast and reversible. Data backfills can be slow and should run as background jobs.

Migration Best Practices

1. One change per migration — don’t create 5 tables in one file
2. Always write a down migration — even if we think we’ll never need it
3. Never modify an already-applied migration — create a new one instead
4. Test on staging first — with production-like data volumes
5. Use transactions — wrap the migration in BEGIN/COMMIT so it’s atomic (where supported)
6. Review migrations in PRs — schema changes deserve as much scrutiny as code changes

Interview Tip

Migrations come up in system design and backend interviews. The key thing to demonstrate is awareness of the zero-downtime problem — that you can’t just ALTER TABLE on a live database with millions of users. Mention the expand-contract pattern and CREATE INDEX CONCURRENTLY. That shows real production experience.