Database migration

In short: A migration is a versioned change to the structure of a database (new table, new column, index) that is applied automatically and traceably.

In more detail: Each change sits in a numbered file. A tool remembers which migrations have already run and executes only new ones. That way development, test and production have the same state.

In Depth

• Backward compatible: Create new columns as optional first, adapt the code, remove old things only later.
• Large tables: Changes can lock for a long time. Better in small steps or online.
• Backup: Take a backup before risky migrations.
• Write idempotently: CREATE TABLE IF NOT EXISTS and ADD COLUMN IF NOT EXISTS allow repeated execution.
• Tools: Drizzle, Prisma, Flyway, Liquibase, Alembic.

See also: Blue-green and canary deployment, Database transactions and ACID