Tech With Galvan
Back to Articles
DevelopmentMar 21, 2027 • 6 min read

FastAPI + Async SQLAlchemy: High-Performance Async Backend

Build a high-performance async backend with FastAPI and SQLAlchemy 2.0 — async engine, sessions, CRUD, and Alembic migrations.

Galvan
Galvan

Founder & Creator

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 — causes MissingGreenlet errors after commit.
  • Sync DB calls in async endpoints — db.query() blocks. Always use await db.execute().
  • Not using connection pooling — default pool is small. Tune for production.
  • Mixing sync and async sessions — pick one. Don’t use sync Session in async code.

Key Concepts

  • Async engine = create_async_engine with asyncpg/aiosqlite
  • Async session = AsyncSession with await for 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

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.