Connecting Pydantic Schemas to Database Models

You've built a Pydantic schema that validates a UserCreate payload, and you've defined a SQLAlchemy User model that maps to a table. But when a POST request hits your FastAPI endpoint, the data sits in a schema object while your database layer expects a model instance — and the two don't speak the same language. This mismatch is the friction that slows down FastAPI developers more than any other. In this lesson, you'll learn the cleanest way to connect Pydantic schemas to database models, so your API can validate, persist, and return data without manual field-mapping spaghetti.

The problem this lesson solves

Imagine you have a UserCreate schema with email and password, and a User model with id, email, hashed_password, and created_at. If you naively pass the schema to a session, SQLAlchemy will error because the schema isn't an ORM instance — and fields like hashed_password don't even exist in the schema. Instead, you end up writing repetitive code like this:

user = User(
    email=user_data.email,
    hashed_password=hash_password(user_data.password)
)

That works for one field, but it doesn't scale. Add first_name, last_name, and phone, and the manual mapping becomes a maintenance nightmare. Every schema change forces you to hunt down and update every endpoint. You also risk subtle bugs: a typo in a field name silently sets an attribute to None, and you only discover it when the database throws a NOT NULL constraint error.

The deeper problem is the separation of concerns. Pydantic schemas are for input validation (and sometimes output serialization). Database models are for persistence. They have different jobs, different fields, and different lifecycle rules. Without a deliberate bridge, the two layers leak into each other — either your models start carrying validation logic or your schemas start dictating database structure.

Core concept / mental model

Think of your API as a sandwich: the Pydantic schema is the bread (the interface with the outside world), the database model is the filling (the persistent truth), and the translation layer is the condiment that binds them together.

In FastAPI, that translation happens in your endpoint functions. You receive a schema, convert it to a model, and use SQLAlchemy to persist it. The key distinction is Pydantic's .model_dump() method (formerly .dict()). This method converts a schema instance into a plain Python dictionary — the launching pad for creating a model instance. You can pass that dictionary directly to the model's constructor using ** unpacking, then add or modify fields like id or hashed_password that don't exist in the schema.

Here's the mental model:

1. Schema in — validated, clean data arrives as a Pydantic object.
2. Dictionary bridge — .model_dump() gives you a plain dict of validated fields.
3. Model out — the dict (with extra fields added) feeds into the ORM constructor.
4. Persist — the session commits and refreshes, giving you a model with generated fields.

This pattern is sometimes called the schema-to-model mapper — a small, reusable function that you can call from every endpoint.

How it works step by step

Let's walk through the lifecycle of a POST request end-to-end.

Step 1: Define your Pydantic schema

Your schema declares what fields the client may send. Use Field() to add validation constraints. Note: never include server-generated fields like id or created_at in the input schema — they'll be added later.

from pydantic import BaseModel, EmailStr, Field

class UserCreate(BaseModel):
    email: EmailStr
    password: str = Field(min_length=8, max_length=128)
    first_name: str | None = None
    last_name: str | None = None

Step 2: Define your database model

The model mirrors the database table. It includes every column, even ones the client never sends. Notice hashed_password and created_at are here but not in the schema.

from sqlalchemy import Column, Integer, String, DateTime, func
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String, nullable=False)
    first_name = Column(String, nullable=True)
    last_name = Column(String, nullable=True)
    created_at = Column(DateTime, server_default=func.now())

Step 3: Write the translation function

The bridge between schema and model. It takes a UserCreate schema and returns a User model instance. This function centralizes the mapping — change it once, and all endpoints benefit.

from passlib.context import CryptContext

pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

def create_user_model(data: UserCreate) -> User:
    # Convert schema to dict, then unpack into the model constructor
    user_dict = data.model_dump()
    # Replace plain password with a hash — never store raw!
    user_dict["hashed_password"] = pwd_context.hash(user_dict.pop("password"))
    return User(**user_dict)

Step 4: Use the bridge in your endpoint

The endpoint is now clean: validate → translate → persist → return. You never touch the mapping logic again.

from fastapi import FastAPI, Depends, HTTPException, status
from sqlalchemy.orm import Session

app = FastAPI()

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

@app.post("/users/", response_model=UserRead, status_code=status.HTTP_201_CREATED)
def create_user(user: UserCreate, db: Session = Depends(get_db)):
    # Check for duplicate email before translation
    existing = db.query(User).filter(User.email == user.email).first()
    if existing:
        raise HTTPException(status_code=409, detail="Email already registered")

    # Translation and persistence
    db_user = create_user_model(user)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)  # Ensure id and created_at are populated

    return db_user

Step 5: Separate response schemas

Notice the response uses UserRead, not UserCreate. This is critical — you might return hashed_password or omit created_at based on the client's needs. Define separate output schemas with model_config = ConfigDict(from_attributes=True) so FastAPI can serialize ORM objects directly. We'll cover this in the hands-on section.

Hands-on walkthrough

Let's build a minimal but complete FastAPI app that connects Pydantic schemas to database models. You'll see the full flow from request to database to response.

Prerequisites: pip install fastapi uvicorn sqlalchemy pydantic[email] passlib[bcrypt]

Complete example: User signup with translation

from fastapi import FastAPI, Depends, HTTPException, status
from pydantic import BaseModel, EmailStr, Field, ConfigDict
from sqlalchemy import create_engine, Column, Integer, String, DateTime, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from passlib.context import CryptContext

# --- Database setup ---
SQLALCHEMY_DATABASE_URL = "sqlite:///./test.db"
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String, nullable=False)
    first_name = Column(String, nullable=True)
    last_name = Column(String, nullable=True)
    created_at = Column(DateTime, server_default=func.now())

Base.metadata.create_all(bind=engine)

# --- Pydantic schemas ---
class UserCreate(BaseModel):
    email: EmailStr
    password: str = Field(min_length=8)
    first_name: str | None = None
    last_name: str | None = None

class UserRead(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: int
    email: EmailStr
    first_name: str | None = None
    last_name: str | None = None
    created_at: datetime | None = None

# --- Translation bridge ---
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

def create_user_model(data: UserCreate) -> User:
    user_dict = data.model_dump()
    user_dict["hashed_password"] = pwd_context.hash(user_dict.pop("password"))
    return User(**user_dict)

# --- App and endpoints ---
app = FastAPI()

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

@app.post("/users/", response_model=UserRead, status_code=201)
def create_user(user: UserCreate, db: Session = Depends(get_db)):
    existing = db.query(User).filter(User.email == user.email).first()
    if existing:
        raise HTTPException(status_code=409, detail="Email already registered")
    db_user = create_user_model(user)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

Expected output when you run uvicorn main:app --reload and POST a valid user:

{
  "id": 1,
  "email": "ada@example.com",
  "first_name": "Ada",
  "last_name": "Lovelace",
  "created_at": "2025-01-15T10:30:00Z"
}

The response includes id and created_at even though the client never sent them, and it never exposes the password hash.

Example 2: Updating with the same pattern

Updates follow the same principle — convert schema to dict, build the model, merge, and save. Use model_dump(exclude_unset=True) to update only fields the client actually sent.

@app.patch("/users/{user_id}", response_model=UserRead)
def update_user(user_id: int, user_update: UserUpdate, db: Session = Depends(get_db)):
    db_user = db.get(User, user_id)
    if not db_user:
        raise HTTPException(status_code=404, detail="User not found")

    # Only include fields that were provided in the request
    update_data = user_update.model_dump(exclude_unset=True)
    for field, value in update_data.items():
        setattr(db_user, field, value)

    db.commit()
    db.refresh(db_user)
    return db_user

Pro tip: exclude_unset=True is a lifesaver for PATCH endpoints — it prevents unintentionally setting fields to None when the client omits them.

Example 3: Using model_validate for responses

FastAPI can serialize ORM objects directly when your response schema has from_attributes=True. But you can also explicitly convert a model to a schema using model_validate:

from pydantic import TypeAdapter

def serialize_user(user: User) -> UserRead:
    return UserRead.model_validate(user)

This is useful when you need to manipulate data before returning, like adding a computed field.

Compare options / when to choose what

When to choose what:

• For simple CRUD where schema fields map 1-to-1 to model attributes, use model_dump() + unpacking with a small helper.
• For complex transformations (hashing, nested objects, computed fields), build a dedicated mapper function like create_user_model.
• For response serialization, always prefer a separate UserRead schema with from_attributes=True over returning raw models.

Troubleshooting & edge cases

"AttributeError: 'UserCreate' object has no attribute 'hashed_password'"

You tried to pass the schema directly to User(...). Fix: always call model_dump() first and modify the dict.

"Field 'id' doesn't have a default value" on insert

You included id in the schema or passed it manually. Fix: remove id from the schema and let the database generate it. After commit, call db.refresh() to populate it.

"ValueError: missing table name"

You forgot __tablename__ or the Base import. Fix: ensure your model inherits from the same Base you used to create the engine.

"None is not allowed" validation error on PATCH

Using model_dump() without exclude_unset=True sets omitted fields to None. Fix: use exclude_unset=True for partial updates.

Password stored as plain text

You forgot to hash before inserting. Never store raw passwords — always replace the password key in the dict before constructing the model.

What you learned & what's next

You now understand the core idea behind connecting Pydantic schemas to database models: separate input validation from persistence, use model_dump() as the bridge, and centralize mapping logic. You completed a hands-on exercise that takes a UserCreate schema, translates it into a User model, persists it to SQLite, and returns a clean UserRead response. You can also handle updates with exclude_unset=True and serialize ORM objects with from_attributes=True.

This is the foundational skill you'll use in every CRUD endpoint you build. Next up, you'll learn how to handle relationships between models — for example, connecting a Post model to a User model, and translating those nested Pydantic schemas in both directions. That's where this schema-to-model bridge becomes truly powerful.

Before moving on, try this: extend the example with a UserUpdate schema and a PATCH endpoint, then test it with httpx or curl to confirm partial updates work correctly. You'll see the same pattern scales effortlessly.