How to run SQLAlchemy database migrations with Alembic

What and why

Alembic is the migration tool for SQLAlchemy. It compares your declarative models to the live database and generates migration scripts, then applies them in order with full up/down support. It is the standard choice for Python ORM projects, including FastAPI and Flask apps.

Prerequisites

• A Python project with SQLAlchemy models defined.
• A reachable database and its driver (e.g. psycopg for PostgreSQL).
• A virtual environment.

Steps

1. Install and initialize Alembic

pip install alembic
alembic init alembic

This creates an alembic/ directory and alembic.ini.

2. Point Alembic at your metadata

In alembic.ini, set the URL (or read it from an env var in env.py):

sqlalchemy.url = postgresql+psycopg://app:secret@localhost/app

In alembic/env.py, import your models' metadata so autogeneration can see them:

from myapp.models import Base
target_metadata = Base.metadata

3. Autogenerate a migration

After changing a model, run:

alembic revision --autogenerate -m "add users table"

Alembic diffs the models against the database and writes a script under alembic/versions/.

4. Review the generated script

Open the new file and check upgrade() and downgrade(). Autogeneration misses some changes (column renames, server defaults, check constraints), so edit by hand where needed:

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.BigInteger, primary_key=True),
        sa.Column('email', sa.String(255), nullable=False, unique=True),
    )

def downgrade():
    op.drop_table('users')

5. Apply the upgrade

alembic upgrade head

Alembic records the current revision in the alembic_version table.

6. Downgrade when needed

Revert one step:

alembic downgrade -1

Always test downgrades on a copy; data-destroying downgrades should be deliberate.

Verification

Run alembic current to confirm the database is at head, and alembic history to see the chain. Connect to the database and check the schema matches your models.

Next Steps

Run alembic upgrade head automatically in CI and at app startup behind a flag, write data migrations using op.execute, and branch/merge revisions carefully when multiple developers add migrations.