Alembic Database Migrations

Introduction

Base.metadata.create_all() works for solo prototypes but fails teams when columns change. Alembic tracks schema versions as migration scripts you commit to Git. FastAPI projects use Alembic directly—the same engine behind Flask-Migrate. This chapter initializes Alembic, autogenerates revisions, and applies upgrade head in dev and deploy.

Prerequisites

SQLAlchemy With FastAPI
Models defined on shared Base
pip install alembic

Install Alembic

bash

pip install alembic

From project root:

bash

alembic init alembic

Creates:

text

alembic/
├── env.py
├── script.py.mako
└── versions/
alembic.ini

Commit alembic/ and alembic.ini to Git.

Compare Flask-Migrate commands in Flask-Migrate chapter—same Alembic core.

Configure alembic.ini

Set SQLAlchemy URL (or override in env.py from settings):

ini

# alembic.ini — optional if env.py reads Settings
sqlalchemy.url = sqlite:///./app.db

Prefer reading DATABASE_URL from environment in env.py so production and dev share one code path.

Configure env.py

alembic/env.py (key parts):

python

Code explanation:

target_metadata = Base.metadata lets autogenerate detect model changes
Import every model module so metadata is complete

First Migration

After defining models:

bash

alembic revision --autogenerate -m "Create items and users"

Review alembic/versions/xxxx_create_items_and_users.py:

python

def upgrade():
    op.create_table("items", ...)
 
def downgrade():
    op.drop_table("items")

Apply:

bash

alembic upgrade head

Check current revision:

bash

alembic current
alembic history

Workflow

text

Edit models → alembic revision --autogenerate -m "msg" → review script → alembic upgrade head

Team flow:

Developer creates migration, commits
Teammates pull and run alembic upgrade head
CI/CD runs upgrade before starting Uvicorn

Production Notes

Run migrations before app restart:

bash

alembic upgrade head
uvicorn app.main:app --host 0.0.0.0 --port 8000

MySQL charset utf8mb4—align with MySQL chapters.

Warning

Review Autogenerate Output

Alembic may miss renames—it might drop + add instead. Edit revision manually when needed.

Adding Non-Nullable Columns

Existing rows block NOT NULL without default:

python

def upgrade():
    op.add_column("items", sa.Column("status", sa.String(20), server_default="active", nullable=False))
    op.alter_column("items", "status", server_default=None)

Or multi-step: add nullable → backfill → set not null.

Rollback

One step back:

bash

alembic downgrade -1

Production prefers forward-fix migrations over downgrade when data exists.

FAQ

Target database is not up to date?

Run alembic upgrade head.

Multiple heads?

Parallel branches created revisions—alembic merge heads then upgrade.

env.py ImportError?

Run alembic from project root; ensure PYTHONPATH includes app package.

SQLite vs MySQL differences?

Test migrations against MySQL before production if dev uses SQLite.

Stamp existing DB?

alembic stamp head marks revision without running—use when aligning legacy DB carefully.

Delete alembic folder?

Only before shared deploy—never on production with history.