Data Modeling with Pydantic & SQLAlchemy

Food Tracker (FastAPI)

Chapter 2 · Data Modeling with Pydantic & SQLAlchemy

Every course in this quartet stores the same shape of data. This course expresses it as two genuinely separate models — one for what actually lives in the database, one for what a request is allowed to send.

The SQLAlchemy Model: Storage

# models.py
from sqlalchemy import Column, Integer, String, Date, DateTime, func
from database import Base

class Item(Base):
    __tablename__ = "items"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    barcode = Column(String, nullable=True)
    category = Column(String, nullable=True)
    expiry_date = Column(Date, nullable=True)
    status = Column(String, nullable=False, default="active")
    added_at = Column(DateTime, server_default=func.now())
    used_at = Column(DateTime, nullable=True)

The same design decisions every course in this quartet already made: expiry_date nullable, cleared rather than the row deleted once an item is used; added_at stamped by the database itself via server_default=func.now(), never trusted from client input.

The Pydantic Schemas: Request & Response Shape

# schemas.py
from pydantic import BaseModel
from datetime import date, datetime
from typing import Optional

class ItemCreate(BaseModel):
    name: str
    barcode: Optional[str] = None
    category: Optional[str] = None
    expiry_date: Optional[date] = None

class ItemResponse(BaseModel):
    id: int
    name: str
    barcode: Optional[str]
    category: Optional[str]
    expiry_date: Optional[date]
    status: str
    added_at: datetime

    class Config:
        from_attributes = True  # lets this schema read directly from a SQLAlchemy object

ItemCreate deliberately has no id, no status, and no added_at field at all — not because the client sends them and they're ignored, but because they genuinely don't exist as fields the schema will accept. A request body containing a status field is simply rejected as an unexpected field, before any route handler code runs.

A different validation model than this quartet's other courses

Food Tracker (React + Express)'s own add-item route had to hand-write real checks — if (!name || typeof name !== "string" || !name.trim()) — inside the route handler's own body, and its own finding-box named that server-side check as the app's real gate. Here, the equivalent gate is the ItemCreate class itself: declaring item: ItemCreate as a route parameter means FastAPI validates the incoming JSON against that schema automatically, rejecting anything that doesn't match with a structured 422 response — the route handler's own code never even runs for a malformed request. Django's ModelForm sits somewhere between these two: one class handling both storage and form validation together, rather than SQLAlchemy and Pydantic's clean two-layer split.

Database Setup

# database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

engine = create_engine("sqlite:///./foodtracker.db")
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

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

get_db is written as a generator specifically so FastAPI's own dependency injection can use it — every route that needs a database session declares db: Session = Depends(get_db) and gets one automatically opened and closed around that single request, without writing that setup/teardown logic in every route by hand.

A SQLAlchemy object and a Pydantic object are not the same thing

Returning an Item instance (the SQLAlchemy model) directly from a route declared to return ItemResponse only works because from_attributes = True tells Pydantic how to read attributes off a non-dict object. Without it, FastAPI would fail trying to serialize the SQLAlchemy object directly — a genuinely common early mistake, conflating "a Python object representing a database row" with "a Python object representing a validated API response," which look similar but are built and behave very differently.

Optional[date] = None reads exactly like the column it represents

Compare this to Food Tracker (React + Express)'s own need to explicitly write expiryDate || null before sending a request, specifically to avoid an empty string reaching the database where a true NULL was expected. Optional[date] = None expresses the same "not required, genuinely absent" idea directly in the schema's own type — there's no separate normalization step to remember, because the type itself only ever admits a real date or nothing at all.

Where This Course Is Headed

Barcode lookup next — a FastAPI endpoint proxying Open Food Facts, reusing this chapter's own Pydantic/SQLAlchemy split for the lookup cache.

Hands-On Exercises

Exercise 1

Explain why ItemCreate has no id, status, or added_at fields, and what happens to a request body that includes a status field anyway.

📄 View solution

Exercise 2

Explain the difference between this chapter's validation approach and Food Tracker (React + Express)'s own hand-written validation checks, specifically in terms of whether route handler code ever runs for a malformed request.

📄 View solution

Exercise 3

Explain what from_attributes = True actually does, and what would go wrong if a route tried to return a SQLAlchemy Item instance from a route declared to return ItemResponse without it.

📄 View solution

Chapter 2 Quick Reference

Two separate layers: Item (SQLAlchemy, storage) and ItemCreate/ItemResponse (Pydantic, request/response validation)
ItemCreate: only fields a client may set — no id, status, or added_at, by design, not by convention
Real advantage: a malformed request is rejected by Pydantic before route handler code ever runs, unlike the Express sibling's own hand-written checks
from_attributes = True: lets a Pydantic schema read directly from a SQLAlchemy object — required, not automatic
get_db(): a generator dependency FastAPI uses to open/close a database session per request
Optional[date] = None: the type itself expresses "genuinely absent," no separate normalization step needed
Next chapter: Barcode Lookup: Integrating Open Food Facts