Scaling Python applications often requires database operations that don't block the event loop. SQLAlchemy 2.0 brings native async support, type-annotated models, and a unified query syntax that fits current Python development. This guide covers setting up async engines, defining type-safe models, handling N+1 query issues, and managing schema migrations with Alembic.
Why Async SQLAlchemy 2.0 Changes the Game
Previous SQLAlchemy versions used synchronous sessions and the session.query() pattern, which could stall async apps and complicate static analysis. SQLAlchemy 2.0 moves to a select()-based syntax and async session execution. This lets developers make async database calls and work smoothly with type checkers like mypy and Pyright.
# Modern async query in SQLAlchemy 2.0 stmt = select(User).where(User.email == "dev@example.com") result = await session.execute(stmt) user = result.scalar_one_or_none()
Using async drivers like asyncpg for PostgreSQL helps avoid blocking I/O and keeps applications responsive under heavy load.
Setting Up an Async Engine and Session
To enable async features, install the necessary packages:
pip install sqlalchemy[asyncio] asyncpg alembic
Set up your async engine and session factory with this configuration:
from sqlalchemy.ext.asyncio import (
AsyncSession, async_sessionmaker, create_async_engine
)
from sqlalchemy.orm import DeclarativeBase
DATABASE_URL = "postgresql+asyncpg://postgres:secret@localhost:5432/production_db"
engine = create_async_engine(
DATABASE_URL,
echo=False,
pool_size=10,
max_overflow=20,
pool_pre_ping=True,
)
AsyncSessionLocal = async_sessionmaker(
bind=engine,
class_=AsyncSession,
expire_on_commit=False, # CRITICAL for async workflows!
autoflush=False
)
class Base(DeclarativeBase):
passSetting expire_on_commit=False is critical for async workflows. Leaving it at the default can cause model attribute access after a commit to trigger unwanted synchronous database calls, which disrupts async code. The pool_size and max_overflow values here are examples and should be tuned for your workload and deployment setup.
Defining Type-Safe Models with Mapped and mapped_column
SQLAlchemy 2.0 adds Mapped and mapped_column for fully typed models. This helps IDEs recognize attribute types and boosts code reliability.
from datetime import datetime
from typing import Optional, List
from sqlalchemy import String, ForeignKey, func
from sqlalchemy.orm import Mapped, mapped_column, relationship
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)
email: Mapped[str] = mapped_column(String(255), unique=True)
created_at: Mapped[datetime] = mapped_column(server_default=func.now())
posts: Mapped[List["Post"]] = relationship(
back_populates="author", cascade="all, delete-orphan"
)
class Post(Base):
__tablename__ = "posts"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(200))
content: Mapped[str]
author_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
author: Mapped["User"] = relationship(back_populates="posts")This setup brings static type checking and better editor support, making it easier to spot errors early in development.
Efficient Relationship Loading and Avoiding N+1 Queries
Async environments don't allow implicit lazy loading for relationships. To fetch related data efficiently, use selectinload() in your queries. This method runs a separate, optimized query for related objects and helps avoid N+1 query performance problems.
from sqlalchemy.orm import selectinload
async def get_user_with_posts(session: AsyncSession, user_id: int) -> Optional[User]:
stmt = (
select(User)
.where(User.id == user_id)
.options(selectinload(User.posts))
)
result = await session.execute(stmt)
return result.scalar_one_or_none()This approach loads related posts in a single extra query, instead of triggering a separate query for each post.
Managing Schema Changes with Alembic
For database migrations, Alembic works with async SQLAlchemy setups. After initializing migrations with alembic init -t async migrations, connect your Base.metadata in migrations/env.py:
from myapp.database import Base, DATABASE_URL target_metadata = Base.metadata
Generate migration scripts automatically and apply them to your database:
alembic revision --autogenerate -m "create users and posts tables" alembic upgrade head
This workflow keeps your schema aligned with your models as your application changes. When using async migrations, keep in mind that migration tooling may use an async database URL but still run migration operations through a synchronous bridge, and migration scripts themselves may not be fully asynchronous.
Further Learning and Practical Application
Learning async SQLAlchemy and Alembic opens up scalable, type-safe data layers for Python backends. For more on transaction management, connection pooling, and advanced configuration, see the original guide at taha-heidaryfard.ir. If you want to build up your Python basics before working with async ORM patterns, the function arguments guide gives a practical foundation for understanding how Python handles data and function calls.
Source: Adapted from the architectural summary at taha-heidaryfard.ir.
