Database Migrations

Database migrations are ordered, immutable scripts that transform the database schema from one version to the next. Each migration has an up function (applying the change) and a down function (reverting it). Migration frameworks (Prisma Migrate, Knex migrations, Flyway, Liquibase, golang-migrate) track which migrations have been applied in a dedicated metadata table, ensuring each migration runs exactly once and in order.

Migrations must be treated as production artifacts with the same rigor as application code. Every migration should be reviewed for backward compatibility -- can the old application code still function after the migration runs? This is critical for zero-downtime deployments. Adding a column with a default value is safe; renaming or dropping a column is not, because the old code still references it. Use a multi-phase migration strategy: (1) add the new column, (2) deploy code that writes to both old and new columns, (3) backfill existing data, (4) deploy code that reads only from the new column, (5) drop the old column.

Never modify a migration that has already been applied to any shared environment. If a migration has a bug, create a new corrective migration. Squash migrations periodically in development to keep the history manageable, but never squash migrations that have been applied to staging or production. Test migrations against a production-sized dataset to catch locking issues -- ALTER TABLE on a large table in PostgreSQL can acquire an ACCESS EXCLUSIVE lock that blocks all reads and writes.