alembic
Alembic is the database migration tool written by the author of SQLAlchemy. You run alembic init once to get a versions directory, an env.py that tells Alembic how to connect and which metadata to compare against, and a config file. From then on each schema change is a Python script with an upgrade() and a downgrade() function calling directives such as op.add_column and op.create_index. Alembic tracks which script a database is on in a single alembic_version table. The headline feature is autogenerate: it inspects the live database, compares it to the tables declared in your SQLAlchemy models, and writes a candidate migration for you. Revisions form a directed graph rather than a straight line, so branches and merge points are first-class.
If your schema is defined in SQLAlchemy, Alembic is the answer and there is no serious competitor. Just treat autogenerate as a first draft that needs reading line by line, not as a build step.
Use it if
- You already define your schema as SQLAlchemy models or Core Table objects and want migrations generated from the diff instead of hand-written
- You need migrations reviewed as code: each revision is an ordinary Python file you can edit, test, and put through code review before it touches production
- Your DBAs will not let the app run DDL and want textual SQL: offline mode with --sql prints the statements to a file instead of executing them
- Several teams merge branches into one schema and you need real branch and merge handling rather than a filename timestamp race
- You are on Django: its migration system is built in, understands your models, and Alembic would be a second source of truth for the same tables
- Your schema lives in hand-written SQL and you do not use SQLAlchemy: Alembic's value comes from comparing against SQLAlchemy metadata, and yoyo-migrations runs plain .sql files with far less machinery
- You expect autogenerate to be correct without review: it cannot see renames, so renaming a column comes out as a drop plus an add that silently discards the data, and server default comparison is off unless you turn it on
- You want a small mental model: env.py, the config file, the revision graph, branch labels, merge points, and the SQLite batch mode are a real amount of machinery for a two-table app
- You use Postgres enums or other backend-specific types heavily: Alembic will not create or alter enum types for you on upgrade, and those migrations end up hand-written anyway
Setup reality
pip install alembic pulls SQLAlchemy, Mako, and typing-extensions, then alembic init alembic drops a directory plus a config file into your repo. The step everyone gets wrong is env.py: you must import the modules that define your models so their tables are registered, then set target_metadata to that MetaData object. Miss the import and autogenerate cheerfully produces a migration that drops your entire schema. The connection URL in alembic.ini goes through ConfigParser interpolation, so any % in a password has to be written as %%, which is a fun evening; most teams delete the URL and read it from the environment inside env.py instead. Pick your template up front too, since generic, async, pyproject, and pyproject_async all produce different env.py files. Python 3.10 or newer, SQLAlchemy 1.4.23 or newer.
Patterns
Create the migration environmentinitialize-environment
alembic init alembic # sync SQLAlchemy
alembic init -t async alembic # asyncio engines
alembic init -t pyproject alembic # config in pyproject.tomlThe template decides what env.py looks like, and switching later means rewriting it by hand. The async template is required if your app uses an async engine; the sync one will hang on connect.
Point env.py at your modelswire-target-metadata
# alembic/env.py
import os
from myapp.db import Base
import myapp.models # noqa: F401 side-effect import registers the tables
target_metadata = Base.metadata
config.set_main_option("sqlalchemy.url", os.environ["DATABASE_URL"])The unused import is load-bearing: a model module that never gets imported has no tables on the metadata, and autogenerate will write a migration that drops them. Reading the URL from the environment also sidesteps the %% escaping the config file requires.
Generate a migration from the model diffautogenerate-revision
alembic revision --autogenerate -m "add users.last_login"
# review alembic/versions/<hash>_add_users_last_login.py
alembic upgrade headThis compares your models against the database currently pointed at, so run it against a database already at head or you get a diff full of unrelated changes. Read every generated line before committing.
Move a database between revisionsupgrade-and-downgrade
alembic upgrade head # apply everything
alembic upgrade +1 # one step forward
alembic downgrade -1 # one step back
alembic current # what is this database on
alembic history --verbosedowngrade only works if whoever wrote the revision actually filled in downgrade(); autogenerate produces a best guess that frequently drops data. Many teams delete downgrade bodies and roll forward instead.
Write a revision by handhand-written-migration
import sqlalchemy as sa
from alembic import op
revision = "a1b2c3d4e5f6"
down_revision = "9f8e7d6c5b4a"
def upgrade():
op.add_column("users", sa.Column("last_login", sa.DateTime(), nullable=True))
op.create_index("ix_users_last_login", "users", ["last_login"])
def downgrade():
op.drop_index("ix_users_last_login", table_name="users")
op.drop_column("users", "last_login")Adding a NOT NULL column to a populated table fails: add it nullable, backfill, then alter to NOT NULL in the same revision. revision and down_revision are what build the graph, so never edit them after the file is merged.
Alter a column on SQLitesqlite-batch-mode
def upgrade():
with op.batch_alter_table("users", schema=None) as batch_op:
batch_op.alter_column("email", nullable=False)
batch_op.create_unique_constraint("uq_users_email", ["email"])SQLite cannot ALTER most things, so batch mode creates a new table, copies rows, drops the old one, and renames. Unnamed constraints often cannot be recreated, which is why the docs push a naming convention on your MetaData first.
Move data, not just schemadata-migration
from alembic import op
import sqlalchemy as sa
def upgrade():
op.add_column("users", sa.Column("full_name", sa.String(200)))
op.execute(
"UPDATE users SET full_name = first_name || ' ' || last_name"
)
op.alter_column("users", "full_name", nullable=False)Never import your application models inside a migration: they describe today's schema, not the schema at this revision, and the migration breaks the next time a model changes. Use raw SQL or a locally declared sa.table().
Emit SQL scripts instead of running DDLoffline-sql-mode
alembic upgrade head --sql > migration.sql
alembic upgrade ae1027a6acf:head --sql # from a known starting revisionOffline mode cannot run anything that depends on reading rows, so op.execute with a SELECT or a Python loop over results produces nothing useful. Give an explicit start revision, since Alembic cannot query alembic_version in this mode.
Resolve two heads after a branch mergemerge-heads
alembic heads # shows more than one head
alembic merge -m "merge feature branches" heads
alembic upgrade headTwo developers branching from the same revision is the normal cause. A merge revision is usually empty; it exists only to rejoin the graph so upgrade head is unambiguous again.
Adopt a database that already has the schemastamp-existing-database
# generate the baseline from the existing DB, then mark it applied
alembic revision --autogenerate -m "baseline"
alembic stamp headstamp writes to alembic_version without running any DDL. Getting this backwards and running upgrade instead will try to CREATE TABLE over live tables.
Turn on type and server default comparisontune-autogenerate-comparison
# alembic/env.py, inside run_migrations_online()
context.configure(
connection=connection,
target_metadata=target_metadata,
compare_type=True,
compare_server_default=True,
include_schemas=False,
)compare_server_default is off by default because backends report defaults in inconsistent text forms, so switching it on produces occasional phantom diffs. Turn it on anyway if silent default drift matters more to you than noise.
Apply migrations from Pythonrun-migrations-in-tests
from alembic import command
from alembic.config import Config
cfg = Config("alembic.ini")
cfg.set_main_option("sqlalchemy.url", test_database_url)
command.upgrade(cfg, "head")Running migrations against your test database instead of create_all() is the only way to find out that a migration is broken before production does. Paths inside the config file are relative to the working directory, so set script_location absolutely in CI.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| yoyo-migrations | PyPI | Your migrations are plain SQL files and you have no SQLAlchemy models to diff against |
| django | PyPI | You are starting fresh and would rather have ORM, migrations, and admin from one framework than assemble them |
| alembic-utils | PyPI | You need autogenerate to also track Postgres views, functions, triggers, and policies, which core Alembic ignores |