Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 50 ยท Databases

Migrations with Alembic

Learn why you need migrations, the Alembic init, revision and upgrade workflow, autogenerate caveats, and how to roll out schema changes safely.

Your first version of the database comes from Base.metadata.create_all(engine). That call creates tables that are missing, and then it is done: it never adds a column to a table that already exists. The moment your app is live and you want a display_name column, create_all cannot help, and dropping the database is not an option because it holds real data.

A migration tool solves this. It keeps an ordered history of small, named schema changes, and records in the database itself which ones have already been applied. Alembic, written by the author of SQLAlchemy, is the standard tool for SQLAlchemy projects, and it is what FastAPI projects use. (SQLModel tables use it too, because they are SQLAlchemy tables underneath; see SQLModel.)

This lesson explains the workflow and the pitfalls. The command-line steps cannot run inside a browser, so they appear as read-only snippets, but Alembic's operations and its change detection are real Python, and the examples run them live.

Why migrations?

What a migration gives you

History Every schema change is a file in git.
Repeatable Dev, test and production get the same steps.
Reversible A downgrade undoes the change.
Versioned The database knows its own revision.
1 / 2

Without migrations, teams fall back on "run this SQL by hand on production", which nobody remembers, nobody reviews and nobody can repeat on a new laptop. With migrations, a new developer runs one command and gets the exact current schema.

The workflow

You set Alembic up once per project (pip install alembic). From the project root:

Terminal
alembic init migrations

That creates an alembic.ini file and a migrations/ folder with env.py (the glue code that connects to your database), script.py.mako (the template for new files) and an empty versions/ folder. Two edits make it useful. First, tell env.py where your models live, so that it can compare them with the database:

Python
# migrations/env.py (excerpt)
from app.database import Base      # your DeclarativeBase
from app import models              # import so every model registers itself

target_metadata = Base.metadata

Second, make the database URL come from your settings, not from alembic.ini (compare Settings and configuration), so the same project migrates development and production without a committed password.

From then on the daily loop is three commands:

Terminal
# 1. you changed a model; ask Alembic to write a revision for the difference
alembic revision --autogenerate -m "add display_name to users"

# 2. read and edit migrations/versions/3f2a1c9d7b10_add_display_name_to_users.py

# 3. apply it
alembic upgrade head

head means "the newest revision". Other useful commands: alembic current (which revision is this database at?), alembic history (the chain of revisions), alembic upgrade +1, alembic downgrade -1 (go back one step) and alembic upgrade head --sql (print the SQL instead of running it, handy for review).

What a revision file looks like

Each revision is a Python file with an id, a link to its parent (down_revision) and two functions:

Python
"""add display_name to users"""
import sqlalchemy as sa
from alembic import op

revision = "3f2a1c9d7b10"
down_revision = "a81c04e55d2f"

def upgrade() -> None:
    op.add_column("users", sa.Column("display_name", sa.String(100), nullable=True))

def downgrade() -> None:
    op.drop_column("users", "display_name")

The down_revision links form a chain, and the database stores the id of the latest applied revision in a one-row table called alembic_version. Running upgrade head applies every revision between "where the database is" and "the newest one", in order.

The op object offers the operations you need: create_table, drop_table, add_column, drop_column, alter_column, create_index, create_foreign_key, execute for raw SQL, and more. The next example runs exactly such functions against an in-memory SQLite database. It also shows an important professional habit: adding a required column to a table that already has rows.

If you add nullable=False with no default, the database rejects the change, because the existing rows have no value. The safe recipe has three steps: add the column as nullable, backfill existing rows, then tighten to NOT NULL. SQLite cannot alter a column in place, so Alembic's batch_alter_table rebuilds the table behind the scenes; on PostgreSQL or MySQL a plain op.alter_column is enough.

Python A revision: add, backfill, tighten, and undo

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The two functions are plain Python that you could also call yourself. Alembic's command line adds the bookkeeping around them: finding the right files, running them in order, and updating alembic_version.

Alembic revisions use the synchronous SQLAlchemy engine even in async projects. Migrations run as a separate command-line step (not inside the web server), and env.py for an async engine needs a short wrapper that the alembic init -t async migrations template generates for you.

Autogenerate and its caveats

--autogenerate compares your models (target_metadata) with the real database and writes the difference as upgrade() code. It is a huge time saver, and it is only a draft. You must read every generated file. Alembic itself can ask the question "what differs?" through compare_metadata, which is what autogenerate uses. The example below creates a table that says age, while the model says years, and a new Tag model that has no table yet.

Python What autogenerate sees: a rename looks like drop plus add

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Alembic reports a new table (add_table: tags), which is correct, and then a remove_column: users.age plus add_column: users.years. It cannot know you meant a rename: it only sees a column that disappeared and a different one that appeared. If you run that revision, the data in age is dropped. The fix is to edit the generated file by hand and replace the pair with op.alter_column("users", "age", new_column_name="years").

Other things autogenerate does not detect reliably:

  • Renamed tables and renamed columns (shown above).
  • Changes to column types and server defaults, unless you enable compare_type and compare_server_default in env.py.
  • Constraint and index changes with unnamed constraints. Give constraints explicit names with a MetaData(naming_convention=...) so they can be found and dropped later.
  • Data changes. Moving or converting data is always a hand-written op.execute(...).

Production rollouts

A migration that works on your laptop with ten rows can lock a production table for minutes. Habits that keep deployments calm:

  1. Review the SQL. Run alembic upgrade head --sql and read it, especially on large tables.
  2. Expand, then contract. Release the schema change first in a form the old code still works with (add a nullable column, add a table), deploy the new code, and only in a later release remove what is unused. A rename becomes: add new column, copy data, switch code, drop old column.
  3. Keep each revision small and focused. One concern per file makes failures easy to understand.
  4. Back up before you migrate, and rehearse on a copy of production data.
  5. Run migrations once, as a separate step, before the new application version starts, not from every worker at start-up. Two processes migrating at once is asking for trouble. In containers this is often a one-off job or an init step (see Deployment).
  6. Know that downgrade is best effort. Dropping a column loses its data, so a downgrade cannot always restore the past. Restoring a backup is the real undo.
  7. Never edit a revision that has been applied on a shared database. Add a new revision instead.
  8. Use a database with transactional DDL if you can: PostgreSQL wraps a revision in a transaction, so a failure rolls back cleanly. MySQL commits each DDL statement immediately, so a half-applied revision needs manual cleanup.

For tests, the usual shortcut is create_all on an in-memory database (fast), plus one test or CI job that runs alembic upgrade head on an empty database and checks that the result matches your models, so the migrations and the models can never silently drift apart.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is Base.metadata.create_all() not enough for a live application?

    create_all skips tables that already exist, so new columns or changed types need migrations.
  2. 2. Where does Alembic record which revisions a database has already applied?

    The database stores the id of its current revision in alembic_version, so upgrade head knows which steps remain.
  3. 3. You rename a model attribute from age to years and autogenerate a revision. What does it contain?

    Alembic compares schemas and cannot tell a rename from a removal plus an addition, so you must replace the pair with alter_column(new_column_name=...) by hand.
  4. 4. What is a safe way to add a required column to a table that already holds rows?

    The three-step recipe gives every existing row a value before the constraint is enforced.

Summary

  • Migrations are an ordered, versioned history of schema changes; Alembic stores the current revision in alembic_version.
  • The workflow: alembic init, point env.py at your metadata and URL, alembic revision --autogenerate -m "...", review, alembic upgrade head.
  • Revisions are Python files with upgrade() and downgrade() that use op.add_column, op.create_table, op.execute and friends.
  • Autogenerate is a draft: it misses renames, type changes and data moves, so always read and edit the file.
  • In production, review the SQL, use expand-then-contract, migrate once as a separate step and keep backups, because downgrades cannot restore dropped data.

Next: Testing FastAPI apps shows how to test endpoints, dependencies and the database with an isolated database per test.