FastAPI + Pydantic + SQLAlchemy: The Modern Python Stack

I get asked about my stack at least once a month. Usually it's from someone who tried to build their first API and ended up with files scattered everywhere. Here's the payoff: a clean, repeatable layout you can copy without inviting future-you to a fistfight.

This combo shows up in my projects a lot: FastAPI for the API part, Pydantic for the validation bouncer, and SQLAlchemy for the database bits. It starts as "one health endpoint." Then it turns into something that pays salaries.

Here's the structure up front, so you don't have to read this twice:

• schemas.py: validate and shape input/output
• models.py: define what gets stored
• crud.py: isolate database operations
• deps.py: wire up the DB session (one way)
• routes/main.py: stay thin; call CRUD; return schemas

A layout that won't betray you

app/
  main.py
  db.py
  models.py
  schemas.py
  crud.py
  deps.py
tests/
  test_users.py

One DB session path (because late-night pager duty)

You want one way to get a DB session. Why? Because production o'clock.

Also: SQLite for dev, Postgres for prod (swap DATABASE_URL; the code doesn't care, only your infra does).

db.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, DeclarativeBase

DATABASE_URL = "sqlite:///./app.db"
engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)

class Base(DeclarativeBase): ...

deps.py

from .db import SessionLocal

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Why the ceremony? Because the most common failure mode I keep seeing is: session leaks + globals + "it works on my laptop." Under traffic, those "helpful" globals turn into a haunted house. You don't need that.

Models + schemas (store vs. validate)

models.py (SQLAlchemy: what lives in the database)

from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column
from .db import Base

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String, unique=True, index=True)

schemas.py (Pydantic: what's allowed in/out)

from pydantic import BaseModel, EmailStr, ConfigDict

class UserCreate(BaseModel):
    email: EmailStr

class UserOut(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: int
    email: EmailStr

Boring CRUD (the dream)

CRUD is deliberately dull. Functions in, objects out. No magic. No "clever." Clever is fun until you're debugging with one eye open.

crud.py

from sqlalchemy.orm import Session
from . import models, schemas

def create_user(db: Session, user: schemas.UserCreate):
    obj = models.User(email=user.email)
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj

def get_user_by_email(db: Session, email: str):
    return db.query(models.User).filter(models.User.email == email).first()

Routes stay thin (they should feel a little boring)

main.py

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from .db import Base, engine
from .deps import get_db
from . import crud, schemas

Base.metadata.create_all(bind=engine)
app = FastAPI()

@app.post("/users", response_model=schemas.UserOut)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    if crud.get_user_by_email(db, user.email):
        raise HTTPException(400, "Email already registered")
    return crud.create_user(db, user)

A quick "how this saves you" story (and the fix)

I got pulled into a "simple" API that kept failing under load. Not locally. Not in staging. Only when real users showed up, like it was a special guest villain on an old Saturday morning cartoon.

The cause was boring: leaked sessions + global session objects + inconsistent DB access patterns.

The fix was also boring, and that's the point:

• DI DB sessions (get_db), no globals
• CRUD functions for all DB access
• explicit Pydantic schemas for input/output

After that, debugging stopped feeling like a prank.

Testing (override the DB session once, then move on)

In tests, override the DB session dependency so tests don't share state.

Here's a minimal pytest fixture skeleton that uses an in-memory SQLite database per test session. (You can make it per-test if you want even more isolation.)

tests/conftest.py

import pytest
from fastapi.testclient import TestClient
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from app.main import app
from app.db import Base
from app.deps import get_db

@pytest.fixture
def client():
    engine = create_engine(
        "sqlite+pysqlite:///:memory:",
        connect_args={"check_same_thread": False},
    )
    TestingSessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False)

    Base.metadata.create_all(bind=engine)

    def override_get_db():
        db = TestingSessionLocal()
        try:
            yield db
        finally:
            db.close()

    app.dependency_overrides[get_db] = override_get_db
    with TestClient(app) as c:
        yield c
    app.dependency_overrides.clear()

tests/test_users.py

def test_create_user(client):
    r = client.post("/users", json={"email": "a@example.com"})
    assert r.status_code == 200
    assert r.json()["email"] == "a@example.com"

If you want to go down the rabbit hole, here are the official spellbooks:

• FastAPI docs: https://fastapi.tiangolo.com/
• SQLAlchemy ORM: https://docs.sqlalchemy.org/en/20/orm/
• Pydantic: https://docs.pydantic.dev/

This is the smallest version I've shipped without regretting it. Add Alembic for migrations once your schema starts changing daily. And it will. Usually right after someone says, "We're done with the database."

Sethers