Async SQLAlchemy — asyncpg and AsyncSession
Advancedcreate_async_engine + AsyncSession + asyncpg make DB calls awaitable, so the event loop serves other requests during queries — but lazy loading breaks in async, making eager loading mandatory.
Overview
A sync DB call inside an async def endpoint blocks the event loop — every in-flight request stalls behind your query (FastAPI protects plain def endpoints by running them in a thread pool, but async def gets no such net). The async stack — create_async_engine with postgresql+asyncpg://, async_sessionmaker, and an async get_db — makes every DB operation a real await point, letting one worker overlap hundreds of I/O-bound requests. The price is discipline: implicit lazy loading cannot await, so touching an unloaded relationship raises MissingGreenlet at runtime — eager loading via selectinload stops being an optimization and becomes a correctness requirement. Choose async DB when the endpoint mixes DB with other awaits or concurrency is high; a plain-def CRUD service on the thread pool is often the simpler, equally fast choice.
The Async Stack — Engine, Session, Dependency
Same architecture as sync, async at every step: await execute, await commit, and an async-generator get_db. Note the driver in the URL — asyncpg, not psycopg2.
# pip install sqlalchemy[asyncio] asyncpg
# ── app/core/db.py ──────────────────────────
from sqlalchemy.ext.asyncio import (
AsyncSession, async_sessionmaker, create_async_engine,
)
engine = create_async_engine(
"postgresql+asyncpg://app:app@localhost:5432/placement", # ← asyncpg driver
pool_size=5, max_overflow=10, pool_pre_ping=True,
)
AsyncSessionLocal = async_sessionmaker(bind=engine, expire_on_commit=False)
async def get_db():
async with AsyncSessionLocal() as db: # async context manager closes it
try:
yield db
await db.commit()
except Exception:
await db.rollback()
raise
# ── endpoints: await every DB touch ─────────
from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy import select
app = FastAPI()
@app.get("/students/{student_id}")
async def get_student(student_id: int, db: AsyncSession = Depends(get_db)):
student = await db.get(Student, student_id)
if not student:
raise HTTPException(404, "student not found")
return {"id": student.id, "name": student.name}
@app.get("/students")
async def search(branch: str, db: AsyncSession = Depends(get_db)):
result = await db.execute(
select(Student).where(Student.branch == branch).limit(20)
)
return [{"id": s.id, "name": s.name} for s in result.scalars()]
# While these awaits wait on Postgres, the SAME worker serves other requests.The Lazy-Loading Trap + When Async DB Is Worth It
Accessing an unloaded relationship needs a query, and property access cannot await — MissingGreenlet at runtime. Eager-load everything you will touch. Then the honest decision table.
from sqlalchemy.orm import selectinload
@app.get("/students/{student_id}/offers")
async def offers(student_id: int, db: AsyncSession = Depends(get_db)):
student = await db.get(Student, student_id)
return [o.company for o in student.offers] # ✗ MissingGreenlet!
# .offers is unloaded; loading needs SQL; property access can't await
# ✓ Eager-load what you'll touch — correctness, not optimization, in async:
@app.get("/students/{student_id}/offers")
async def offers_ok(student_id: int, db: AsyncSession = Depends(get_db)):
result = await db.execute(
select(Student)
.options(selectinload(Student.offers))
.where(Student.id == student_id)
)
student = result.scalar_one_or_none()
if not student:
raise HTTPException(404, "student not found")
return [o.company for o in student.offers] # loaded — safe
# (expire_on_commit=False above: without it, attribute access after
# commit triggers a refresh query → same MissingGreenlet issue.)
# Sync or async DB? The honest table:
# async def + async DB → high concurrency; endpoint also awaits
# HTTP/cache/queue alongside the DB
# plain def + sync DB → CRUD services; runs in the thread pool;
# simpler, no greenlet traps, plenty fast
# async def + SYNC DB → ✗ NEVER: blocks the loop for everyone
# Mixed apps are fine: def where simple, async def where it pays.Key Points to Remember
- 1Sync DB calls inside async def block the event loop for every request
- 2Stack: postgresql+asyncpg:// + async_sessionmaker + async get_db, await everything
- 3Lazy loading raises MissingGreenlet in async — selectinload is mandatory
- 4expire_on_commit=False; plain def + sync DB (thread pool) is a legitimate choice
Interview Questions
Sign in to ask AriaWhat happens if you call a psycopg2 query inside an async def endpoint under load?
Why does lazy loading fail with AsyncSession, and what replaces it?
Your service is simple CRUD at moderate traffic — argue for or against going async-DB.
Ask Aria about Async SQLAlchemy — asyncpg and AsyncSession
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.