# Alembic

> This skill should be used when the user asks to "set up Alembic migrations", "create a database migration", "run alembic upgrade", "configure alembic autogenerate", or needs guidance on SQLAlchemy schema versioning and migration best practices.

- Skill: `the-perfect-developer/alembic` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx skillmds@latest add the-perfect-developer/alembic`
- Raw SKILL.md: https://api.skillmd.com/api/skills/the-perfect-developer/alembic/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: the-perfect-developer (https://skillmd.com/u/the-perfect-developer)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/the-perfect-developer/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

```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.