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.
