Database Migrations

• A migration is a versioned, incremental change to the database schema (and sometimes data)
• Each migration is a script checked into the repo, so the schema evolves in lockstep with the code
• Solves the core problem: keeping every environment (dev, CI, staging, prod) and every teammate on the same schema
• Mostly a concern of schema-enforcing databases, like relational databases; schemaless stores (document, key-value) migrate data lazily in application code instead

Anatomy

• Migrations are ordered — by timestamp or sequential number — and applied in that order
• The DB records which migrations have run (a schema_migrations table), so each runs exactly once
• Two directions:
• up: apply the change (e.g. ADD COLUMN, CREATE TABLE)
• down: reverse it (rollback) — not always possible (dropping a column loses data)

-- 20260717_add_email_to_users.up.sql
ALTER TABLE users ADD COLUMN email TEXT;

-- 20260717_add_email_to_users.down.sql
ALTER TABLE users DROP COLUMN email;

Two flavours

• Schema migration
• Change table structure (DDL)

• Flyway, Liquibase, golang-migrate

• Data migration

• backfill / transform existing rows (DML)
• usually custom scripts

Tooling

• Standalone: Flyway, Liquibase, golang-migrate, dbmate — plain SQL files + a runner
• ORM-integrated: Alembic (SQLAlchemy/SQLModel), Django migrations, Rails Active Record, Prisma Migrate, TypeORM
• These can autogenerate migrations by diffing the ORM models against the live schema

Safe migrations in production

• Prefer backward-compatible changes so old and new app versions run against the schema at once (zero-downtime deploys)
• Expand / contract pattern for breaking changes:
• Expand — add the new column/table (nullable), deploy code that writes to both
• Backfill — migrate existing data

• Contract — switch reads over, then drop the old column in a later release

• Watch for locking: on large tables some DDL rewrites the whole table and blocks writes (Postgres ALTER varies by operation; use CREATE INDEX CONCURRENTLY)

• Never edit an already-applied migration — write a new one forward