Alembic Database Migrations

Alembic is the migration tool for SQLAlchemy. It manages schema versioning through a directory of revision scripts linked by down_revision pointers, forming a linear (or branched) migration chain.

Environment Setup

Initialize the Migration Environment

# Generic single-database (most common)
alembic init alembic

# pyproject.toml-integrated (modern projects)
alembic init --template pyproject alembic

# Async DBAPI support
alembic init --template async alembic

Use pyproject template for modern Python projects that already have a pyproject.toml. It separates source-code config (in pyproject.toml) from deployment config (database URL, logging in alembic.ini).

Project Structure

yourproject/
├── alembic.ini          # DB URL, logging, deployment config
├── pyproject.toml       # Source/code config (pyproject template)
└── alembic/
    ├── env.py           # Migration runner — customize here
    ├── script.py.mako   # Template for new revision files
    └── versions/
        ├── 3512b954651e_add_account.py
        └── ae1027a6acf_add_column.py

Configure `alembic.ini`

Set the database URL:

sqlalchemy.url = postgresql+psycopg2://user:pass@localhost/mydb

URL escaping: Special characters in passwords must be percent-encoded, then % doubled for ConfigParser interpolation:

import urllib.parse
from sqlalchemy import URL
url = URL.create("postgresql+psycopg2", username="scott", password="P@ss%rd", host="localhost")
# Renders as: postgresql+psycopg2://scott:P%40ss%25rd@localhost
# In alembic.ini: postgresql+psycopg2://scott:P%%40ss%%25rd@localhost

Never hard-code production credentials. Read the URL from an environment variable in env.py instead:

# env.py
import os
config.set_main_option("sqlalchemy.url", os.environ["DATABASE_URL"])

Enable Autogenerate in `env.py`

Link the application's SQLAlchemy MetaData so Alembic can diff schema:

# env.py — replace the None assignment
from myapp.models import Base
target_metadata = Base.metadata

Constraint Naming Conventions

Always configure a naming convention. Autogenerate cannot detect anonymously named constraints, and databases use incompatible auto-naming schemes (PostgreSQL vs Oracle differ significantly).

Set this on the MetaData used by the declarative base:

from sqlalchemy import MetaData
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention={
        "ix": "ix_%(column_0_label)s",
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_`%(constraint_name)s`",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s",
    })

This allows unique=True and index=True column flags to produce consistently named constraints across databases, and allows autogenerate to detect and drop them reliably.

Creating and Writing Migrations

Generate a Revision

# Manual (empty script)
alembic revision -m "add account table"

# Autogenerate from model diff
alembic revision --autogenerate -m "add account table"

Always review autogenerated scripts before running them. Autogenerate cannot detect: table renames, column renames, or anonymously named constraints.

Anatomy of a Revision File

"""add account table

Revision ID: 1975ea83b712
Revises: <previous_rev_id or None>
Create Date: 2024-01-15 10:30:00
"""

revision = '1975ea83b712'
down_revision = None   # None = first migration
branch_labels = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'account',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
        sa.Column('created_at', sa.DateTime, nullable=False),
    )

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

Always implement downgrade(). It enables rollback and is required for alembic downgrade.

Common Operations

# Add a column
op.add_column('account', sa.Column('email', sa.String(255)))

# Drop a column
op.drop_column('account', 'email')

# Add named foreign key (naming convention resolves the name)
op.create_foreign_key(
    None, 'order', 'account',
    ['account_id'], ['id']
)

# Drop constraint — use op.f() to bypass naming convention tokenization
op.drop_constraint(op.f('fk_order_account_id_account'), 'order', type_='foreignkey')

# Create index
op.create_index('ix_account_name', 'account', ['name'])

Running Migrations

# Upgrade to latest
alembic upgrade head

# Upgrade to specific revision (partial ID works)
alembic upgrade ae10

# Upgrade N steps forward
alembic upgrade +2

# Downgrade to base (undo all)
alembic downgrade base

# Downgrade N steps backward
alembic downgrade -1

Inspecting State

# Show current DB revision
alembic current

# Show full history
alembic history --verbose

# Show history range
alembic history -r1975ea:ae1027

# Check if new migrations needed (CI-friendly)
alembic check

Use alembic check in CI pipelines to assert that all model changes have a corresponding migration committed.

Post-Write Code Formatting

Configure auto-formatting of generated revision files in alembic.ini:

[post_write_hooks]
hooks = ruff

ruff.type = exec
ruff.executable = ruff
ruff.options = check --fix REVISION_SCRIPT_FILENAME

Or with pyproject.toml:

[[tool.alembic.post_write_hooks]]
name = "ruff"
type = "exec"
executable = "ruff"
options = "check --fix REVISION_SCRIPT_FILENAME"

Quick Reference

Command	Description
`alembic init alembic`	Initialize migration environment
`alembic revision -m "..."`	Create empty revision
`alembic revision --autogenerate -m "..."`	Generate revision from model diff
`alembic upgrade head`	Apply all pending migrations
`alembic downgrade base`	Roll back all migrations
`alembic current`	Show current DB revision
`alembic history --verbose`	List all revisions
`alembic check`	Assert no pending migrations (CI)

Key Best Practices

Configure naming conventions on MetaData before creating any migrations.
Always review autogenerated scripts — renames are detected as add+drop pairs.
Always implement downgrade() for every revision.
Never hard-code database URLs; read from environment variables.
Run alembic check in CI to catch missing migrations early.
Use pyproject template for modern projects to keep source config separate from deployment config.
Use op.f() when explicitly naming constraints in drop operations to bypass naming convention tokenization.

Additional Resources

references/env-configuration.md — Deep dive on env.py customization, async support, multiple databases, and include_name/include_object hooks.
references/autogenerate-guide.md — What autogenerate detects and does not detect, type comparison, custom hooks, and alembic check in CI.

alembic