What a schema migration is and why it replaces ad-hoc DDL

Otto wants to add a phone column to users. He opens psql and runs ALTER TABLE users ADD COLUMN phone TEXT. Three months later a new engineer joins, clones the repo, and gets errors because their database has no phone column. Nobody knows what to change. The schema has drifted.

The problem with ad-hoc DDL

Running ALTER TABLE directly in psql works once, on one machine, for one engineer. The moment a second environment exists — staging, CI, a teammate’s laptop — the schema diverges silently. Nobody tracks what changed or when. Forward reproducibility is gone.

A migration file encodes the change as a named, ordered artefact:

-- migrations/20260514_add_user_phone.sql
ALTER TABLE users ADD COLUMN phone TEXT;

The migration tool records which files have run in a schema_migrations table. A new engineer running prisma migrate dev or flyway migrate gets all pending migrations applied in order. The database reaches the same known state on every machine.

Migrations are versioned like commits

Deploy order matters

The database schema and the application code that uses it must be compatible at every moment of the deploy, because production traffic does not pause for your migration.

Two rules:

1. Additive change (new column the new code reads): run the migration before deploying code. Old code ignores the new column; new code finds it ready.
2. Removal (drop a column the old code reads): deploy new code first, wait for old code to drain, then run the migration. Otherwise old code queries a non-existent column and fails.

Single-transaction migrations that drop or rename columns in one step are the classic deploy-coupled bug; the fix is to split changes into safe additive steps — covered in lesson 05.