SQLAlchemy 2.0 #7 Alembic and Production Setup: Migrations, Async, Team Rules

If you have followed the series this far, you can declare models, drive them with sessions, and handle relationships and queries. What remains is operations. Schemas always change, growing traffic eventually demands async, and growing teams demand rules. The final part covers these three.

The limits of create_all, and Alembic #

Base.metadata.create_all(engine) only creates tables that do not exist. It will not add a column to an existing table, change a type, or add an index. From the moment your models and the real database start to drift, you need a tool that manages schema change history — and the standard in the SQLAlchemy ecosystem is Alembic, by the same author.

uv add alembic
alembic init migrations

Of the files alembic init generates, two places need your attention.

• alembic.ini: set the database URL in sqlalchemy.url (in production, better to read it from an environment variable inside env.py).
• migrations/env.py: wire the models’ metadata with target_metadata = Base.metadata. This is the “target state” autogenerate compares against.

From then on, the cycle is three commands on repeat.

# 1. after changing models, generate a migration from the diff against the current DB
alembic revision --autogenerate -m "add nickname column to user"

# 2. open the generated file and review it (mandatory!)

# 3. apply
alembic upgrade head

autogenerate is a draft, nothing more #

autogenerate compares Base.metadata (target) with the live database (current) and generates a Python file with upgrade and downgrade functions filled in. You need to know what it detects well and what it does not.

The most dangerous case is the rename. Rename nickname to alias and autogenerate emits “drop nickname, add alias” — apply that as-is and the data is gone. You have to open the file and rewrite op.drop_column + op.add_column into op.alter_column(..., new_column_name=...). This is why “autogenerate output is a draft; nothing gets applied without review” belongs in the team rulebook.

Two more rules for production. First, migration files get reviewed in the same PR as the code. Second, check lock time for column adds and index builds on large tables. Databases have their own zero-downtime options, like PostgreSQL’s CREATE INDEX CONCURRENTLY, and autogenerate will not reach for them on your behalf.

The naming convention configured in part 3 pays off here. Because constraint names are predictable, the op.drop_constraint("uq_user_account_email", ...) that autogenerate writes works identically on any database.

Async: create_async_engine and AsyncSession #

Use synchronous SQLAlchemy inside an async framework like FastAPI and the event loop blocks for every database wait. 2.0 supports asyncio natively, and it looks almost identical to everything you have learned.

uv add "sqlalchemy[asyncio]" asyncpg  # async PostgreSQL driver

from sqlalchemy import select
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

engine = create_async_engine("postgresql+asyncpg://user:pw@localhost/mydb")
AsyncSessionLocal = async_sessionmaker(engine, expire_on_commit=False)


async def list_users() -> list[User]:
    async with AsyncSessionLocal() as session:
        result = await session.scalars(select(User))
        return list(result.all())

The changes are regular: the engine becomes create_async_engine, the driver becomes an async one (asyncpg and friends), the session becomes AsyncSession, and await attaches to the calls that do I/O (execute, scalars, commit, flush). Model declarations and select() composition are exactly the same as sync.

One difference demands care: lazy loading does not work by default in async sessions. An attribute access like user.addresses implicitly doing I/O cannot exist without an await (it surfaces as a MissingGreenlet error). In async, part 5’s eager loading (selectinload) stops being a choice and becomes effectively mandatory. The expire_on_commit=False in the example is the same story — the async convention that keeps post-commit attribute access from triggering a reload.

Project layout and team rules #

To close the series, a layout proven in real projects:

myapp/
├── app/
│   ├── db.py          # engine, sessionmaker (created once per process)
│   ├── models/        # model modules by domain; one Base in one place
│   ├── repositories/  # a layer collecting queries (optional)
│   └── ...
├── migrations/        # output of alembic init
├── alembic.ini
└── pyproject.toml

• One engine per process. Calling create_engine per request is rebuilding the pool every time. Create it once in a module like db.py and import it.
• Models split by domain, but one Base. Alembic’s target_metadata has to see every model, so make sure env.py ends up importing all model modules. “I added a model and autogenerate can’t see it” is almost always a missing import.
• A layer that collects queries (repository, service — the name is yours) keeps select() assembly out of view code, and puts N+1 policy — which query eager-loads what — in one place.
• Three team rules: every schema change goes through Alembic (no manual DDL on production databases), autogenerate output merges only after review, and session scope stays one per unit of work. Keep these three and most SQLAlchemy incidents never happen.

Closing the series #

The seven parts in one paragraph: SQLAlchemy is a two-layer stack with the ORM on top of Core (part 1); engines and explicit transactions are the foundation (part 2); models are declared with Mapped (part 3); the session batches changes into SQL (part 4); relationships are convenient but N+1 must be watched (part 5); queries all assemble on select() (part 6); and operations rest on Alembic and rules (part 7). The FastAPI integration shape continues in Modern Python in Practice #3, and package and dependency management in the Python Packaging series.

Summary #

• create_all only creates new tables. Schema change history is Alembic’s job, and the cycle is revision –autogenerate, review, upgrade head, on repeat.
• autogenerate is a draft. Renames in particular come out as drop + add and destroy data, so reviews must rewrite them into alter_column.
• Async converts regularly to create_async_engine + AsyncSession, but lazy loading stops working, making eager loading mandatory.
• One engine per process, one Base per project, every schema change through Alembic. These rules are what prevent production incidents.