Day 26 · ~13m●

Database Integration

Using Pydantic models for database reads and writes with model_validate and model_dump.

🧑‍💻

In a real app, data comes from a database — not just dictionaries. How does Pydantic fit into the database read/write cycle?

👩‍🏫

Pydantic models are perfect as an intermediate layer between your database and your application logic. Data flows in both directions:

Database Row → dict → Pydantic Model → Application
Application → Pydantic Model → dict → Database Insert

When reading, you validate database rows through a model. When writing, you dump the model to a dict for insertion:

from pydantic import BaseModel, Field

class UserDB(BaseModel):
    id: int
    name: str
    email: str
    is_active: bool = True

# Reading: database row → model
row = {"id": 1, "name": "Alice", "email": "alice@test.com", "is_active": True}
user = UserDB.model_validate(row)

# Writing: model → dict for insert
new_user = UserDB(id=2, name="Bob", email="bob@test.com")
insert_data = new_user.model_dump()
# {'id': 2, 'name': 'Bob', 'email': 'bob@test.com', 'is_active': True}

🧑‍💻

But when inserting, I usually don't want to include the id field — the database generates that. How do I handle that?

👩‍🏫

Create separate models for different operations:

class UserCreate(BaseModel):
    """For creating new users — no id field."""
    name: str = Field(min_length=1)
    email: str
    is_active: bool = True

class UserRead(BaseModel):
    """For reading users — includes id."""
    id: int
    name: str
    email: str
    is_active: bool

# Insert: validate input, dump without id
new_user = UserCreate(name="Alice", email="alice@test.com")
insert_data = new_user.model_dump()
# {'name': 'Alice', 'email': 'alice@test.com', 'is_active': True}

# Read: validate database row including id
row = {"id": 1, "name": "Alice", "email": "alice@test.com", "is_active": True}
user = UserRead.model_validate(row)

🧑‍💻

That's what FastAPI does, right? Separate request and response models?

👩‍🏫

Exactly. The pattern is sometimes called "Create/Read/Update" models. Each one defines what fields are valid for that operation:

class UserUpdate(BaseModel):
    """For updating users — all fields optional."""
    name: str | None = None
    email: str | None = None
    is_active: bool | None = None

# Only update fields that were provided
update = UserUpdate(name="Alice Smith")
changes = update.model_dump(exclude_unset=True)
# {'name': 'Alice Smith'}

exclude_unset=True only includes fields that were explicitly set. This maps perfectly to a SQL UPDATE — only change the columns that the user provided.

🧑‍💻

What about converting between database column names and Python field names? Like created_at in the DB vs createdAt in the API?

👩‍🏫

Use aliases for the external-facing model, and keep the database model matching column names:

class UserDBRow(BaseModel):
    """Matches database columns exactly."""
    id: int
    full_name: str
    created_at: str

class UserAPI(BaseModel):
    """Matches API response format."""
    id: int
    full_name: str = Field(alias="fullName")
    created_at: str = Field(alias="createdAt")

# DB → API conversion
row = {"id": 1, "full_name": "Alice", "created_at": "2026-01-01"}
db_user = UserDBRow.model_validate(row)
api_data = UserAPI.model_validate(db_user.model_dump())
print(api_data.model_dump(by_alias=True))
# {'id': 1, 'fullName': 'Alice', 'createdAt': '2026-01-01'}

This is the full pipeline: database rows validate through a DB model, transform into an API model, and serialize with the right field names for the client.

Practice your skills

Already have an account? Sign in