Database Migration

Database Migration Skill

Migration Lifecycle

1. Generate Migration

alembic revision --autogenerate -m "Add user preferences"

2. Review Generated Script

# File: alembic/versions/001_add_user_preferences.py

def upgrade():
    op.create_table('user_preferences',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('theme', sa.String(), nullable=True),
        sa.ForeignKeyConstraint(['user_id'], ['user.id']),
        sa.PrimaryKeyConstraint('id')
    )

def downgrade():
    op.drop_table('user_preferences')

3. Add Indexes

def upgrade():
    op.create_table(...)
    # Add indexes for performance
    op.create_index(
        'ix_user_preferences_user_id',
        'user_preferences',
        ['user_id']
    )

def downgrade():
    op.drop_index('ix_user_preferences_user_id')
    op.drop_table('user_preferences')

4. Test Locally

# Test upgrade
alembic upgrade head

# Verify data integrity
SELECT * FROM user_preferences;

# Test downgrade
alembic downgrade -1

# Verify
SELECT COUNT(*) FROM user_preferences;  # Should error

5. Test on Production-like Data

# Backup production
pg_dump production > backup.sql

# Load to staging
psql staging < backup.sql

# Run migration
alembic upgrade head

# Performance test
EXPLAIN ANALYZE SELECT * FROM user_preferences WHERE user_id = 123;

6. Deploy with Zero Downtime

# Strategy 1: Expand-Contract
# 1. Add new column (backward compatible)
# 2. Backfill data
# 3. Deploy code to use new column
# 4. Drop old column

# Strategy 2: Blue-Green
# 1. Deploy new schema to green DB
# 2. Sync data
# 3. Switch traffic to green
# 4. Keep blue as rollback option

Backward Compatibility Rules

• ✅ Add columns with defaults
• ✅ Add tables
• ✅ Add indexes
• ✅ Change column types (with care)
• ❌ Drop columns without deprecation
• ❌ Rename columns without alias
• ❌ Change constraints abruptly

Versioning

• Never edit old migrations
• Always create new migration
• Migrations are immutable
• Name clearly: 001_initial_schema.py

Rollback Procedure

ALWAYS test downgrade first:

alembic current  # See current version
alembic downgrade -1  # Go back 1 version
alembic upgrade +2  # Forward 2 versions