Handbooks / FastAPI / Chapter 7

Database Migrations with Alembic

7 pages · ~5 min✓ Reviewed

Builds on Authentication and Security. Next up: Testing and Deployment.

FastAPIPage 1

Alembic Mental Model and Setup

1. What Alembic Is

Alembic = migration tool for SQLAlchemy
Chain of revision scripts, linear or branched
Each has revision and down_revision IDs
alembic_version table holds the one applied head ID

a1b2down_revision None
c3d4down_revision a1b2
e5f6head, stored in DB

2. Setup

bash

alembic init alembic
alembic init -t async alembic

second form = async template

Creates alembic.ini, env.py, script.py.mako, versions/
Set sqlalchemy.url in the ini, or override in env.py
config.set_main_option("sqlalchemy.url", settings.db_url)
Never commit real credentials in alembic.ini

3. Alembic vs create_all()

	create_all()	Alembic
Existing tables	skipped	altered
Column changes	never	yes
Version tracking	none	`alembic_version`

Not a migration tool

create_all() only creates missing tables. It never alters or tracks versions.

Why not create_all()? It never alters tables or tracks versions
Where is DB state? alembic_version, holding the head ID
Safe URL? Override in env.py from app settings

FastAPIPage 2

env.py and target_metadata

1. What env.py Does

alembic cmdevery command
env.pybuilds engine
configure()context
run_migrations()apply

2. target_metadata

target_metadata = Base.metadata is what autogenerate diffs against the live DB
None: autogenerate aborts with an error, no MetaData object
Import models before reading Base.metadata, else tables are missing: empty migration

python

import myapp.models  # registers tables
from myapp.db import Base
target_metadata = Base.metadata

imports first, then read the metadata

3. Modes and Flags

	Offline	Online
Command	`--sql`	normal
DB	none, URL only	connects

FastAPIPage 3

Autogenerate Workflow and Its Limits

1. Generate a Draft

bash

alembic revision --autogenerate -m "add user email"

diffs target_metadata against the DB, writes a script to versions/

Output is a draft, not a guarantee
Read and edit the script before alembic upgrade

2. Detects vs Misses

Detects	Misses
Added or removed tables and columns	Table or column renames
Nullable changes	Most `server_default` changes
Indexes and unique constraints	CHECK constraints on some backends
Foreign keys	Enum value changes

3. Fix by Hand

Data migrations and backfills are never generated

FastAPIPage 4

Upgrade, Downgrade and Naming

1. Moving the Version

Command	Effect
`alembic upgrade head`	Applies all pending revisions
`alembic upgrade +1`	Applies one revision
`alembic upgrade <rev>`	Targets one revision; a unique prefix works
`alembic downgrade -1`	Reverts one step
`alembic downgrade base`	Reverts everything

Rollbacks need a working downgrade() in every revision

2. Inspect State

Command	Shows or does
`alembic current`	Revision the DB is at

FastAPIPage 5

Async Engines and Deploying Migrations

1. Async env.py

Alembic is synchronous; async drivers (asyncpg, aiosqlite) need a bridge
Build async_engine_from_config, then connection.run_sync(do_run_migrations)
Start from the template: alembic init -t async alembic

python

from alembic import context

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

sync part, runs inside run_sync

python

import asyncio
from sqlalchemy.ext.asyncio import async_engine_from_config
async def run_async_migrations():
    engine = async_engine_from_config(config.get_section(config.config_ini_section), prefix="sqlalchemy.")
    async with engine.connect() as connection:
        await connection.run_sync(do_run_migrations)
asyncio.run(run_async_migrations())

async part, entry point of online mode

2. Deploying Safely

Release stepalembic upgrade head
Run onceinit job
Start new appall replicas

Expand then contract

1Expandadd column
2Deploy codeold + new work
3Contractdrop later

FastAPIPage 6

Alembic Cheat Sheet

1. Setup and Create

bash

alembic init -t async alembic
alembic revision --autogenerate -m "add user email"

Set sqlalchemy.url, then target_metadata = Base.metadata
None metadata: autogenerate errors; missing model imports: empty migration
Review the generated script before applying

2. Apply and Inspect

Command	Effect
`upgrade head / +1 / <rev>`	Apply all / one / target
`downgrade -1 / base`	Revert one / everything
`current`, `history --verbose`, `heads`	Show state, chain, heads
`stamp head`	Mark DB current, run nothing
`merge heads -m "merge"`	Fix multiple heads

FastAPIPage 7

Check yourself

You add a new model and run alembic revision --autogenerate. The script has an empty upgrade(). Name two causes.

The model's module was never imported, so Base.metadata lacks the table.
target_metadata points at the wrong or an empty MetaData.

You rename column name to full_name and autogenerate. What does the script do?

It drops name and adds full_name, so the data is lost.
Edit it to use op.alter_column(..., new_column_name=...).

Four app replicas each run alembic upgrade head on startup. What is the bug?

Replicas can run the same migration concurrently and race.
Run it once as a release step before the new version starts.

configure() flag	Effect
`compare_type`	type changes; default on since 1.12, older need True
`compare_server_default=True`	opt-in for server_default
`include_schemas=True`	multi-schema
`render_as_batch=True`	SQLite

Database Migrations with Alembic

Alembic Mental Model and Setup

1. What Alembic Is

2. Setup

3. Alembic vs create_all()

env.py and target_metadata

1. What env.py Does

2. target_metadata

3. Modes and Flags

Autogenerate Workflow and Its Limits

1. Generate a Draft

2. Detects vs Misses

3. Fix by Hand

Upgrade, Downgrade and Naming

1. Moving the Version

2. Inspect State

Async Engines and Deploying Migrations

1. Async env.py

2. Deploying Safely

Alembic Cheat Sheet

1. Setup and Create

2. Apply and Inspect

Check yourself

3. Naming and Branches

3. Blind Spots

4. Deploy