FastAPI + SQLAlchemy 2.0 Async Engine

Mental model

SQLAlchemy 2.0 introduces native async support over AsyncIO drivers (asyncpg). Combining FastAPI dependency injection with AsyncSession guarantees clean connection acquisition and teardown on every request tick.

HTTP Request Route

FastAPI Depends(get_async_session)

Acquire AsyncSession from async_sessionmaker

Execute async SQL with await session.execute()

Yield Teardown & Commit/Rollback

Conceptual teaching model synthesized from:FastAPI Framework Architecture & Dependency Injection Specification

Theory

When running async route handlers (async def), executing blocking synchronous ORM calls (session.query()) blocks Python's single-threaded event loop. AsyncEngine combined with asyncpg offloads database socket I/O to non-blocking epoll events.

python(36 lines)

1from typing import AsyncGenerator

2from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession

3from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

4from sqlalchemy import select

5from fastapi import FastAPI, Depends

7DATABASE_URL = "postgresql+asyncpg://user:pass@localhost:5432/app_db"

9engine = create_async_engine(DATABASE_URL, pool_size=20, max_overflow=10)

10AsyncSessionFactory = async_sessionmaker(engine, expire_on_commit=False)

12class Base(DeclarativeBase):

13 pass

15class User(Base):

16 __tablename__ = "users"

17 id: Mapped[int] = mapped_column(primary_key=True)

18 username: Mapped[str]

20async def get_db_session() -> AsyncGenerator[AsyncSession, None]:

21 async with AsyncSessionFactory() as session:

22 try:

23 yield session

24 await session.commit()

25 except Exception:

26 await session.rollback()

27 raise

29app = FastAPI()

31@app.get("/users/{user_id}")

32async def get_user(user_id: int, db: AsyncSession = Depends(get_db_session)):

33 result = await db.execute(select(User).where(User.id == user_id))

34 user = result.scalar_one_or_none()

35 return {"user": user.username if user else None}

16 lines hidden

Alternatives and trade-offs

Synchronous ORM (psycopg2): Simple; blocks AsyncIO event loop unless wrapped in run_in_executor.
SQLAlchemy 2.0 Async (asyncpg): High concurrency non-blocking I/O; requires explicit await on all DB operations and lazy loading handling.

Failure modes and misconceptions

Implicit Lazy Loading Crashes: Accessing un-loaded ORM relationships (user.orders) in AsyncIO raises MissingGreenlet error. Use explicit joined loads (options(joinedload(User.orders))).
expire_on_commit=True Overhead: Default SQLAlchemy setting expires object attributes on commit, forcing secondary async DB fetches when accessing object attributes after commit. Always set expire_on_commit=False.

Reflect before revealing the guide

Decision scenario

Always set expire_on_commit=False on async_sessionmaker and use explicit eager loading (joinedload/selectinload) to avoid MissingGreenlet errors in FastAPI async routes.

Learning outcomes

Build non-blocking FastAPI database integration with SQLAlchemy 2.0 and asyncpg.
Manage AsyncSession lifecycles safely using FastAPI dependency injection.
Prevent MissingGreenlet errors using explicit eager loading strategies.

Trade-offs

SQLAlchemy 2.0 Async delivers massive concurrency under high HTTP load, but requires disciplined handling of relationship loading and explicit session scopes.

FastAPI + SQLAlchemy 2.0 Async Engine

Key Learning Outcomes

Mental model

Theory

Alternatives and trade-offs

Failure modes and misconceptions

Decision scenario

Learning outcomes

Trade-offs

Prerequisites & Related Concepts (2)

Private notes

Theory and decision mastery

Which dependency pattern guarantees clean AsyncSession closing even if route handlers raise uncaught exceptions?