Alembic Migration Deep Dive

Master database schema changes with Alembic. Learn to upgrade, downgrade, and debug migrations.

🎯 The Mission

Database migrations are one of the riskiest parts of deployment. A bad migration can take down production. A missing downgrade can block rollbacks.

This exercise builds muscle memory for safe schema changes. You'll create migrations, test rollbacks, and handle common pitfalls.

Alembic Quick Reference

Command	What it does
`alembic revision --autogenerate -m "msg"`	Generate migration from model changes
`alembic revision -m "msg"`	Create empty migration (manual edits)
`alembic upgrade head`	Apply all pending migrations
`alembic downgrade -1`	Rollback one migration
`alembic history`	Show migration history
`alembic current`	Show current database revision
`alembic heads`	Show latest revision(s)

What You'll Learn

Auto-generation: How Alembic detects model changes
Manual migrations: When auto-generate fails
Downgrade logic: Writing safe rollbacks
Index management: Adding indexes without downtime
Data migrations: Transforming existing data

Exercise 1: Add an Index

Add a performance index to an existing column.

Scenario

The finding table gets slow when filtering by severity. Add an index.

Step 1: Create the Migration

# Create an empty migration (we'll write it manually)
alembic revision -m "add_index_finding_severity"

Step 2: Edit the Migration

"""add_index_finding_severity

Revision ID: abc123
Revises: previous_revision
Create Date: 2025-01-27
"""
from alembic import op

# revision identifiers
revision = 'abc123'
down_revision = 'previous_revision'

def upgrade() -> None:
    # CONCURRENTLY prevents table locks in production
    # Note: CONCURRENTLY cannot run inside a transaction
    op.execute(
        "CREATE INDEX CONCURRENTLY IF NOT EXISTS "
        "ix_finding_severity ON finding (severity)"
    )

def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_finding_severity")

⚠️ Production Safety: For large tables, always use CREATE INDEX CONCURRENTLY to avoid locking the table during index creation. This requires running outside a transaction.

Exercise 2: Add a Column with Default

Add a new column to an existing table with existing data.

Scenario

Add a priority column to the lead table. Existing leads should default to 'medium'.

def upgrade() -> None:
    op.add_column(
        'lead',
        sa.Column(
            'priority',
            sa.String(20),
            nullable=False,
            server_default='medium'
        )
    )

def downgrade() -> None:
    op.drop_column('lead', 'priority')

Exercise 3: Data Migration

Transform existing data as part of a migration.

Scenario

Rename severity values: 'info' → 'informational'

def upgrade() -> None:
    # Use raw SQL for data migrations
    op.execute(
        "UPDATE finding SET severity = 'informational' "
        "WHERE severity = 'info'"
    )

def downgrade() -> None:
    op.execute(
        "UPDATE finding SET severity = 'info' "
        "WHERE severity = 'informational'"
    )

⚠️ Data Migration Safety: Always test data migrations on a copy of production data. Large updates should be batched to avoid long-running transactions.

Exercise 4: Verify Migrations

Ensure your migration is safe and complete.

# 1. Apply the migration
alembic upgrade head

# 2. Verify the change
# (connect to DB and check schema)

# 3. Test the rollback
alembic downgrade -1

# 4. Verify the rollback
# (schema should be back to original)

# 5. Re-apply for final state
alembic upgrade head

Common Pitfalls

Missing downgrade: Always implement downgrade, even if you "won't need it"
Implicit locks: Adding NOT NULL or changing types can lock tables
Auto-generate misses: Check names, constraints, and column order manually
Branching conflicts: Multiple developers creating migrations simultaneously

✓ Success Criteria

Created at least 2 migrations (index + column or data)
All migrations have working downgrade logic
alembic upgrade head succeeds from clean database
alembic downgrade -1 succeeds for each migration
Can explain when to use CONCURRENTLY for indexes

📋 Progress Checklist

Review existing migrations in platform/migrations/

Create index migration with CONCURRENTLY

Test upgrade and downgrade for index migration

Create add-column migration with default

Test upgrade and downgrade for column migration

Create a data migration (optional)

Run alembic history to see migration chain

Downgrade to base and upgrade to head (full cycle)

Stretch Goals

Handle a merge conflict: Create two branches, then merge them
Create a multi-step migration: Add column → backfill → add constraint
Write a batched data migration for a large table

← Previous: New Table Next: Event Processing →