Complete Guide to SQLAlchemy 2.0 and Alembic in Python (Modern Async ORM)
In modern Python backend architectures, reliable database abstraction, declarative schema definitions, and migration workflows are cornerstones of maintainable systems. SQLAlchemy 2.0 represents a comprehensive paradigm shift: discarding legacy 1.x conventions in favor of strictly typed Mapped[...] constructs, unified select() syntax, and native first-class AsyncIO execution.
Paired with Alembic, Python developers gain an automated schema evolution pipeline that tracks model modifications safely without manual DDL scripting.
Short Answer: In SQLAlchemy 2.0, deprecated session.query() calls are eliminated in favor of select(). Declarative models use Python native types with Mapped[int] = mapped_column(), and asynchronous engines are powered by create_async_engine using high-speed async drivers like asyncpg.
Architectural Advantages of SQLAlchemy 2.0
- Deep Static Typing: Full compliance with PEP 484 type annotations, empowering IDE autocomplete and static type checkers like mypy.
- True AsyncIO Concurrency: Execute concurrent database statements without blocking Python's event loop via
AsyncSession. - Unified Query Construction: A singular, expressive API centered around
select(),insert(),update(), anddelete()across both Core and ORM.
1. Installation & Driver Setup
pip install "sqlalchemy>=2.0.0" "alembic>=1.13.0" "asyncpg>=0.29.0" python-dotenv
2. Asynchronous Engine and Session Configuration (database.py)
import os
from collections.abc import AsyncGenerator
from dotenv import load_dotenv
from sqlalchemy.ext.asyncio import (
AsyncSession,
async_sessionmaker,
create_async_engine,
)
from sqlalchemy.orm import DeclarativeBase
load_dotenv()
DATABASE_URL = os.getenv(
"DATABASE_URL",
"postgresql+asyncpg://postgres:password@localhost:5432/my_project_db"
)
engine = create_async_engine(
DATABASE_URL,
echo=False,
pool_size=10,
max_overflow=20,
pool_pre_ping=True,
)
AsyncSessionFactory = async_sessionmaker(
bind=engine,
class_=AsyncSession,
expire_on_commit=False,
)
class Base(DeclarativeBase):
pass
async def get_db_session() -> AsyncGenerator[AsyncSession, None]:
async with AsyncSessionFactory() as session:
try:
yield session
except Exception:
await session.rollback()
raise
3. Declarative Model Modeling
from datetime import datetime
from sqlalchemy import ForeignKey, String, Text, func
from sqlalchemy.orm import Mapped, mapped_column, relationship
from database import Base
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
username: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)
email: Mapped[str] = mapped_column(String(255), unique=True, index=True, nullable=False)
is_active: Mapped[bool] = mapped_column(default=True)
created_at: Mapped[datetime] = mapped_column(server_default=func.now())
articles: Mapped[list["Article"]] = relationship(back_populates="author", cascade="all, delete-orphan")
class Article(Base):
__tablename__ = "articles"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(250), nullable=False)
slug: Mapped[str] = mapped_column(String(250), unique=True, index=True)
body: Mapped[str] = mapped_column(Text, nullable=False)
author_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
author: Mapped[User] = relationship(back_populates="articles")
4. Query Execution with select() and Eager Loading
from sqlalchemy import select
from sqlalchemy.orm import selectinload
from database import AsyncSessionFactory
async def fetch_user_data():
async with AsyncSessionFactory() as session:
# Single record retrieval
stmt = select(User).where(User.username == "taha")
result = await session.execute(stmt)
user = result.scalar_one_or_none()
# Relationship eager loading with selectinload
stmt_with_articles = (
select(User)
.options(selectinload(User.articles))
.where(User.is_active.is_(True))
)
active_users = (await session.scalars(stmt_with_articles)).all()
5. Automated Database Migrations with Alembic
Initialize async template in the repository:
alembic init -t async alembic
In alembic/env.py, bind your metadata:
from database import Base
import models
target_metadata = Base.metadata
Generate and apply revisions:
alembic revision --autogenerate -m "create initial tables"
alembic upgrade head
Frequently Asked Questions
Why is expire_on_commit=False mandatory in async SQLAlchemy?
By default, commits mark instance attributes expired. In an asynchronous context, evaluating attributes after committing would trigger implicit lazy synchronous I/O, leading to MissingGreenlet exceptions.
How does selectinload compare to joinedload?
selectinload generates a secondary WHERE IN (...) query, avoiding Cartesian explosion on one-to-many collections, making it the preferred strategy for async collection loading.
Tags for this article:
