alembic

alembic — SKILL.md

Variant: standard · When to use: the skill is invoked, produces wired Alembic config + a reviewed migration script (or the relevant slice), and control returns to the caller.

Overview

This skill teaches an agent to run Alembic schema migrations on top of an existing SQLAlchemy 2.x data layer, across SQLite, PostgreSQL, and MySQL from one migration history. It assumes a working Base.metadata already exists (that is the sibling sqlalchemy skill's job) and wires Alembic to it. The primary path is sync — the standard run_migrations_online() shape on a sync connection — with a short async aside. Two pieces are load-bearing: (1) autogenerate drafts, you review — it has documented blind spots that cause silent data loss if applied blindly; and (2) the SQLite batch / move-and-copy gotcha — SQLite can't ALTER most things, so migrations are written in batch_alter_table style that recreates the table on SQLite while emitting ordinary ALTER on PG/MySQL. This skill also owns the create_all() vs Alembic decision and the baseline-stamp path for adopting Alembic on an already-created database.

When to activate

• ✅ Wiring Alembic (env.py + alembic.ini) to an existing SQLAlchemy Base.metadata so autogenerate can diff the live schema against your models.
• ✅ Authoring a migration — empty (revision) or drafted (revision --autogenerate) — and reviewing/editing it before applying.
• ✅ Running upgrade / downgrade, inspecting history / current / heads, or resolving multiple heads with merge.
• ✅ Writing ONE migration that must apply on SQLite, PostgreSQL, and MySQL — reaching for batch (move-and-copy) operations.
• ✅ Deciding Base.metadata.create_all() vs Alembic for a project, or stamping a baseline to adopt Alembic on a create_all'd database.

Do NOT activate when:

• Defining SQLAlchemy models / Engine / connection URLs / sessions → sqlalchemy. This skill only wires Alembic to a Base.metadata that already exists.
• Building a job queue / scheduler / lease loop on the migrated schema → sql-job-queue.
• Writing raw SQL DDL by hand with no version history — Alembic exists to give you that history; if you genuinely don't need it, see the create_all() decision in Step 6.

Workflow

Step 1: Initialize the migration environment

alembic init scaffolds the layout next to your project.

alembic init alembic        # creates alembic.ini + alembic/ (env.py, script.py.mako, versions/)

This gives you alembic.ini (config), alembic/env.py (the runtime hook), alembic/script.py.mako (the revision template), and alembic/versions/ (where revision files land). script_location in alembic.ini points at the alembic/ dir; version_locations (optional) lets you split revisions across multiple dirs.

Step 2: Source the DB URL from the environment (don't hardcode)

The scaffolded alembic.ini has a literal sqlalchemy.url = driver://.... Do not commit a real URL there. Either leave it blank and set it in env.py from the environment, or interpolate. Setting it in env.py keeps one source of truth shared with the app:

# alembic/env.py
import os
from alembic import context

config = context.config
config.set_main_option("sqlalchemy.url", os.environ["DB_URL"])

set_main_option overrides whatever is in alembic.ini at runtime, so the same migration history runs against SQLite locally and PostgreSQL/MySQL in other environments just by changing DB_URL.

Step 3: Point target_metadata at your models' Base.metadata

This is the link that makes --autogenerate work — it diffs the live DB against the MetaData you hand it. Import your models' Base (so every table is registered on its metadata) and assign:

# alembic/env.py (continued)
from myapp.models import Base   # your DeclarativeBase subclass — sqlalchemy sibling owns this

target_metadata = Base.metadata

Importing the module that defines the models is required — a table that is never imported is invisible to target_metadata, so autogenerate won't see it (a blind spot — Step 5).

Step 4: The env.py online runner (sync-first) with batch + naming-convention

The scaffold ships an offline and an online runner. Online uses a live connection (and is the common path); offline emits SQL to stdout/a file without a DB (--sql mode), for hand-applied or review-gated deploys. Edit the online runner to enable batch mode so SQLite migrations work (Step 7), and define a naming_convention so batch table-recreate can reference constraints by name:

# alembic/env.py (continued)
from sqlalchemy import engine_from_config, pool, 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",
}
# Best applied on Base.metadata itself in the models module so live + autogen agree;
# shown here for visibility. If set in models: MetaData(naming_convention=NAMING_CONVENTION)

def run_migrations_online() -> None:
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            render_as_batch=True,          # makes SQLite migrations move-and-copy (Step 7)
            compare_type=True,             # also diff column TYPE changes (still review them)
            compare_server_default=True,   # attempt server_default diffs (imperfect — Step 5)
        )
        with context.begin_transaction():
            context.run_migrations()

run_migrations_online()   # the scaffold guards this with context.is_offline_mode()

render_as_batch=True is the global switch that wraps emitted alters in batch mode. NullPool is the right pool for a short-lived migration process. (The scaffold's offline runner — run_migrations_offline() — calls context.configure(url=..., literal_binds=True) and emits SQL; keep it for --sql deploys.)

Step 5: Author a revision — autogenerate DRAFTS, you REVIEW

Two ways to create a revision:

alembic revision -m "add jobs table"              # EMPTY — you write upgrade()/downgrade() by hand
alembic revision --autogenerate -m "add jobs table"   # DRAFTS ops by diffing target_metadata vs DB

Autogenerate is a draft generator, not a source of truth. It compares target_metadata against the live DB and writes its best guess — but it has documented blind spots. Always open the generated file and review/edit it before applying. What autogenerate does NOT reliably detect:

• Table / column RENAMES — it sees a drop of the old + an add of the new. Apply that blindly and you lose the data. Rewrite it as op.rename_table(...) / a batch column rename by hand.
• Most server_default changes — even with compare_server_default=True, many are missed or mis-rendered.
• CHECK constraints and some named constraints — frequently not detected.
• Some index changes and some column-TYPE changes — compare_type=True helps but is not exhaustive; verify the rendered type.
• Anything not reachable from target_metadata — a model you forgot to import, or schema objects Alembic isn't told to compare (other schemas, certain object types). Out of sight, out of diff.

A reviewed autogenerated file looks like this — note the explicit, reversible downgrade():

"""add jobs table"""
from alembic import op
import sqlalchemy as sa

revision = "a1b2c3d4e5f6"
down_revision = None        # first migration; otherwise the prior revision id
branch_labels = None
depends_on = None

def upgrade() -> None:
    op.create_table(
        "jobs",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("name", sa.String(length=200), nullable=False),
        sa.Column("data", sa.JSON(), nullable=True),
    )
    op.create_index("ix_jobs_name", "jobs", ["name"])

def downgrade() -> None:
    op.drop_index("ix_jobs_name", table_name="jobs")
    op.drop_table("jobs")

Write downgrade() as the true inverse of upgrade() — reverse order, reverse each op. A migration with a wrong or pass downgrade can't be rolled back. See references/alembic-extras.md for a rename done safely and a data migration.

Step 6: Apply / roll back / inspect

alembic upgrade head        # apply every unapplied revision up to the latest
alembic upgrade +1          # apply the next one only
alembic downgrade -1        # roll back the most recent revision
alembic downgrade base      # roll back everything (empty schema)

alembic current             # which revision the DB is stamped at
alembic history             # the revision graph, newest first
alembic heads               # the current head(s) — more than one means a branch to merge

Step 7: Multi-dialect — write migrations in BATCH style

This is the load-bearing multi-dialect rule. SQLite cannot ALTER most schema elements — you cannot drop a column, alter a column's type/nullability, or add most constraints with a plain ALTER TABLE. Alembic's batch operations work around this with move-and-copy: create a new table with the target schema, INSERT ... SELECT the rows across, drop the old table, then rename the new one into place. On PostgreSQL and MySQL the exact same batch code emits ordinary ALTER statements — so writing every alter in batch style gives you ONE migration script that applies on all three dialects.

def upgrade() -> None:
    # Same code path on all three; SQLite does move-and-copy, PG/MySQL do ALTER.
    with op.batch_alter_table("jobs") as batch_op:
        batch_op.add_column(sa.Column("priority", sa.Integer(), nullable=False, server_default="0"))
        batch_op.alter_column("name", existing_type=sa.String(200), nullable=True)
        batch_op.create_unique_constraint("uq_jobs_name", ["name"])

def downgrade() -> None:
    with op.batch_alter_table("jobs") as batch_op:
        batch_op.drop_constraint("uq_jobs_name", type_="unique")
        batch_op.alter_column("name", existing_type=sa.String(200), nullable=False)
        batch_op.drop_column("priority")

render_as_batch=True (Step 4) makes autogenerate emit batch_alter_table blocks for you. The naming_convention (Step 4) is what lets the SQLite move-and-copy recreate the table with its constraints intact — without named constraints, the recreate can drop unnamed ones. When reviewing the emitted SQL, expect a CREATE TABLE _alembic_tmp_... / INSERT ... SELECT / DROP / ALTER ... RENAME sequence on SQLite and a single ALTER TABLE on PG/MySQL. See references/alembic-extras.md for batch_alter_table(..., copy_from=...) when reflection isn't enough and for the recreate-mode controls.

Step 8: Branching — resolve multiple heads with merge

When two people (or two feature branches) each add a revision off the same parent, alembic heads shows two heads and upgrade head errors (ambiguous). Stitch them with a merge revision — it has both as parents and applies no schema change itself:

alembic heads                              # shows e.g. two head ids: rev_a, rev_b
alembic merge -m "merge a and b" rev_a rev_b
alembic upgrade head                       # now unambiguous

Step 9: Async note (short)

For an async SQLAlchemy stack, migrations are still commonly run sync — the simplest path is to point Alembic at a sync driver URL and use the Step 4 runner unchanged. If you must drive an async engine, env.py's online runner becomes:

import asyncio
from sqlalchemy.ext.asyncio import async_engine_from_config

def do_run_migrations(connection):
    context.configure(connection=connection, target_metadata=target_metadata, render_as_batch=True)
    with context.begin_transaction():
        context.run_migrations()

async def run_async_migrations():
    connectable = async_engine_from_config(
        config.get_section(config.config_ini_section, {}), prefix="sqlalchemy.", poolclass=pool.NullPool
    )
    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)   # greenlet bridge to Alembic's sync context
    await connectable.dispose()

asyncio.run(run_async_migrations())

run_sync(do_run_migrations) hops from the async connection into Alembic's sync migration context via the greenlet bridge. That's the only async-specific piece; everything else (revisions, batch, review) is identical.

Rules

Hard rules (never violate):

• Autogenerate drafts; you review and edit every generated script before applying. Never upgrade an unreviewed autogenerated revision — renames and constraint changes will silently lose data.
• Write migrations in batch style when SQLite is a target. render_as_batch=True + op.batch_alter_table(...) for any alter that SQLite can't do as a plain ALTER. The same code stays correct on PG/MySQL.
• Set target_metadata = Base.metadata and import the models so autogenerate can see every table. An un-imported table is invisible.
• Source the DB URL from the environment, not a committed alembic.ini literal — one history, many dialects/environments.
• Every upgrade() has a true-inverse downgrade(). No pass downgrades on a reversible op.
• Use a naming_convention so batch move-and-copy on SQLite can reference and preserve named constraints.

Preferences (override-able):

• Prefer the sync runner; reach for the async run_async_migrations shape only when a sync driver URL genuinely isn't available.
• Prefer --autogenerate to seed a revision, then hand-edit — over writing ops from scratch — except for pure data migrations (write those by hand).
• Keep compare_type=True / compare_server_default=True on for better diffs, but treat their output as a draft, not a guarantee.
• Use NullPool for the migration process — it's short-lived and shouldn't hold a pool.

Gotchas

• Autogenerate sees a rename as drop+add → data loss. The single highest-value review check. If a column/table was renamed, the generated drop_column+add_column (or drop_table+create_table) destroys the data. Rewrite as op.rename_table(...) or a batch alter_column(..., new_column_name=...).
• A new model that isn't imported produces an empty autogenerate. Autogenerate "sees nothing to do" because the table never registered on target_metadata. Import the model module in (or before) env.py.
• SQLite ALTER failures without batch. add_column with some constraints, any drop_column/alter_column, most add_constraint raise on SQLite if not wrapped in batch_alter_table. The error often surfaces only when the migration actually runs on SQLite, not on the PG you developed against. Turn on render_as_batch=True from the start.
• Unnamed constraints vanish on SQLite batch recreate. The move-and-copy recreate rebuilds the table; constraints Alembic can't name (no naming_convention) may not survive. Define the convention before you have data to lose.
• alembic upgrade head errors with "multiple heads". Two unmerged branches. alembic heads to see them, alembic merge to join them (Step 8). Not a corruption — just an unresolved branch.
• server_default diffs are unreliable. Even with compare_server_default=True, a changed default is often missed or rendered wrong. Verify defaults by hand; don't trust autogenerate here.
• downgrade base wipes the schema. It runs every downgrade() to empty. Fine in dev, destructive in prod — know which DB DB_URL points at before running it.

Anti-patterns

• Don't apply an autogenerated migration without reading it because "autogenerate is usually right" — its blind spots (renames, server defaults, CHECK/named constraints) are exactly the data-destroying cases.
• Don't write a plain op.alter_column / op.drop_column and assume it'll run everywhere "because it works on Postgres" — it raises on SQLite. Use batch_alter_table.
• Don't ship Base.metadata.create_all() as your migration story because "it's simpler" — it has no version history, no incremental change, no downgrade. See the decision below; once a schema is shipped, it's Alembic.
• Don't hardcode the production URL in alembic.ini because "it's just config" — it leaks a credential and pins one dialect. Source it from the environment.
• Don't leave downgrade() as pass because "we'll never roll back" — a non-reversible migration blocks every later rollback through it.
• Don't hand-merge two heads by editing down_revision — use alembic merge, which records both parents correctly.

create_all() vs Alembic — the decision (this skill owns it)

Base.metadata.create_all(engine) emits CREATE TABLE for the current model state in one shot. It is the right call only for: greenfield / not-yet-shipped schemas, throwaway databases, and test/dev bootstrap (the sqlalchemy sibling uses it exactly there). Its limits are fatal for anything shipped: no version history, no incremental change, no downgrade — it can only create from scratch, never evolve.

Reach for Alembic the moment the schema is shipped, runs in multiple environments, or evolves over time (a DAG of changes a team applies in order). That's the whole point of a migration history.

Adopting Alembic on a database that was built with create_all() — don't re-run the creation. Instead:

1. Initialize Alembic (Steps 1–4) and author a baseline migration whose upgrade() creates the current schema (autogenerate against an empty DB, or hand-write it to match what create_all produced).
2. On the existing create_all'd DB, run alembic stamp head — this records the DB as already at the baseline revision without running it (no CREATE TABLE re-executed).
3. From then on, every change is a new revision applied with upgrade.

alembic stamp <rev> writes the alembic_version row to mark a revision as applied without executing its body — the bridge from an unmanaged create_all'd DB to a managed history.

Output

Wired Alembic configuration (alembic.ini + an env.py whose target_metadata = Base.metadata, DB URL sourced from the environment, render_as_batch=True, and a naming_convention) plus reviewed, reversible revision files under versions/ — written in batch style so one history applies on SQLite, PostgreSQL, and MySQL. The artifact is consumed by the next layer: a deploy step that runs alembic upgrade head, or a higher-level pattern (e.g. sql-job-queue) that builds on the now-migrated schema.

Related

• sqlalchemy — the data layer this sits on top of: the DeclarativeBase/Mapped models, Engine/URL, sessions, and the cross-dialect ORM gotchas. This skill assumes its Base.metadata exists and only wires Alembic to it; sqlalchemy uses create_all() for dev/test bootstrap and defers the managed-change decision here.
• sql-job-queue — job-store/scheduler patterns (ready-set query, atomic lease, fair-share) built on a migrated schema. This skill produces the schema history; it does not teach the queue.
• pydantic-v2 — app-boundary validation (not DB schema or migrations).

Progressive disclosure

• references/alembic-extras.md — fuller worked migrations: a safe rename (preserving data), a data migration via op.bulk_insert / op.execute, batch_alter_table(..., copy_from=..., recreate=...) for when reflection isn't enough, offline --sql mode, and include_object/include_name to scope autogenerate. Load when the in-body snippet isn't enough for a specific migration shape.
• references/sources.md — research provenance for this skill. Load when verifying a claim's origin or during a fresh review.

Body budget

• description ≤ 1,024 chars.
• Body ≤ ~500 lines / 5,000 tokens.
• Per reference file: warn >10k tokens, error >25k. Total references: warn >25k tokens, error >50k.