Database migrations

Schema changes are the riskiest deploys you'll ever do — they're stateful, often non-reversible, and a bad one takes down writes. The "expand and contract" pattern, and a discipline of never combining a column rename with a code change, eliminate 90% of the risk.

The big idea

Run migrations separately from app deploys, and never let one deploy require both old and new code paths to read or write incompatible schemas. The expand-and-contract pattern formalises this.

Why one-shot migrations break

A "rename username to handle" deploy looks innocent. In production:

1. 1

   The migration runs at deploy time

   Renames the column.

2. 2

   Old app instances still running

   They query SELECT username FROM users and crash.

3. 3

   New instances expect handle

   They query SELECT handle FROM users and work — until they hit a request that was routed mid-rollout.

4. 4

   Rolling back is impossible

   The old column doesn't exist anymore. The old app version can't run.

The same change, done safely

1. 1

   Deploy 1 — expand

   Migration: ALTER TABLE users ADD COLUMN handle TEXT; Code: still reads/writes username. No risk.

2. 2

   Deploy 2 — dual-write

   Code: writes both username and handle. Reads username. Both columns track.

3. 3

   Backfill

   Batched job: UPDATE users SET handle = username WHERE handle IS NULL. Run in chunks of 10k so locks don't pile up.

4. 4

   Deploy 3 — migrate reads

   Code: writes both, reads handle. Verify in production.

5. 5

   Deploy 4 — contract

   Code: writes only handle. Migration: ALTER TABLE users DROP COLUMN username;

Five steps, four deploys. Slow — and safe. Every step is independently revertible.

What's risky in a migration

Tool choice

The standard tools enforce ordering and history:

Alembic (SQLAlchemy)

Python; revision graph; upgrade and downgrade.

Prisma Migrate

TypeScript; schema-first; generates SQL.

Flyway / Liquibase

JVM; simple, battle-tested SQL-file approach.

goose / sqlx

Go; lightweight, SQL-file or Go-function migrations.

Rails migrations

Active Record's; one of the originals.

All of them:

• Write a schema_migrations table tracking which migrations have run.
• Order migrations by timestamp or version.
• Refuse to run the same migration twice.

// Knex example: add the new column (expand)
export async function up(knex: Knex) {
  await knex.schema.alterTable('users', (t) => {
    t.text('handle').nullable();
  });
}
 
export async function down(knex: Knex) {
  await knex.schema.alterTable('users', (t) => {
    t.dropColumn('handle');
  });
}

# Alembic example: add the new column (expand)
def upgrade():
    op.add_column('users', sa.Column('handle', sa.Text(), nullable=True))
 
def downgrade():
    op.drop_column('users', 'handle')

Backfills

A migration that touches every row in a 50M-row table will lock everything for minutes. Don't do it in one statement.

-- Bad: one giant transaction
UPDATE users SET handle = username WHERE handle IS NULL;
 
-- Good: bite-sized batches via a job
WITH batch AS (
  SELECT id FROM users WHERE handle IS NULL LIMIT 5000 FOR UPDATE SKIP LOCKED
)
UPDATE users u SET handle = u.username FROM batch WHERE u.id = batch.id;
-- run this loop until 0 rows affected, sleeping 100ms between batches

In practice

The discipline: schema changes deploy alone, code reads new columns only after a backfill, drops happen in their own deploy after a wait period. Tooling like strong_migrations (Ruby) and squawk (Postgres SQL linter) automatically catches the dangerous patterns.