Open this lesson in your favourite AI. It'll walk you through the why, explain the demo, and quiz you on the try-it list.
On Day 0 it's tempting to just open a psql shell and ALTER TABLE whenever you need a column. Three months later you have a production schema nobody can recreate from scratch, and a staging environment that drifted in unrelated ways. Schema migrations — versioned, ordered, idempotent SQL files in your repo — solve this with one practice: every schema change ships as a migration file, run by the same tool in dev, staging, and prod, recorded in a migrations table. The cost is a single npm dependency and a 10-line convention. The benefit is that 'rebuild prod from a fresh DB' is always a real, working option.
Every popular language has a battle-tested migration tool: golang-migrate, Flyway/Liquibase, sqlx-cli (Rust), Alembic (Python), Prisma Migrate / Knex (Node.js), ActiveRecord (Rails). They all do the same three things: keep an ordered set of .sql files in the repo, run them once each in order, and record what's been run in a schema_migrations table inside your DB. The convention is: write the migration, run it locally, commit, deploy, run it in prod via the same tool in CI. Never ALTER directly. Ever.
// Using node-pg-migrate (or similar). Files live in /migrations/
// 1700000001_create-users.sql
CREATE TABLE users (
id bigserial PRIMARY KEY,
email text NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now()
);
// 1700000002_add-display-name.sql
ALTER TABLE users ADD COLUMN display_name text;
// 1700000003_index-created-at.sql
CREATE INDEX CONCURRENTLY users_created_at_idx ON users(created_at);
// Run it (CI step):
// npm run migrate up
// What it does: looks at schema_migrations table, sees which files
// have NOT run, runs them in filename order inside a transaction
// each, then INSERTs the filename into schema_migrations.node main.jsdropdb mydev && createdb mydev && npm run migrate up. If that fails, your migrations aren't idempotent — fix them now, not at 2am.Use these three in order. Each builds on the one before.
What is a schema migration tool, and why does shipping schema changes via versioned files beat running ad-hoc ALTERs against prod?
Walk me through what happens when the migrator runs: how does it know which files have been applied, how does it handle concurrent migrator instances, what happens if a migration fails halfway?
I need to add a NOT NULL column to a 50M-row table without taking a multi-minute lock. Design the migration as a sequence of small, reversible steps that work under live traffic.