Cheatsheet for cleanly mapping between SQLAlchemy ORM and Pydantic.

Why separate

Pydantic models = API I/O contracts. SQLAlchemy models = DB persistence.

They look similar; their concerns differ:

  • API schemas evolve with consumers.
  • DB models evolve with storage / queries.
  • Sensitive columns (password_hash) shouldn’t leak to API.

Don’t reuse SQLAlchemy classes for API responses.

Read / Create / Update trio

# Pydantic
class UserBase(BaseModel):
    email: EmailStr
    name: str

class UserCreate(UserBase):
    password: str

class UserUpdate(BaseModel):
    email: EmailStr | None = None
    name: str | None = None

class UserRead(UserBase):
    id: int
    created_at: datetime
    model_config = {"from_attributes": True}

# SQLAlchemy
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(unique=True)
    name: Mapped[str]
    hashed_password: Mapped[str]
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())

Note: hashed_password exists on DB model only.

Create handler

@app.post("/users", response_model=UserRead, status_code=201)
async def create(data: UserCreate, db: AsyncSession = Depends(get_db)):
    user = User(
        email=data.email,
        name=data.name,
        hashed_password=hash_pw(data.password),
    )
    db.add(user)
    await db.commit()
    await db.refresh(user)
    return user                            # SA → UserRead

Get handler

@app.get("/users/{id}", response_model=UserRead)
async def get_(id: int, db: AsyncSession = Depends(get_db)):
    user = await db.get(User, id)
    if not user: raise HTTPException(404)
    return user

response_model=UserRead + from_attributes=True = automatic mapping.

PATCH (partial update)

@app.patch("/users/{id}", response_model=UserRead)
async def update_(id: int, data: UserUpdate, db: AsyncSession = Depends(get_db)):
    user = await db.get(User, id)
    if not user: raise HTTPException(404)
    
    # Only fields explicitly sent
    for k, v in data.model_dump(exclude_unset=True).items():
        setattr(user, k, v)
    
    await db.commit()
    return user

List handler

@app.get("/users", response_model=list[UserRead])
async def list_(db: AsyncSession = Depends(get_db)):
    return (await db.execute(select(User))).scalars().all()

Nested relations

# SA
class User(Base):
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

# Pydantic
class PostRead(BaseModel):
    id: int
    title: str
    model_config = {"from_attributes": True}

class UserWithPosts(UserRead):
    posts: list[PostRead]

Eager-load in handler:

@app.get("/users/{id}", response_model=UserWithPosts)
async def get_(id: int, db: AsyncSession = Depends(get_db)):
    user = await db.scalar(
        select(User).options(selectinload(User.posts)).where(User.id == id)
    )
    if not user: raise HTTPException(404)
    return user

SA hybrid_property → Pydantic computed_field

# SA
class User(Base):
    first: Mapped[str]
    last: Mapped[str]
    
    @hybrid_property
    def full_name(self) -> str:
        return f"{self.first} {self.last}"

# Pydantic — reads via from_attributes
class UserRead(BaseModel):
    id: int
    full_name: str           # populated from SA hybrid_property
    model_config = {"from_attributes": True}

Or use Pydantic @computed_field if SA model doesn’t have it:

class UserRead(BaseModel):
    id: int
    first: str
    last: str
    
    @computed_field
    @property
    def full_name(self) -> str:
        return f"{self.first} {self.last}"
    
    model_config = {"from_attributes": True}

Sensitive columns

Never include in Read model:

class UserRead(BaseModel):
    id: int
    email: str
    # NO hashed_password, internal_notes, etc.

If you forget: FastAPI’s response_model shaping drops fields not in the model.

Aliases for camelCase API

from pydantic.alias_generators import to_camel

class UserRead(BaseModel):
    full_name: str
    created_at: datetime
    
    model_config = {
        "from_attributes": True,
        "alias_generator": to_camel,
        "populate_by_name": True,
    }

# Output: {"fullName": "...", "createdAt": "..."}

SA enum ↔ Pydantic

import enum

class Role(str, enum.Enum):
    USER = "user"
    ADMIN = "admin"

# SA
class User(Base):
    role: Mapped[Role] = mapped_column(SAEnum(Role))

# Pydantic — uses the same enum
class UserRead(BaseModel):
    role: Role
    model_config = {"from_attributes": True}

JSONB ↔ Pydantic-backed JSONB

See SQLA Postgres Cheatsheet 02 for PydanticJSONB.

Service layer (between API and DB)

class UserService:
    def __init__(self, db: AsyncSession):
        self.db = db
    
    async def create(self, data: UserCreate) -> User:
        user = User(email=data.email, hashed_password=hash_pw(data.password))
        self.db.add(user)
        await self.db.commit()
        return user
    
    async def get(self, id: int) -> User | None:
        return await self.db.get(User, id)

Handler stays thin:

@app.post("/users", response_model=UserRead)
async def create_(data: UserCreate, svc: UserService = Depends(get_user_service)):
    return await svc.create(data)

Common mistakes

  • Returning SA model directly without response_model — leaks fields.
  • Same model for input and output — too rigid; sensitive fields in input contract.
  • Lazy-load in async without eager-load — error converting to Pydantic.
  • from_attributes=True missing on Read model — model_validate fails on SA instance.

Read this next

If you want my schema/model trio template, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .