If you have worked with Python ORMs over the last decade, you remember the traditional SQLAlchemy 1.x patterns: query objects attached to sessions, string-based relationships, implicit lazy loading, and blocking I/O calls that froze your async event loop.
With SQLAlchemy 2.0, the entire ecosystem went through a massive generational overhaul. It brought first-class async support, full type safety conforming to PEP 484, and a unified query syntax built around select().
When combined with Alembic for schema migrations, it gives Python backends a robust, enterprise-grade data layer.
💡 Note: This article is an adapted architectural summary. For the exhaustive, step-by-step masterclass covering deep engine configurations, transaction isolation, and pool tuning, read the original guide: Complete Guide to SQLAlchemy 2.0 and Alembic in Python (Modern Async ORM) on taha-heidaryfard.ir.
1. What Makes SQLAlchemy 2.0 Different?
In legacy SQLAlchemy 1.4, writing queries often looked like this:
# Legacy 1.x style (deprecated in 2.0)
user = session.query(User).filter(User.email == "dev@example.com").first()
SQLAlchemy 2.0 eliminates session.query() in favor of standard SQL-like expressions using select(). This ensures consistent query building across ORM entities and Core tables, full static analysis support for mypy and Pyright, and native async execution:
# Modern 2.0 style
stmt = select(User).where(User.email == "dev@example.com")
result = await session.execute(stmt)
user = result.scalar_one_or_none()
2. Setting Up an Async Engine & Session
To work asynchronously with PostgreSQL (or MySQL/SQLite), you need an asynchronous DB driver like asyncpg:
pip install sqlalchemy[asyncio] asyncpg alembic
Here is the production-ready configuration for initializing your asynchronous session factory:
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):
pass
Why expire_on_commit=False is Mandatory in Async
In synchronous SQLAlchemy, after you commit a session, SQLAlchemy expires all model attributes so they re-fetch fresh state on the next read. In an async environment, trying to read an expired attribute triggers implicit synchronous lazy-loading, which raises an asyncio greenlet error. Setting expire_on_commit=False avoids this entirely.
3. Defining Models with Mapped and mapped_column
SQLAlchemy 2.0 introduces fully typed declarative models:
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())
# 1-to-Many Relationship
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")
Your IDE now understands the exact Python types of user.id, user.username, and user.created_at without external stub plugins!
4. Eager Loading in Async (Preventing N+1 Queries)
Because lazy loading does not work asynchronously without special wrappers, you must explicitly declare how relationships are fetched. The best approach is selectinload():
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()
selectinload executes a fast, indexed secondary SELECT ... WHERE user_id IN (...), preventing both Cartesian explosion (from joinedload) and lazy loading failures.
5. Integrating Alembic with Async SQLAlchemy
When running alembic init -t async migrations, Alembic creates an async-compatible template. In your migrations/env.py, simply wire up your Base.metadata:
from myapp.database import Base, DATABASE_URL
target_metadata = Base.metadata
Then generate and apply migrations seamlessly:
# Auto-generate migration script from model changes
alembic revision --autogenerate -m "create users and posts tables"
# Apply to database
alembic upgrade head
Deep-Dive & Next Steps
Adopting SQLAlchemy 2.0 with async sessions provides the optimal balance between high-concurrency throughput and robust type safety.
👉 Read the complete, in-depth tutorial on my blog:
Complete Guide to SQLAlchemy 2.0 and Alembic in Python (Modern Async ORM)
The full article covers:
- Full transaction management patterns and dependency injection with FastAPI
- Connection pool exhaustion prevention under high load
- Multi-environment Alembic configuration (
development,staging,production) - Comprehensive FAQ addressing common pitfalls and performance bottlenecks
Originally published at taha-heidaryfard.ir.
Top comments (0)