Alembic

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

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.