Schema Migrations with Alembic

Learning outcomes

• Write non-blocking Alembic migration scripts
• Execute zero-downtime schema migrations in CI/CD

Mental model

Alembic provides a version-controlled database migration framework for SQLAlchemy, tracking schema revisions in a dedicated alembic_version table.

Theory

Production DDL statements (ALTER TABLE, ADD COLUMN) acquire table locks. If a migration waits for an exclusive lock behind long-running queries, subsequent SELECT queries queue up behind it, exhausting connection pools. Safe migrations configure strict lock_timeout values prior to DDL execution.

# Alembic env.py - Adding lock timeout safety
from alembic import context
from sqlalchemy import engine_from_config, pool

def run_migrations_online():
    connectable = context.get_engine()
    with connectable.connect() as connection:
        # Prevent DDL migrations from locking tables indefinitely
        connection.execute("SET lock_timeout = '5s';")
        connection.execute("SET statement_timeout = '60s';")
        
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True
        )
        with context.begin_transaction():
            context.run_migrations()

Alternatives and trade-offs

• Manual DDL SQL Scripts: Error-prone, hard to track in CI/CD, no automated rollback verification.
• Alembic Versioned Migrations: Audited, repeatable, auto-generates delta scripts from SQLAlchemy model changes.

Failure modes and misconceptions

1. Blocking Default Values: Adding a column with a non-constant default (ALTER TABLE users ADD COLUMN created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL) in PostgreSQL < 11 rewrote the entire table, holding an ACCESS EXCLUSIVE lock.
2. Dropping Columns in Single Deployment: Dropping a column while old application pods are still running causes HTTP 500 crashes. Always use multi-phase expand/contract migrations.

Decision scenario

Configure SET lock_timeout = '5s' in Alembic env.py so that production schema migration deployment scripts fail fast rather than blocking user queries if a lock cannot be acquired immediately.

Learning outcomes

• Structure version-controlled database migrations using Alembic.
• Enforce lock timeout safety to prevent DDL migration deadlocks.
• Execute zero-downtime expand/contract schema migrations.

Trade-offs

Alembic automates database schema evolution, but auto-generated revision scripts must be manually audited before applying DDL changes in production.

Primary sources

• FastAPI Framework Architecture & Dependency Injection Specification — Tiangolo, verified 2026-07-22