Introduction
FastAPI is async-native, but if your database calls are synchronous (standard SQLAlchemy), you block the event loop. Async SQLAlchemy 2.0 fixes this — non-blocking DB operations that scale to thousands of concurrent requests.
This post builds a production async backend with FastAPI + SQLAlchemy 2.0 + Alembic.
Setup
pip install fastapi uvicorn sqlalchemy[asyncio] asyncpg alembic
asyncpg is the async Postgres driver. For SQLite (dev), use aiosqlite.
Step 1: Async engine and session
# database.py
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.orm import DeclarativeBase
DATABASE_URL = "postgresql+asyncpg://user:pass@localhost/db"
engine = create_async_engine(DATABASE_URL, echo=True)
AsyncSessionLocal = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)
class Base(DeclarativeBase):
pass
async def get_db():
async with AsyncSessionLocal() as session:
yield session
expire_on_commit=False is critical for async — it prevents lazy-loading errors after commit.
Step 2: Model
# models.py
from sqlalchemy import String, Integer, DateTime, func
from sqlalchemy.orm import Mapped, mapped_column
from database import Base
from datetime import datetime
class Note(Base):
__tablename__ = "notes"
id: Mapped[int] = mapped_column(Integer, primary_key=True)
title: Mapped[str] = mapped_column(String(200))
body: Mapped[str] = mapped_column(String, default="")
created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
SQLAlchemy 2.0 uses type-annotated Mapped[] columns — cleaner and type-safe.
Step 3: Pydantic schemas
# schemas.py
from pydantic import BaseModel
from datetime import datetime
class NoteCreate(BaseModel):
title: str
body: str = ""
class NoteRead(BaseModel):
id: int
title: str
body: str
created_at: datetime
class Config:
from_attributes = True # allows ORM → Pydantic conversion
Step 4: CRUD endpoints (async)
# main.py
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from database import get_db, Base, engine
from models import Note
from schemas import NoteCreate, NoteRead
app = FastAPI()
@app.on_event("startup")
async def startup():
async with engine.begin() as conn:
await conn.run_sync(Base.metadata.create_all)
@app.get("/notes", response_model=list[NoteRead])
async def list_notes(db: AsyncSession = Depends(get_db)):
result = await db.execute(select(Note).order_by(Note.created_at.desc()))
return result.scalars().all()
@app.post("/notes", response_model=NoteRead, status_code=201)
async def create_note(note: NoteCreate, db: AsyncSession = Depends(get_db)):
db_note = Note(**note.model_dump())
db.add(db_note)
await db.commit()
await db.refresh(db_note)
return db_note
@app.get("/notes/{note_id}", response_model=NoteRead)
async def get_note(note_id: int, db: AsyncSession = Depends(get_db)):
result = await db.execute(select(Note).where(Note.id == note_id))
note = result.scalar_one_or_none()
if not note:
raise HTTPException(404, "Not found")
return note
@app.delete("/notes/{note_id}", status_code=204)
async def delete_note(note_id: int, db: AsyncSession = Depends(get_db)):
result = await db.execute(select(Note).where(Note.id == note_id))
note = result.scalar_one_or_none()
if not note:
raise HTTPException(404, "Not found")
await db.delete(note)
await db.commit()
Every DB call is awaited — the event loop is never blocked.
Step 5: Alembic migrations
alembic init alembic
Edit alembic.ini:
sqlalchemy.url = postgresql+asyncpg://user:pass@localhost/db
Edit alembic/env.py:
from database import Base
target_metadata = Base.metadata
Create and apply migrations:
alembic revision --autogenerate -m "create notes table"
alembic upgrade head
Step 6: Testing (async)
import pytest
from httpx import AsyncClient
from main import app
@pytest.mark.asyncio
async def test_create_note():
async with AsyncClient(app=app, base_url="http://test") as client:
r = await client.post("/notes", json={"title": "Test", "body": "Hello"})
assert r.status_code == 201
assert r.json()["title"] == "Test"
@pytest.mark.asyncio
async def test_list_notes():
async with AsyncClient(app=app, base_url="http://test") as client:
r = await client.get("/notes")
assert r.status_code == 200
Use httpx.AsyncClient for async tests. pytest-asyncio handles the event loop.
Step 7: Connection pooling
engine = create_async_engine(
DATABASE_URL,
echo=False,
pool_size=20, # max connections in pool
max_overflow=10, # extra connections beyond pool_size
pool_timeout=30, # wait for connection
pool_recycle=3600, # recycle connections every hour
)
Tune based on your DB’s connection limit and traffic.
Step 8: Relationships (async)
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, Mapped
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
notes: Mapped[list["Note"]] = relationship(back_populates="owner")
class Note(Base):
__tablename__ = "notes"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str]
owner_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
owner: Mapped["User"] = relationship(back_populates="notes")
In async, avoid lazy loading — it triggers sync DB calls. Use selectinload or joinedload:
from sqlalchemy.orm import selectinload
result = await db.execute(
select(Note).options(selectinload(Note.owner)).where(Note.id == note_id)
)
note = result.scalar_one_or_none()
Common mistakes
- Lazy loading in async — triggers sync DB calls, blocks the event loop. Use
selectinload/joinedload. - Forgetting
expire_on_commit=False— causesMissingGreenleterrors after commit. - Sync DB calls in async endpoints —
db.query()blocks. Always useawait db.execute(). - Not using connection pooling — default pool is small. Tune for production.
- Mixing sync and async sessions — pick one. Don’t use sync
Sessionin async code.
Key Concepts
- Async engine =
create_async_enginewith asyncpg/aiosqlite - Async session =
AsyncSessionwithawaitfor all operations Mapped[]= type-annotated columns (SQLAlchemy 2.0)selectinload= eager loading for relationships in async- Alembic = database migrations
expire_on_commit=False= prevent lazy-load errors
What to Try Next
- Add auth with JWT.
- Build a Streamlit frontend that calls this API.
- Add Redis caching for read-heavy endpoints.
- Deploy with Docker.
FAQ
Async SQLAlchemy vs sync — when to use which?
Use async when you have high concurrency (many simultaneous requests) and I/O-bound work. Use sync for simpler apps, background tasks, or when your DB driver doesn’t support async. For most web APIs, async is better.
SQLAlchemy 2.0 vs 1.4 — what changed?
2.0 uses Mapped[] type annotations, select() instead of Query, and has better async support. The migration is mostly mechanical. New projects should use 2.0.
Can I use async SQLAlchemy with SQLite?
Yes — use aiosqlite as the driver: create_async_engine("sqlite+aiosqlite:///db.sqlite"). Good for dev/testing. Use Postgres (asyncpg) for production.