Database Migrations Core Knowledge

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: migrations for comprehensive documentation.

Migration Fundamentals

What is a Migration?

A migration is a version-controlled change to your database schema or data.

Migration Types

Type Description Example

Schema DDL changes Add column, create index

Data DML changes Backfill data, transform values

Combined Both schema and data Add column with default, populate

Version Naming Conventions

Timestamp-based (Recommended)

V20240115103000__create_users_table.sql V20240115104500__add_email_index.sql V20240116090000__add_status_column.sql

Sequential

V001__create_users_table.sql V002__add_email_index.sql V003__add_status_column.sql

Semantic

V1.0.0__initial_schema.sql V1.1.0__add_orders_table.sql V1.1.1__fix_orders_constraint.sql

Migration File Structure

Flyway Format

-- V20240115103000__create_users_table.sql

CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

CREATE INDEX idx_users_email ON users(email);

With Rollback (Flyway Pro/Enterprise)

-- V20240115103000__create_users_table.sql CREATE TABLE users (...);

-- U20240115103000__create_users_table.sql (undo) DROP TABLE IF EXISTS users;

Liquibase Format

<!-- changelog.xml --> <databaseChangeLog> <changeSet id="1" author="dev"> <createTable tableName="users"> <column name="id" type="int" autoIncrement="true"> <constraints primaryKey="true"/> </column> <column name="email" type="varchar(255)"> <constraints nullable="false" unique="true"/> </column> </createTable> <rollback> <dropTable tableName="users"/> </rollback> </changeSet> </databaseChangeLog>

Migration Strategies

Expand-Contract Pattern

For backward-compatible changes:

Phase 1: EXPAND ├── Add new column (nullable or with default) ├── Add new table ├── Deploy new code that writes to both old and new └── Backfill existing data

Phase 2: CONTRACT ├── Remove old column usage from code ├── Make new column non-nullable if needed ├── Drop old column └── Deploy final code

Example - Renaming a column:

-- Phase 1: Expand ALTER TABLE users ADD COLUMN full_name VARCHAR(200); UPDATE users SET full_name = name; -- Deploy code that reads from both, writes to both

-- Phase 2: Contract (after verification) ALTER TABLE users DROP COLUMN name;

Blue-Green Deployment

┌─────────────┐ ┌─────────────┐ │ Blue │ │ Green │ │ (Current) │ │ (New) │ └──────┬──────┘ └──────┬──────┘ │ │ └───────┬───────────┘ │ ┌──────┴──────┐ │ Database │ │ (Shared) │ └─────────────┘

1. Green environment runs migrations
2. Test Green with new schema
3. Switch traffic Blue → Green
4. Blue becomes standby

Rolling Updates

1. Apply backward-compatible migration
2. Update servers one by one
3. Old code continues working
4. After all servers updated, remove old code paths
5. Apply cleanup migration

Zero-Downtime Patterns

Add Column (Safe)

-- Safe: Column added as nullable ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Safe: Column added with default ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- PostgreSQL 11+: Fast default ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT NOW();

Add Non-Nullable Column

-- Step 1: Add nullable ALTER TABLE users ADD COLUMN email_verified BOOLEAN;

-- Step 2: Backfill UPDATE users SET email_verified = FALSE WHERE email_verified IS NULL;

-- Step 3: Add constraint ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

Rename Column

-- Step 1: Add new column ALTER TABLE users ADD COLUMN full_name VARCHAR(200);

-- Step 2: Copy data UPDATE users SET full_name = name;

-- Step 3: Add trigger for sync (during transition) CREATE TRIGGER sync_name BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE FUNCTION sync_name_columns();

-- Step 4: Update application to use new column

-- Step 5: Remove old column ALTER TABLE users DROP COLUMN name; DROP TRIGGER sync_name ON users;

Add Index (Non-Blocking)

-- PostgreSQL: CONCURRENTLY CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- MySQL: ALGORITHM=INPLACE, LOCK=NONE ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE;

-- SQL Server: ONLINE CREATE INDEX idx_users_email ON users(email) WITH (ONLINE = ON);

Drop Column (Safe)

-- Step 1: Stop writing to column (application change) -- Step 2: Deploy application -- Step 3: Drop column ALTER TABLE users DROP COLUMN deprecated_field;

Rename Table

-- Step 1: Create view with old name pointing to new table ALTER TABLE orders RENAME TO order_records; CREATE VIEW orders AS SELECT * FROM order_records;

-- Step 2: Update application to use new name -- Step 3: Drop view DROP VIEW orders;

Data Migration Patterns

Batch Processing

-- Process in batches to avoid locking DO $$ DECLARE batch_size INT := 1000; affected INT := 1; BEGIN WHILE affected > 0 LOOP UPDATE users SET status = 'active' WHERE id IN ( SELECT id FROM users WHERE status IS NULL LIMIT batch_size FOR UPDATE SKIP LOCKED ); GET DIAGNOSTICS affected = ROW_COUNT; COMMIT; PERFORM pg_sleep(0.1); -- Small delay END LOOP; END $$;

Background Job Migration

Instead of SQL, use application code

def migrate_user_status(): batch_size = 1000 offset = 0

while True:
    users = User.query.filter(User.status == None) \
                     .limit(batch_size).all()
    if not users:
        break

    for user in users:
        user.status = calculate_status(user)

    db.session.commit()
    time.sleep(0.1)  # Rate limiting

ETL Pattern

-- 1. Create new table with desired structure CREATE TABLE users_new ( id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL, full_name VARCHAR(200) NOT NULL, -- Combined from first_name, last_name created_at TIMESTAMP DEFAULT NOW() );

-- 2. Copy and transform data INSERT INTO users_new (id, email, full_name, created_at) SELECT id, email, first_name || ' ' || last_name, created_at FROM users;

-- 3. Swap tables ALTER TABLE users RENAME TO users_old; ALTER TABLE users_new RENAME TO users;

-- 4. Update sequences SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

-- 5. Drop old table (after verification) DROP TABLE users_old;

Rollback Strategies

Immediate Rollback Script

-- migration.sql ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- rollback.sql ALTER TABLE users DROP COLUMN phone;

Point-in-Time Recovery

PostgreSQL

pg_restore --target-time="2024-01-15 10:00:00" -d mydb backup.dump

MySQL

mysqlbinlog --stop-datetime="2024-01-15 10:00:00" binlog.000001 | mysql

Forward-Fix (Preferred)

Instead of rollback, deploy a fix:

-- Original migration had bug -- V2: Create new migration to fix ALTER TABLE users ALTER COLUMN status SET DEFAULT 'pending'; -- Fix the default

Migration Testing

Pre-deployment Checklist

• Migration tested on copy of production data
• Rollback script tested
• Application compatible with both old and new schema
• Index creation time estimated
• Lock duration estimated
• Disk space requirements checked
• Backup taken before migration

Test Environment Setup

Create production copy

pg_dump production_db | psql test_db

Run migration

flyway -url=jdbc:postgresql://localhost/test_db migrate

Run application tests

npm test

Verify schema

pg_dump --schema-only test_db > schema.sql diff schema.sql expected_schema.sql

Best Practices

DO

• Use version control for migrations

• Test migrations on production-like data

• Make migrations idempotent when possible

• Document complex migrations

• Keep migrations small and focused

• Use expand-contract for breaking changes

• Create indexes concurrently

DON'T

• Mix schema and data changes

• Run migrations during peak hours

• Delete migration files

• Edit applied migrations

• Skip testing rollbacks

• Make assumptions about data

Common Pitfalls

Lock Contention

-- Problem: Long-running transaction holds lock BEGIN; ALTER TABLE users ADD COLUMN x INT; -- ... long running queries ... COMMIT;

-- Solution: Keep transaction short ALTER TABLE users ADD COLUMN x INT;

Missing Index

-- Problem: Query becomes slow after adding data ALTER TABLE users ADD COLUMN status VARCHAR(20);

-- Solution: Add index in same migration ALTER TABLE users ADD COLUMN status VARCHAR(20); CREATE INDEX CONCURRENTLY idx_users_status ON users(status);

Constraint Violations

-- Problem: Existing data violates new constraint ALTER TABLE users ADD CONSTRAINT chk_email CHECK (email LIKE '%@%'); -- Fails if bad data exists

-- Solution: Clean data first UPDATE users SET email = 'invalid@example.com' WHERE email NOT LIKE '%@%'; ALTER TABLE users ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');

When NOT to Use This Skill

• Flyway specifics - Use flyway skill for Flyway commands and patterns

• Prisma migrations - Use prisma skill for Prisma migrate

• TypeORM migrations - Use typeorm skill for TypeORM migrations

• Liquibase - Use specific Liquibase documentation

Anti-Patterns

Anti-Pattern Problem Solution

Mixing schema and data changes Hard to rollback Separate into different migrations

No rollback script Can't undo changes Always create undo migration

Long-running migrations Locks tables, downtime Use online DDL, batch processing

Editing applied migrations Version conflicts, checksum errors Create new migration

Missing backup Data loss risk Always backup before migrating

No testing on prod-like data Unexpected failures Test with production data copy

Quick Troubleshooting

Problem Diagnostic Fix

Migration fails mid-run Check transaction support Use smaller batches, manual fix

Lock timeout Check running queries Run during low traffic

Version conflicts Check migration history table Resolve conflicts, rebase

Checksum mismatch Compare file with history Repair or recreate migration

Out of disk space Check table sizes Clean old data first

Reference Documentation

• Migration Strategies

• Schema Versioning

• Rollback Patterns

• Data Migration