Databases With FastAPI

Bring your own data layer

FastAPI has no built-in ORM. Common choices are SQLAlchemy 2.x (sync or async sessions), SQLModel (SQLAlchemy plus Pydantic models in one class), or async drivers directly; Alembic handles migrations. Provide sessions through a yield dependency, keep transactions short, and validate input with Pydantic before it reaches the database. For simple CRUD on a relational model with an admin, Django may be the faster path.

A session dependency with SQLAlchemy (sketch)

Not run here; check the framework documentation for your version.

from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session

engine = create_engine("postgresql+psycopg://shop@db/shop", pool_pre_ping=True)

def get_session():
    with Session(engine) as session:     # closed automatically after the request
        yield session

@app.get("/products/{pid}")
def read(pid: int, session: Annotated[Session, Depends(get_session)]):
    product = session.get(Product, pid)
    if product is None:
        raise HTTPException(404, "Not found")
    return product

Use Alembic for schema changes

Generate migrations with alembic revision --autogenerate, review them, and apply them in deployment.

Quick check: Which tool commonly manages schema migrations for SQLAlchemy?

• Alembic
• Django admin
• Pydantic
• uvicorn