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.