Migrations

Evolve your schema safely with versioned migrations and careful rollout strategies.

Tooling

• SQL-first: Flyway, Sqitch
• Code-first: Prisma, Sequelize, Knex, ActiveRecord
• Mixed: Liquibase Pick a tool that fits your stack and review generated SQL.

Migration files

• Numbered/sequenced files with up/down (or forward-only) steps
• Keep migrations idempotent where feasible (guard with IF EXISTS/IF NOT EXISTS)

Backward-compatible changes

• Additive first: add new columns as nullable or with defaults
• Backfill data in batches
• Deploy code that writes both old and new fields
• Flip reads to new field after backfill
• Remove old column in a later migration

Online changes

• Avoid long locks on hot tables
• Use concurrent/online index creation where supported (Postgres CREATE INDEX CONCURRENTLY)
• Break large DDL into smaller steps

Data migrations

• Wrap in transactions when safe; chunk large updates
• Record progress checkpoints to allow resumes

Rollbacks

Prefer forward-only migrations + fix-forward; keep rollback scripts for critical cases

Environments and CI

• Apply migrations in staging before production
• Run in CI against a fresh database snapshot

Summary

• Version and review migrations; prefer backward-compatible, incremental steps
• Plan online changes and backfills to avoid downtime