---
name: alembic
description: Alembic — database migration tool for SQLAlchemy. Use when creating/running migrations, autogenerate, batch (SQLite) operations, branching, naming conventions, offline SQL generation, programmatic command invocation, or extending Alembic with custom operations and plugins.
version: 2.0.0
---

# Alembic Skill

Alembic is a lightweight database migration tool for use with SQLAlchemy. Use this skill when creating/running migrations, configuring autogenerate, performing batch (SQLite) operations, managing branches/merges, applying naming conventions, generating offline SQL, invoking commands programmatically, or extending Alembic with custom operations and plugins.

## When to Use This Skill

Use this skill when you need to:
- Set up a new Alembic migration environment (`alembic init`)
- Create, edit, run, or revert migration scripts (`revision`, `upgrade`, `downgrade`)
- Configure autogenerate to detect schema changes against SQLAlchemy models
- Run "batch" migrations for SQLite (the "move and copy" workflow)
- Manage multiple heads, branches, merges, and multi-base configurations
- Apply constraint naming conventions to make migrations portable
- Generate offline SQL scripts (`--sql` mode) for restricted DDL environments
- Invoke Alembic commands programmatically from Python
- Extend Alembic with custom operations, autogenerate comparators, or plugins

## Core Concepts

- **Migration Environment**: A directory (typically `alembic/`) containing `env.py`, `script.py.mako`, and a `versions/` folder. Created once via `alembic init`.
- **`env.py`**: Python script run on every command invocation; configures connectivity, `target_metadata`, and behavioral options via `context.configure(...)`.
- **Revision**: A migration file with `revision`, `down_revision`, `upgrade()`, and `downgrade()`. Ordering is determined by `down_revision` links forming a directed acyclic graph.
- **`op` / Operations**: The directive interface (`alembic.op`) used inside `upgrade()`/`downgrade()`.
- **`context` / MigrationContext**: The runtime facade providing database access and configuration.
- **`alembic_version` table**: Tracks the current applied revision(s); supports multiple rows when multiple heads exist.

## Quick Reference

### Common CLI Commands

```bash
alembic init alembic                          # create environment (generic template)
alembic init --template pyproject alembic     # pyproject.toml-based config
alembic list_templates                        # generic, pyproject, async, multidb

alembic revision -m "create account table"    # blank revision
alembic revision --autogenerate -m "msg"      # autogenerated revision
alembic upgrade head                          # apply to latest
alembic upgrade ae1027a6acf                   # apply to specific (partial id ok)
alembic upgrade +2                            # relative upgrade
alembic downgrade -1                           # relative downgrade
alembic downgrade base                        # revert all

alembic current --verbose                     # show current revision(s)
alembic current --check-heads                 # nonzero exit if not at head
alembic history -r-3:current --verbose        # slice of history
alembic heads                                 # show head revisions
alembic branches --verbose                    # show branch points
alembic show <rev>                            # show a revision

alembic merge -m "merge" heads                # merge multiple heads
alembic stamp head                            # set version without running migrations
alembic check                                 # CI: detect pending autogenerate ops
alembic upgrade ae1027a6acf --sql > out.sql   # offline SQL generation
```

### High-Signal Operation Examples

**Add a column:**
```python
from alembic import op
from sqlalchemy import Column, String

op.add_column("organization", Column("name", String()))
```

**Add a column with a foreign key:**
```python
from alembic import op
from sqlalchemy import Column, INTEGER, ForeignKey

op.add_column(
    "organization",
    Column("account_id", INTEGER, ForeignKey("accounts.id")),
)
```

**Create a table (server-side default uses `server_default`, not `default`):**
```python
op.create_table(
    "account",
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("name", sa.String(50), nullable=False),
    sa.Column("description", sa.String(200)),
)
```

**Alter a column (specify `existing_*` for MySQL compatibility):**
```python
op.alter_column(
    "account", "name",
    type_=sa.String(100),
    existing_type=sa.String(50),
    existing_nullable=False,
)
```

**Bulk insert (data migration):**
```python
my_table = sa.table("my_table", sa.column("data", sa.String))
op.bulk_insert(my_table, [{"data": "a"}, {"data": "b"}])
```

**Execute raw SQL (offline-safe; use `inline_literal` for parameters):**
```python
op.execute("UPDATE foo SET bar = 1 WHERE bar IS NULL")
```

**Custom operation (extension API):**
```python
def upgrade():
    op.create_sequence("my_sequence")

def downgrade():
    op.drop_sequence("my_sequence")
```

### Autogenerate Setup (in `env.py`)

```python
from myapp.mymodel import Base
target_metadata = Base.metadata  # passed to context.configure(target_metadata=...)
```

```python
def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section), prefix="sqlalchemy."
    )
    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()
```

### Programmatic Command Invocation

```python
from alembic.config import Config
from alembic import command

alembic_cfg = Config("/path/to/yourapp/alembic.ini")
command.upgrade(alembic_cfg, "head")
```

**Share a connection across commands** (via `Config.attributes`; `env.py` must consume it):
```python
with engine.begin() as connection:
    alembic_cfg.attributes["connection"] = connection
    command.upgrade(alembic_cfg, "head")
```

### Read configuration from `env.py`

```python
from alembic import context
some_param = context.config.get_main_option("my option")
```

## Key Usage Notes

### Transaction demarcation in `env.py`
`context.begin_transaction()` enclose a series of migrations. It "does the right thing" across offline/online and transactional-DDL settings:
```python
with connectable.connect() as connection:
    context.configure(connection=connection, target_metadata=target_metadata)
    with context.begin_transaction():
        context.run_migrations()
```

### Migration environment layout
```
yourproject/
    alembic.ini
    pyproject.toml
    alembic/
        env.py
        README
        script.py.mako
        versions/
            3512b954651e_add_account.py
            2b1ae634e5cd_add_order_id.py
```

### `file_template` and subdirectory organization
The `file_template` may include directory separators to organize migrations; requires `recursive_version_locations = true`:
```ini
file_template = %%(year)d/%%(month).2d/%%(day).2d_%%(hour).2d%%(minute).2d_%%(second).2d_%%(rev)s_%%(slug)s
recursive_version_locations = true
```
Note percent signs not part of an interpolation token (like `%(here)s`) must be doubled (`%%`). This applies to both `alembic.ini` and `pyproject.toml` values.

## What Autogenerate Detects (and Does Not)

**Detects:** table add/remove, column add/remove, nullable changes, basic index and explicitly-named unique-constraint changes, basic foreign-key changes.

**Optionally detects (off by default unless configured):** column type changes (`compare_type=True`, default since 1.12), server default changes (`compare_server_default=True`).

**Does NOT detect:** table renames (shows as drop+add), column renames (shows as drop+add), anonymously-named constraints, special types like `Enum` on backends without native ENUM, and (currently) some freestanding constraints / sequences.

**Always review and hand-edit autogenerated migrations.** Name your constraints (see Naming Conventions) to make them reliably detectable and droppable.

Filter what gets compared with `include_name` (cheap, name-based) and `include_object` (fine-grained, requires reflection) hooks in `context.configure(...)`.

## Batch Migrations (SQLite)

SQLite lacks most `ALTER` support; use batch mode for the "move and copy" workflow:
```python
with op.batch_alter_table("some_table") as batch_op:
    batch_op.add_column(Column("foo", Integer))
    batch_op.drop_column("bar")
```
- Batch defaults to "move and copy" only on SQLite and only when needed; pass `recreate="always"` to force on other backends.
- Use `naming_convention=` to drop unnamed SQLite constraints; specify full `existing_type` when changing Boolean/Enum columns.
- For autogenerate batch rendering, set `render_as_batch=True` in `env.py`.
- For offline mode, supply a prefabricated table via `copy_from=`.

## Branching, Merging, and Multiple Bases

- Multiple heads arise when independent revision trees share a parent. Resolve with `alembic merge heads` (creates a merge revision whose `down_revision` is a tuple).
- Reference heads explicitly: `alembic upgrade heads` (all), `branchname@head` (specific branch), partial revision ids, or relative `branchname@+2`.
- Use `branch_labels = ("shoppingcart",)` in a revision to name a branch.
- For multiple independent bases, configure `version_locations`, use `--branch-label` and `--version-path` on `revision`, and `depends_on` for cross-stream dependencies.

## Naming Conventions

Always name constraints so they are portable and droppable across databases. Define a `MetaData` naming convention:
```python
from sqlalchemy import MetaData
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",
}
metadata = MetaData(naming_convention=convention)
```
Pass this metadata as `target_metadata`. In autogenerated scripts, names already tokenized are wrapped with `op.f("...")` to bypass convention re-application. Use `op.f()` explicitly to bypass conventions in `create_*` / `drop_constraint`.

## Offline SQL Generation

Generate SQL without a live connection using `--sql`:
```bash
alembic upgrade ae1027a6acf --sql > migration.sql
alembic upgrade 1975ea83b712:ae1027a6acf --sql > migration.sql   # start:end (offline only)
```
- Offline scripts cannot rely on client/server reads (no `SELECT`-then-act). Use `op.inline_literal()` for literal values in `op.execute()` / `op.bulk_insert()`.
- `env.py` branches on `context.is_offline_mode()` between `run_migrations_offline()` and `run_migrations_online()`.

## Extending Alembic

- **Custom operations**: subclass `MigrateOperation`, register via `@Operations.register_operation("name")`, and provide implementation via `@Operations.implementation_for(MyOp)`. See `references/operations.md`.
- **Autogenerate hooks**: `process_revision_directives` (alter the generated `MigrationScript`), `Rewriter` (targeted rewrites of op directives), and comparison functions registered globally or via plugins.
- **Plugins (Alembic 1.18+)**: define a `setup(plugin)` function; register operations, implementations, and autogenerate comparators. Enable autogenerate comparators per-environment with `autogenerate_plugins=["alembic.autogenerate.*", ...]`. See `references/other.md` (Plugins).

## Reference Files

This skill includes comprehensive documentation in `references/`:

- **getting_started.md** — installation, tutorial, environment creation, `.ini` and `pyproject.toml` configuration, first migrations, history/relative identifiers.
- **autogenerate.md** — autogenerate behavior, filtering hooks, type/default comparison, post-write hooks (Black/zimports), `alembic check`, and the autogenerate API (`compare_metadata`, `produce_migrations`, `Rewriter`, custom comparators).
- **operations.md** — full `Operations`/`BatchOperations` reference (add/alter/drop column, constraints, indexes, tables, execute, bulk_insert), DDL internals, and the operation plugin/extension API.
- **batch.md** — SQLite "move and copy" workflow, reflection control, constraints handling, offline batch, autogenerate batch rendering.
- **branches.md** — multiple heads, merging, branch labels, relative/branch identifiers, multiple bases, `depends_on`.
- **naming.md** — constraint naming conventions, integration with autogenerate, `op.f()` bypass.
- **offline.md** — `--sql` mode, start version handling, writing offline-compatible scripts, environment customization.
- **cookbook.md** — recipes: build DB from scratch, conditional migrations, shared connections, replaceable objects (views/SPs/triggers), multi-tenancy, async (asyncio), data migrations, custom CLI commands, autogenerate rewriters.
- **other.md** — runtime objects (`EnvironmentContext`, `MigrationContext`), commands API, plugins API, configuration API, exceptions, internals overview, script directory/revision map.

Use `view` to read specific reference files when detailed information is needed.

## Working with This Skill

### Start Here
For foundational concepts and a guided walkthrough, read **getting_started.md** (installation, environment creation, `.ini`/`pyproject.toml` config, first migration).

### For Specific Features
- Operations / writing migration bodies → **operations.md**
- Autogenerate behavior and customization → **autogenerate.md**
- SQLite or "move and copy" migrations → **batch.md**
- Multiple heads / merges / multi-base → **branches.md**
- Portable constraint names → **naming.md**
- DBA handoff / restricted DDL → **offline.md**
- Programmatic usage, async, extensions, multi-tenancy → **cookbook.md** and **other.md**

### For Code Examples
Use the high-signal examples above first, then open the matching reference file for full parameter lists and edge cases.

## Notes

- Alembic versions use a three-number scheme but **not** SemVer; the middle digit is a "Significant Minor Release" that may remove deprecated APIs. Pin to major.minor to avoid surprises.
- Requires SQLAlchemy ≥ 1.4.0 and Python ≥ 3.9 (as of Alembic 1.15).
- Reference files preserve the structure and examples from the official documentation.

## Updating

To refresh this skill with updated documentation:
1. Re-run the scraper with the same configuration.
2. The skill will be rebuilt with the latest information.