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

bash

# 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:

ini

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

URL escaping: Special characters in passwords must be percent-encoded, then

doubled for ConfigParser interpolation:

python

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:

python

# 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:

python

# 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:

python

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

bash

# 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

python

"""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

python

# 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

bash

# 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

bash

# 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

ini

[post_write_hooks]
hooks = ruff

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

Or with

pyproject.toml

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

NPX Install

Tags

SKILL.md Content

Alembic Database Migrations

Environment Setup

Initialize the Migration Environment

Project Structure

Configure
`alembic.ini`

Enable Autogenerate in
`env.py`

Constraint Naming Conventions

Creating and Writing Migrations

Generate a Revision

Anatomy of a Revision File

Common Operations

Running Migrations

Inspecting State

Post-Write Code Formatting

Quick Reference

Key Best Practices

Additional Resources