Schema changes are scary. Here's how to make them routine.

The Golden Rule

Never make breaking changes in one step.

Breaking: Drop column, rename column, change type Safe: Add column, add index, add table

Breaking changes require multiple deployments.

Alembic Basics

# Initialize
alembic init migrations
 
# Create migration
alembic revision --autogenerate -m "add users table"
 
# Apply migrations
alembic upgrade head
 
# Rollback one step
alembic downgrade -1

Safe Migration Patterns

Adding a Column

def upgrade():
    op.add_column("users", sa.Column("email", sa.String(255), nullable=True))
 
def downgrade():
    op.drop_column("users", "email")

Safe because existing rows get NULL.

Adding a Column with Default

def upgrade():
    # Add nullable first
    op.add_column("users", sa.Column("status", sa.String(20), nullable=True))
    # Backfill
    op.execute("UPDATE users SET status = 'active' WHERE status IS NULL")
    # Then make not-null
    op.alter_column("users", "status", nullable=False)

Three steps: add nullable, backfill, constrain.

Renaming a Column

Don't rename in one step. Instead:

# Migration 1: Add new column
def upgrade():
    op.add_column("users", sa.Column("full_name", sa.String(255)))
    op.execute("UPDATE users SET full_name = name")
 
# Deploy code that reads from both columns
 
# Migration 2: Drop old column (after code no longer uses it)
def upgrade():
    op.drop_column("users", "name")

Changing Column Type

# Migration 1: Add new column with new type
def upgrade():
    op.add_column("orders", sa.Column("total_cents", sa.BigInteger()))
    op.execute("UPDATE orders SET total_cents = total_dollars * 100")
 
# Deploy code that writes to both, reads from new
 
# Migration 2: Drop old column
def upgrade():
    op.drop_column("orders", "total_dollars")

Zero-Downtime Checklist

Before deploying:

  • Migration runs in < 1 minute
  • No table locks on large tables
  • Backward compatible with current code
  • Rollback tested

The sequence:

  1. Deploy migration (schema change)
  2. Deploy code (uses new schema)
  3. Clean up old columns (optional later migration)

Handling Large Tables

Adding an index on a big table locks it:

# Bad: locks table
op.create_index("ix_orders_user_id", "orders", ["user_id"])
 
# Good: concurrent index (PostgreSQL)
op.execute("CREATE INDEX CONCURRENTLY ix_orders_user_id ON orders (user_id)")

Backfilling millions of rows:

# Bad: one huge update
op.execute("UPDATE orders SET status = 'pending' WHERE status IS NULL")
 
# Good: batched updates
connection = op.get_bind()
while True:
    result = connection.execute(text("""
        UPDATE orders SET status = 'pending'
        WHERE id IN (SELECT id FROM orders WHERE status IS NULL LIMIT 1000)
    """))
    if result.rowcount == 0:
        break

Testing Migrations

def test_migration_upgrade():
    # Start with clean DB
    alembic.command.upgrade(config, "head")
    
    # Verify schema
    inspector = inspect(engine)
    columns = [c["name"] for c in inspector.get_columns("users")]
    assert "email" in columns
 
def test_migration_downgrade():
    alembic.command.upgrade(config, "head")
    alembic.command.downgrade(config, "-1")
    
    # Verify rollback
    inspector = inspect(engine)
    columns = [c["name"] for c in inspector.get_columns("users")]
    assert "email" not in columns

My Workflow

  1. Write migration with alembic revision --autogenerate
  2. Review generated SQL — autogenerate misses things
  3. Test locally — up and down
  4. Test in staging — with production-like data
  5. Deploy to production — during low traffic
  6. Monitor — watch for errors

Common Mistakes

Running migrations during deployment: Separate migration from code deploy. Run migrations first, verify, then deploy code.

Not testing rollback: Always write and test the downgrade() function.

Changing too much at once: One logical change per migration. Easy to debug, easy to rollback.

Forgetting about NULL: New columns should be nullable or have defaults. Existing rows need values.

The Philosophy

Migrations should be boring. Small, incremental, reversible changes that nobody notices. If a migration feels risky, break it into smaller steps.

Your database is the foundation. Treat it carefully.

React to this post: