DB Migration Guide

Why Database Migrations Matter

Database migrations are among the highest-risk operations in any application lifecycle. Every schema change — adding a column, creating an index, modifying a constraint — can affect data integrity, query performance, or even bring down your service entirely. Without versioned migration management, teams quickly fall into the "works on my machine" trap, and production schemas drift away from development environments. This guide covers 12 popular migration tools, zero-downtime strategies, PostgreSQL and MySQL syntax examples, comprehensive checklists, and common pitfalls to help you manage database changes safely and reliably.

Migration Tools Comparison (12 Tools)

Tool	Language	Approach	Rollback Support	Best For
Flyway	Java / CLI	Versioned SQL files (V1__name.sql)	Paid Undo feature	Java apps, SQL-first teams
Liquibase	Java / CLI	XML / YAML / SQL changelogs	Auto rollback	Multi-DB, complex change sets
golang-migrate	Go / CLI	Numbered .up.sql / .down.sql	Hand-written down files	Go apps, simple SQL migrations
Alembic	Python	Python scripts with upgrade / downgrade	downgrade function	SQLAlchemy / Python projects
Prisma Migrate	TypeScript	Schema-diff based, auto-generated	Manual down scripts	Node.js / TypeScript apps
Atlas	Go / CLI	Declarative (desired state) or versioned	Auto-generated reverse	Modern Go apps, CI/CD
Knex.js	JavaScript	JS/TS migration functions (up / down)	Hand-written down function	Node.js / Express projects
TypeORM	TypeScript	Auto-detects entity changes, generates migrations	Auto-generated down	TypeScript full-stack apps
Sequelize	JavaScript	JS migration files (up / down)	Hand-written down function	Node.js legacy projects
Django Migrations	Python	Auto-detects Model changes, generates migrations	Auto-generated reverse	Django web apps
Rails ActiveRecord	Ruby	DSL migration files (change / up / down)	Auto-reversible or manual down	Ruby on Rails projects
dbmate	Go / CLI	Plain SQL .up.sql / .down.sql	Hand-written down files	Lightweight, language-agnostic

Migration File Structure

golang-migrate (numbered up/down)

db/migrations/
├── 000001_create_users.up.sql
├── 000001_create_users.down.sql
├── 000002_add_email_index.up.sql
├── 000002_add_email_index.down.sql
├── 000003_add_orders_table.up.sql
└── 000003_add_orders_table.down.sql

Flyway (V / R / U__)

sql/
├── V001__create_users.sql            -- versioned (runs once, in order)
├── V002__add_orders_table.sql
├── R__refresh_materialized_view.sql  -- repeatable (re-runs when content changes)
└── U001__undo_create_users.sql       -- undo (paid feature)

Prisma (auto-generated)

prisma/
├── schema.prisma                      -- declarative schema
└── migrations/
    ├── 20260101120000_init/
    │   └── migration.sql
    ├── 20260115090000_add_orders/
    │   └── migration.sql
    └── migration_lock.toml

Django (auto-generated)

myapp/migrations/
├── __init__.py
├── 0001_initial.py                    -- auto-detected from models.py
├── 0002_add_email_field.py
└── 0003_create_order_model.py

# Generate migration: python manage.py makemigrations
# Run migrations:     python manage.py migrate
# Show status:        python manage.py showmigrations

Common Migration Operations (PostgreSQL & MySQL)

Create Table

-- PostgreSQL
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id),
    total       NUMERIC(12,2) NOT NULL DEFAULT 0,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- MySQL
CREATE TABLE orders (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    total       DECIMAL(12,2) NOT NULL DEFAULT 0,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Add Column (with default — zero-downtime safe)

-- PostgreSQL (PG 11+ adds columns with defaults without table rewrite)
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT '';

-- MySQL (8.0+ INSTANT algorithm, near-zero lock time)
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT '', ALGORITHM=INSTANT;

Remove Column (expand-contract)

-- Step 1: Deploy new code that no longer reads old_column
-- Step 2: Confirm no active queries reference old_column
-- Step 3:

-- PostgreSQL
ALTER TABLE users DROP COLUMN old_column;

-- MySQL
ALTER TABLE users DROP COLUMN old_column;

Rename Column (expand-contract)

-- NEVER do a direct rename (breaks live app immediately):
-- ALTER TABLE users RENAME COLUMN username TO display_name;

-- Correct approach: add new → backfill → dual-write → switch reads → drop old

-- Step 1 (Expand)
ALTER TABLE users ADD COLUMN display_name VARCHAR(100);
-- Step 2 (Backfill)
UPDATE users SET display_name = username WHERE display_name IS NULL;
-- Steps 3-4: Deploy code that dual-writes, then switch reads to display_name
-- Step 5 (Contract)
ALTER TABLE users DROP COLUMN username;

Add Index (without locking)

-- PostgreSQL — CONCURRENTLY does not block writes
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- MySQL — INPLACE algorithm (8.0+), allows concurrent DML
ALTER TABLE orders ADD INDEX idx_orders_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE;

Change Column Type

-- PostgreSQL (small tables can alter directly; large tables: add new column + backfill)
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(14,2);

-- MySQL (large tables: use pt-online-schema-change)
-- Direct approach (locks table! small tables only):
ALTER TABLE products MODIFY COLUMN price DECIMAL(14,2);

Add Foreign Key

-- PostgreSQL — NOT VALID creates constraint without checking existing rows, then validate async
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

-- MySQL
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id);

Zero-Downtime Migration Strategies

Expand-Contract Pattern

The most universal zero-downtime approach, executed in 5 phases:

Phase	Action	Notes
1. Expand	Add new column / table	Additive only — no impact on existing code
2. Backfill	Migrate old data to new structure	Process in batches to avoid long locks
3. Dual-write	Deploy code writing to both old and new	Ensures data consistency during transition
4. Switch reads	Deploy code reading from new structure	Old structure becomes a fallback
5. Contract	Remove old column / table	Only after confirming no rollback is needed

Blue-Green Database Deployments

Maintain two database instances (Blue and Green). Run migrations on Green, verify correctness, then switch traffic. Suited for major version upgrades but requires double storage and a data synchronization mechanism such as logical replication.

Online DDL Tools

Tool	Database	Mechanism
pt-online-schema-change	MySQL	Creates shadow table → installs triggers → copies data → atomic rename
gh-ost	MySQL	Replicates changes via binlog, no triggers needed — safer approach
pg_repack	PostgreSQL	Rebuilds tables and indexes online, reclaims bloat
pgroll	PostgreSQL	Multi-version schema coexistence, progressive migrations

Shadow Table Approach

-- 1. Create shadow table matching original (with new schema changes)
CREATE TABLE users_new (LIKE users INCLUDING ALL);
ALTER TABLE users_new ADD COLUMN display_name VARCHAR(100);

-- 2. Install triggers to sync incremental changes
-- 3. Batch-copy existing data to shadow table
-- 4. Atomic swap
ALTER TABLE users RENAME TO users_old;
ALTER TABLE users_new RENAME TO users;

-- 5. Drop old table
DROP TABLE users_old;

Migration Checklist

Phase	Action	Details
Before	Take a full database backup	pg_dump / mysqldump / cloud snapshot
Before	Test on staging with production data copy	Ensures migration script works on real data
Before	Estimate lock duration for DDL changes	Use pg_stat_activity to check active locks on large tables
Before	Prepare and test rollback script	Run down migration on staging first
Before	Notify team of migration window	Coordinate release freeze period
During	Monitor active connections and replication lag	Pause if lag exceeds threshold
During	Set statement_timeout / lock_timeout	Prevent indefinite lock waits from cascading
During	Manage connection pools	Temporarily increase pool size or pause non-critical services
After	Verify row counts and data integrity	Compare COUNT / CHECKSUM before and after
After	Monitor slow queries and index usage	Check pg_stat_user_indexes or EXPLAIN plans
After	Run performance benchmarks	Compare core query response times to baseline
Cleanup	Remove deprecated columns after old code is gone	Wait at least 1-2 release cycles

Common Pitfalls

Running untested migrations in production — Always test on staging with a production data copy first. A single syntax error can leave your migration in a half-applied dirty state.
No rollback scripts — Every up migration should have a corresponding down. Without rollback scripts, recovery means manual fixes under pressure.
Long-running locks on busy tables — ALTER TABLE on large tables can hold locks for minutes or hours. Use CONCURRENTLY, pt-online-schema-change, or gh-ost to avoid blocking.
Irreversible data loss — DROP COLUMN is permanent. Ensure data has been migrated to new columns and backups exist before dropping anything.
Adding NOT NULL columns without defaults — Existing rows cannot satisfy the constraint. Always ADD COLUMN with DEFAULT first, then alter as needed.
Forgetting to update ORM models — Migration changes the database schema, but if entity classes or models are not updated, you get runtime errors.
Migration ordering conflicts — Parallel development branches create conflicting migration numbers or dependency ordering. Use timestamp-based naming instead of sequential numbers to reduce conflicts.

Tool-Specific Quickstart

golang-migrate

# Install
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

# Create migration
migrate create -ext sql -dir db/migrations -seq create_users

# Run all pending migrations
migrate -path db/migrations -database "postgres://user:pass@localhost/mydb?sslmode=disable" up

# Rollback one step
migrate -path db/migrations -database "postgres://..." down 1

# Check current version
migrate -path db/migrations -database "postgres://..." version

# Force version (fix dirty state)
migrate -path db/migrations -database "postgres://..." force 3

Flyway

# Install (macOS)
brew install flyway

# Configuration (flyway.conf)
flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=postgres
flyway.password=secret
flyway.locations=filesystem:./sql

# Run migrations
flyway migrate

# Show migration status
flyway info

# Validate applied migrations match local files
flyway validate

# Clean database (DANGEROUS — dev only)
flyway clean

# Baseline existing database for Flyway adoption
flyway baseline

Prisma Migrate

# Install
npm install prisma --save-dev

# After editing prisma/schema.prisma models:

# Generate migration (development)
npx prisma migrate dev --name add_orders_table

# Deploy migrations (production)
npx prisma migrate deploy

# Check migration status
npx prisma migrate status

# Reset database (DANGEROUS — destroys all data)
npx prisma migrate reset

# Generate Prisma Client
npx prisma generate

Alembic

# Install
pip install alembic

# Initialize
alembic init alembic

# Auto-generate migration (based on SQLAlchemy model diff)
alembic revision --autogenerate -m "add orders table"

# Upgrade to latest
alembic upgrade head

# Rollback one step
alembic downgrade -1

# Check current version
alembic current

# View migration history
alembic history --verbose

Frequently Asked Questions

Q: How do I handle a failed migration?

A: First, check the actual database state (was it partially applied?). If you use a database with transactional DDL (PostgreSQL), failed migrations roll back automatically. MySQL DDL is not transactional, so you may need manual fixes. Most tools mark a "dirty" state — after fixing the issue, reset the version number (e.g., migrate force N).

Q: Should I use auto-generated or hand-written migrations?

A: It depends on your team and use case. Auto-generation (Prisma, Django, Alembic autogenerate) is great for rapid iteration but may miss data migration logic. Hand-written SQL gives precise control for performance-sensitive operations (indexes, large table changes). Recommended: auto-generate, then review and edit before committing.

Q: How do I handle data migrations (not just schema)?

A: Keep data migrations separate from schema migrations. First, apply the schema change (add new column), then use a dedicated script or migration step to backfill data. For large datasets, process in batches (1,000-10,000 rows each) to avoid long transactions and lock contention.

Q: How should I handle migrations in CI/CD?

A: Migrations should run before your application starts in the deployment pipeline. Recommended flow: in CI, validate against a temporary database (up → down → up); in CD, run migrate up first, then rolling-update application containers. In Kubernetes, use an init container to execute migrations before the app container starts.

Q: When should I use declarative vs versioned migrations?

A: Declarative tools (Atlas, Prisma) define the desired end state and auto-compute the diff — ideal for new projects and rapid prototyping. Versioned tools (Flyway, golang-migrate) give explicit control over every change — better for production systems requiring audit trails and precise control. Many teams use declarative during development and export to versioned migrations for production deployment.