Alembic

Overview

Alembic is the migration tool for SQLAlchemy. It records schema changes as versioned Python scripts, each pointing at its parent revision, and stores the current revision in an alembic_version table. It can autogenerate scripts by comparing your models with the live database, run data migrations, handle branches, and emit plain SQL for review. Checked against Alembic 1.20 (September 2026), which requires Python 3.10+.

Instructions

Step 1: Setup

pip install alembic
alembic list_templates                 # generic, async, multidb, pyproject, pyproject_async
alembic init migrations                # sync driver (psycopg, pymysql, sqlite)
alembic init -t async migrations       # async driver (asyncpg, aiosqlite, aiomysql)

Set the database URL in alembic.ini (sqlalchemy.url) or override it in env.py from an environment variable such as DATABASE_URL; do not commit passwords. Then point env.py at your models:

# migrations/env.py (edit the generated file, do not replace it)
from app.models import Base          # your DeclarativeBase subclass
target_metadata = Base.metadata

The async template already contains the async engine wiring and the offline mode branch (needed for --sql). Async drivers need pip install "sqlalchemy[asyncio]" asyncpg (or aiosqlite); without greenlet, env.py fails with an ImportError. Hand-written env.py files that always call run_migrations_online() break --sql, so start from the template.

Step 2: Create migrations

alembic revision --autogenerate -m "add projects table"   # diff models vs database
alembic revision -m "backfill user roles"                 # empty script for data work
alembic check                                             # fails if models differ from the migrations

Generated files are named <hash>_<slug>.py and look like this (autogenerate output, adjusted by hand):

def upgrade() -> None:
    op.create_table(
        "projects",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("name", sa.String(100), nullable=False),
        sa.Column("owner_id", sa.Integer(), sa.ForeignKey("users.id"), nullable=False),
    )
    op.create_index(op.f("ix_projects_owner_id"), "projects", ["owner_id"])

def downgrade() -> None:
    op.drop_index(op.f("ix_projects_owner_id"), table_name="projects")
    op.drop_table("projects")

Pass table_name to op.drop_index; MySQL requires it.

Step 3: Data migrations

def upgrade() -> None:
    op.add_column("users", sa.Column("role", sa.String(20), server_default="member", nullable=False))
    op.execute(sa.text("UPDATE users SET role = 'admin' WHERE email LIKE '%@mycompany.com'"))

def downgrade() -> None:
    op.drop_column("users", "role")

op.execute() also works in --sql mode, while op.get_bind() results do not.

Step 4: Commands

alembic upgrade head          # apply all pending migrations
alembic upgrade +1            # apply the next one only
alembic downgrade -1          # roll back one
alembic current               # revision the database is at
alembic history --verbose     # full chain
alembic heads                 # more than one head means branches to merge
alembic merge -m "merge heads" a1b2c3d4e5f6 9f8e7d6c5b4a
alembic stamp head            # mark the database as current without running anything
alembic upgrade head --sql > upgrade.sql   # SQL for a DBA to review

Examples

Example 1: "Add a tasks table and ship it"

alembic revision --autogenerate -m "add tasks table"
# review migrations/versions/3f1c9a7b2d10_add_tasks_table.py
alembic upgrade head
alembic current

Result: the script creates tasks, alembic current prints 3f1c9a7b2d10 (head), and a later alembic check reports no new operations.

Example 2: "Preview the production SQL before deploying"

export DATABASE_URL="postgresql+asyncpg://app:${DB_PASSWORD}@db.internal:5432/orders"
alembic upgrade head --sql > release-42.sql

Result: release-42.sql holds the DDL for every pending revision, including the statements that update alembic_version; nothing touches the database.

Guidelines

• Always read autogenerated scripts: renames show up as drop plus create (data loss), and server defaults, enum changes and some constraint changes can be missed.
• Run alembic upgrade head against a scratch database in CI, and alembic check to catch models changed without a migration.
• Make downgrades real or delete them deliberately; untested downgrades give false safety.
• Use op.batch_alter_table() on SQLite, which cannot alter most constraints in place.
• Never edit a migration that has been applied elsewhere; add a new one.
• Two developers creating revisions in parallel produce two heads; resolve with alembic merge.
• Large tables: avoid locking changes in one step (add the column nullable, backfill in batches, then add the constraint).