Database Migrations with Alembic

Someone ran ALTER TABLE directly on production at 2 a.m. A column got renamed. A week of customer records went with it. That is what happens when database migrations live outside of version control.

Alembic is the canonical migration tool for SQLAlchemy, written by the same author (Mike Bayer). It gives you one Python file per schema change, with an upgrade() and a downgrade() function, checked into Git, and applied with alembic upgrade head. The same file knows how to roll back, so a bad deploy is one command away from being undone. Alembic works with the SQLAlchemy ORM, raw Core tables, and any database SQLAlchemy can talk to.

If you have not read our SQLAlchemy basics guide yet, do that first. This article assumes you already have a Base = declarative_base() and a few model classes.

Key Takeaways

• One Python file per schema change, checked into Git, applied with alembic upgrade head. The same file has a downgrade() so any migration is reversible.
• alembic init alembic sets up the migration environment once. Every subsequent change uses alembic revision --autogenerate -m "..." and then a careful hand-review.
• For an existing database, run alembic stamp head to mark it as caught up. Never run alembic upgrade head against a populated database.
• Wrap any alter_column on SQLite in op.batch_alter_table(...), or your local test suite will explode.
• Read the sqlalchemy.url from your app config inside env.py. Secrets stay out of alembic.ini, and percent signs in passwords do not need escaping.
• Autogenerate is a first draft, not a final answer. Renames and anonymous constraints will silently destroy data if you trust it blindly.

How do I install and initialise Alembic?

Install Alembic the usual way:

$ pip install alembic

Then scaffold a migration environment inside your project. The default template is called generic and covers plain sync SQLAlchemy apps, which is what most tutorials and starter projects assume:

$ alembic init alembic

This creates an alembic/ directory and an alembic.ini file at your project root, right next to your application package. The generated structure looks like this:

project/
├── alembic/
│   ├── env.py            # wired up here, runs every migration
│   ├── script.py.mako    # template for new migration files
│   └── versions/         # your actual migrations live here
├── alembic.ini
├── myapp/
│   └── models.py
└── ...

The three files that matter for day-to-day work are alembic.ini (your config), env.py (the runtime hook that connects to your models and database), and script.py.mako (the template that generates new migration files, which are almost never edited directly).

If you want a different scaffold, alembic list_templates shows what is available. For a PEP 621 package, use alembic init --template pyproject alembic. For an asyncio project, use --template async. The rest of this article sticks to the default generic template.

How do I wire Alembic to my SQLAlchemy models?

alembic.ini ships with a placeholder sqlalchemy.url. You can hard-code your connection string there, but for any non-toy app you should read it from your app config so the same code runs locally, in CI, and in production. Alembic needs to know which Base to compare against for autogenerate to work, and that is wired up in env.py.

Edit alembic/env.py to import your Base and override the URL:

# alembic/env.py
from logging.config import fileConfig
from sqlalchemy import engine_from_config, pool
from alembic import context

from myapp.config import settings   # your pydantic BaseSettings, env vars, etc.
from myapp.models import Base       # your declarative Base

config = context.config
config.set_main_option("sqlalchemy.url", settings.database_url)

if config.config_file_name is not None:
    fileConfig(config.config_file_name)

target_metadata = Base.metadata   # required for --autogenerate

# run_migrations_offline() and run_migrations_online() below stay as
# generated; nothing else needs to change in a basic setup.

Two things to notice:

• target_metadata = Base.metadata is what makes alembic revision --autogenerate know which tables to compare against. Forget this and autogenerate silently does nothing. It will report an empty migration and commit.
• Reading the URL from your app settings (often via python-dotenv or a pydantic BaseSettings) means your secrets never live in alembic.ini. Percent signs in passwords also do not need to be escaped there, because ConfigParser only interpolates the alembic.ini file itself.

What does a migration file actually look like?

It is worth writing one migration by hand before letting autogenerate do it for you. You learn the file shape, autogenerate output stops looking like magic, and the next section makes more sense.

Create a file by hand under alembic/versions/, or use the CLI:

$ alembic revision -m "create user table"
  Generating /.../alembic/versions/27c6a30d7c24_create_user_table.py ... done

Open the new file. The header is generated from script.py.mako; the body of the file is the upgrade() and downgrade() functions. Fill them in with a real schema change so you can see what a finished migration actually looks like:

"""create user table

Revision ID: 27c6a30d7c24
Revises:
Create Date: 2026-06-07 10:00:00.000000

"""
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa


revision: str = "27c6a30d7c24"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "user",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("email", sa.String(200), nullable=False),
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now()),
    )
    op.create_unique_constraint("uq_user_email", "user", ["email"])


def downgrade() -> None:
    op.drop_table("user")

Save the file, then apply it against your local database. Alembic prints what it did so you can spot mistakes before they reach staging or CI:

$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 27c6a30d7c24, create user table

$ alembic current
27c6a30d7c24 (head)

$ alembic history --verbose

alembic downgrade -1 runs the downgrade() function. alembic downgrade base reverts everything and leaves the database empty. Filenames use a partial GUID, not an integer. Ordering is relative, set by down_revision and depends_on, so files can be spliced between branches by hand.

How does alembic revision —autogenerate work?

Autogenerate compares your Base.metadata to the live database and writes a candidate migration that closes the gap. It is the feature that makes Alembic worth adopting on day one, and the feature most likely to bite you if you trust it blindly. The official docs use the word “candidate” on purpose: treat the output as a first draft you read end-to-end before committing.

$ alembic revision --autogenerate -m "add post table"

It catches a lot:

• Table and column add/drop
• Nullable changes
• Basic index and foreign-key changes
• Server-side default changes (only if you set compare_server_default=True in context.configure)

It does not catch:

• Column renames. Renaming email_address to email shows up as drop email_address + add email, which destroys data. Hand-edit the generated file to op.alter_column(..., new_column_name="email").
• Anonymously-named constraints. Always name them: UniqueConstraint("email", name="uq_user_email"). Autogenerate cannot match an unnamed index against a previous unnamed index, so a “no change” run still emits a drop-and-recreate.
• Changes inside custom CheckConstraint expressions, ENUM changes on databases without native enums, partition changes, and a few other dialect-specific tweaks.

The discipline that saves you: read the file, delete anything dangerous, fix what it missed, and only then commit. If a teammate reviews a 200-line autogenerated migration without reading it, that is on both of you.

What are the most common database migration operations?

These are the operations you will actually use day to day. The full list is in the Alembic operations reference, but these cover most migrations. Each snippet below shows the upgrade() half; the matching downgrade() is the inverse and is what you would write in a real migration.

Add a column with a server default

Adding a NOT NULL column to a populated table fails unless you also give it a default. The safe pattern is: add the column with a server default in this migration, then drop the default in a follow-up migration once the column is backfilled. That gives Postgres and SQLite time to handle the rewrite without a long lock.

def upgrade() -> None:
    op.add_column(
        "user",
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False),
    )

def downgrade() -> None:
    op.drop_column("user", "created_at")

Create and drop indexes

Use op.f("ix_user_email") so the index name is generated by your naming convention and stays consistent across autogenerate runs. Indexes are cheap to add but expensive to forget on a hot query path, so it is worth the extra line. Mark the index unique when the column is, and remember that unique indexes and unique constraints are not the same thing in Postgres. Pick one deliberately.

def upgrade() -> None:
    op.create_index(op.f("ix_user_email"), "user", ["email"], unique=True)

def downgrade() -> None:
    op.drop_index(op.f("ix_user_email"), table_name="user")

Add a foreign key

Foreign keys are the other thing you will add constantly, and a clear naming convention pays off the first time you debug a constraint violation. Use names like fk_<table>_<col>_<reftable> so they are easy to find in psql output and in your migration history. ondelete="CASCADE" is the right choice when the child row has no meaning without the parent; for everything else, default to "RESTRICT" and be explicit.

def upgrade() -> None:
    op.create_foreign_key(
        "fk_post_author_id_user",
        source_table="post",
        referent_table="user",
        local_cols=["author_id"],
        remote_cols=["id"],
        ondelete="CASCADE",
    )

def downgrade() -> None:
    op.drop_constraint("fk_post_author_id_user", "post", type_="foreignkey")

Rename a column by hand

Autogenerate cannot detect renames. If you renamed email to email_address in your model, the generated migration will look like a drop-plus-add, which would silently destroy your data. The fix is to recognise the pattern, delete the drop-and-add lines, and replace them with a single alter_column that uses new_column_name. This is the most expensive autogenerate mistake in the wild.

def upgrade() -> None:
    op.alter_column("user", "email", new_column_name="email_address")

def downgrade() -> None:
    op.alter_column("user", "email_address", new_column_name="email")

SQLite needs batch_alter_table

SQLite cannot ALTER COLUMN directly. On SQLite (and old MySQL), wrap any alter_column in a batch_alter_table block. It rebuilds the table under the hood — copy every row into a temp table, swap them, drop the original — which is why a migration that is instant on Postgres can take real wall-clock time on a multi-million-row SQLite database.

def upgrade() -> None:
    with op.batch_alter_table("user") as batch_op:
        batch_op.alter_column("email", type_=sa.String(320))
        batch_op.add_column(sa.Column("age", sa.Integer(), nullable=True))

def downgrade() -> None:
    with op.batch_alter_table("user") as batch_op:
        batch_op.drop_column("age")
        batch_op.alter_column("email", type_=sa.String(200))

This is the single most common gotcha when running a full local SQLite test suite. If your SQLite migration blows up on ALTER COLUMN, the fix is almost always batch_alter_table. See our SQLite guide for the full local-dev setup.

When do I need a data migration?

Schema changes sometimes need to move rows. A new column needs values; an old one needs splitting; a status field needs rewriting. Reach for op.bulk_insert for inserts and op.execute for raw SQL when you need it. The typical pattern is add nullable, backfill, enforce NOT NULL, and only then add a unique constraint: never add a unique constraint over a column whose values have not been backfilled, or the migration will fail on the first duplicate.

from alembic import op
import sqlalchemy as sa


def upgrade() -> None:
    op.add_column("user", sa.Column("slug", sa.String(200), nullable=True))
    op.execute("UPDATE user SET slug = lower(replace(email, '@', '-'))")
    op.alter_column("user", "slug", nullable=False)
    op.create_unique_constraint("uq_user_slug", "user", ["slug"])


def downgrade() -> None:
    op.drop_constraint("uq_user_slug", "user", type_="unique")
    op.drop_column("user", "slug")

Keep data migrations idempotent when you can (WHERE slug IS NULL instead of running the UPDATE twice), and split large backfills into batches of a few thousand rows so a write lock does not sit on the table for ten minutes. A 10 million row UPDATE is a different migration from a 10 row UPDATE; the rest of the schema does not need to be blocked while you figure that out.

What is the production checklist?

A few habits that save real pain:

• Stamp existing databases, do not upgrade them. If your database already has tables because you shipped before adopting Alembic, run alembic stamp head to mark it as caught up. Running alembic upgrade head against a populated database will try to recreate every table and fail on the first one. This is the number one production incident in the first month of adopting Alembic.

• One logical change per migration. Easier to review, easier to revert, easier to bisect. Splitting a “rename column and add index” change into two files costs you almost nothing and buys you the ability to roll back the rename without losing the index.

• Always provide a working downgrade. A migration without one is a one-way door. You can still deploy a one-way migration, but write it as a separate file named so the team knows.

• Run migrations in CI. Spin up a throwaway Postgres in your pipeline, run alembic upgrade head, and fail the build if the migration does not apply cleanly. The cheapest version is a GitHub Actions job that boots postgres:16 as a service container, sets DATABASE_URL, and runs the upgrade:

  # .github/workflows/test.yml
  services:
    postgres:
      image: postgres:16
      env:
        POSTGRES_DB: app_test
        POSTGRES_USER: app
        POSTGRES_PASSWORD: app
      ports: ["5432:5432"]
      options: >-
        --health-cmd "pg_isready -U app"
        --health-interval 5s
  steps:
    - run: pip install -e ".[test]"
    - run: alembic upgrade head
    - run: pytest

  The same trick works for SQLite-in-CI when you do not want a Postgres dependency: point sqlalchemy.url at a temp file and delete it after the run.

• Read your URLs from config, not alembic.ini. Same reason as above: keeps secrets out of source control and avoids the percent-sign escape trap in ConfigParser.

• Pin your Alembic version in production. Behaviour around add_column defaults changed in 1.18.x; pinning makes that predictable. Treat Alembic like any other tool with a migration path: lock the version, upgrade it deliberately.

Conclusion

Alembic turns database migrations into code review. The day-to-day loop is small: edit a model, run alembic revision --autogenerate -m "...", hand-fix the file, run alembic upgrade head locally, commit, and let CI verify it. Production runs the same alembic upgrade head against the real database. The whole point is that your database migrations are diffable, reviewable, and reversible, not a mystery SQL script someone ran at midnight.

If you are wiring this into a web service, the FastAPI quickstart shows how Alembic slots in next to a running app, and the SQLAlchemy basics guide covers the model side if you skipped it. For local development without a Postgres dependency, the SQLite guide plus a batch_alter_table habit covers most of what you will need.

Frequently asked questions

What is the difference between Alembic and Base.metadata.create_all()?

create_all() issues CREATE TABLE for every model every time it runs, and does nothing for tables that already exist. It also has no concept of “downgrade.” Alembic emits a Python file per change, tracks which migrations have been applied with a alembic_version table, and ships a working downgrade() for each one. Use create_all() for prototypes and tests; use Alembic for anything that needs to survive a release.

Can Alembic detect column renames automatically?

No. Autogenerate sees the old column and the new column as two separate changes — drop one, add the other — which on a populated table means data loss. The fix is to recognise the pattern in the generated file, delete the drop and add, and replace them with a single op.alter_column(..., new_column_name=...). There is no flag that turns rename detection on.

Should I put one migration per file or several changes in one?

One logical change per file. Two columns that always ship together can share a migration; two unrelated changes (rename a column, add a new index) should be in two files. The payoff shows up the first time you need to revert one without the other, and the first time git bisect lands you on a smaller diff.

How do I adopt Alembic for a database that already has tables?

Run alembic init alembic, wire up target_metadata in env.py, and then — critically — run alembic stamp head instead of alembic upgrade head. The stamp command writes a row to alembic_version saying “the database is already at the head revision” without running any DDL. After that, every subsequent migration applies normally.

How do I roll back a bad migration in production?

alembic downgrade -1 runs the most recent migration’s downgrade() function. In a real incident you would usually take the application offline first, run the downgrade, verify the schema, then bring the app back up. This is why a working downgrade() is non-negotiable. A one-way migration in production is a pager event waiting for a quiet Sunday.

See Also

• SQLAlchemy Basics
• python-dotenv
• SQLite Guide
• FastAPI Quickstart