DEV Community

Mohammad Taha Heidarifard
Mohammad Taha Heidarifard

Posted on Originally published at taha-heidaryfard.ir

Mastering Modern Async SQLAlchemy 2.0 & Alembic in Python (A Pragmatic Guide)

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()
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)