skip to content

In a FastAPI PATCH endpoint backed by SQLModel, why pass `book.model_dump(exclude_unset=True)` to `sqlmodel_update()` instead of the `BookUpdate` model itself?

level: middleimportance: should knowfreq 35%

answer

  1. sent versus defaulted
  2. every update field defaults to None
  3. dict keys versus model fields
  4. an explicit null still counts

basics

~20 s

Given a model, sqlmodel_update() copies every one of its fields, so the None defaults of fields the client never sent overwrite stored values. model_dump(exclude_unset=True) keeps only what the client sent, explicit nulls included, so PATCH touches just those columns.

solid answer

~40 s

`BookUpdate` redeclares every field as optional with a default of `None`, so one body can carry any subset. When `db_book.sqlmodel_update()` receives a model, it walks all of that model's fields and sets each one, so a body of `{"year": 1965}` also writes `None` into `title`, wiping data or failing a NOT NULL constraint with a 500. `book.model_dump(exclude_unset=True)` returns only the keys the client sent, and given a dict, `sqlmodel_update()` sets just the keys that are fields of the table model. Unlike `exclude_none=True`, it still lets a client clear a nullable column by sending `null`. `sqlmodel_update()` does not validate what it copies, so constraints belong on `BookUpdate`, and an explicit null for a NOT NULL column needs a check in the endpoint.

code

python · 46 lines
python
from typing import Annotated

from fastapi import Depends, FastAPI, HTTPException
from sqlmodel import Field, Session, SQLModel, create_engine


class Book(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    title: str
    year: int | None = None


class BookPublic(SQLModel):
    id: int
    title: str
    year: int | None


class BookUpdate(SQLModel):
    title: str | None = Field(default=None, min_length=1)
    year: int | None = None


engine = create_engine("sqlite:///catalogue.db", connect_args={"check_same_thread": False})


def get_session():
    with Session(engine) as session:
        yield session


SessionDep = Annotated[Session, Depends(get_session)]
app = FastAPI()


@app.patch("/books/{book_id}", response_model=BookPublic)
def update_book(book_id: int, book: BookUpdate, session: SessionDep):
    db_book = session.get(Book, book_id)
    if not db_book:
        raise HTTPException(status_code=404, detail="Book not found")
    changes = book.model_dump(exclude_unset=True)  # only what the client sent
    db_book.sqlmodel_update(changes)
    session.add(db_book)
    session.commit()
    session.refresh(db_book)
    return db_book

go deeper

for a junior

Recall the PATCH recipe: an all-optional update model, model_dump(exclude_unset=True), sqlmodel_update, then add, commit and refresh.

for a middle

Explain why passing the update model itself writes None into fields nobody sent, and why exclude_unset, not exclude_none, lets a client clear a nullable column.

for a senior

Show the edges you guard in production: explicit nulls on NOT NULL columns, no validation inside sqlmodel_update, silently skipped keys and server-computed values via update.

for a principal

Decide whether the team's update contract allows clearing fields with null and how that is documented and enforced across services.

## The update model A **PATCH** endpoint changes only the fields a client sends. In the FastAPI and SQLModel recipe that starts with an update data model whose fields are all optional: ```python class BookUpdate(SQLModel): title: str | None = None year: int | None = None ``` Every field defaults to `None`, so `{}`, `{"year": 1965}` and `{"title": "Dune", "year": null}` are all valid bodies. The model redeclares its fields rather than inheriting `BookBase` unchanged, because `title: str` on the base is required and the update model needs it optional; SQLModel's docs call this a case where an independent model is clearer than more inheritance. The catch is that after validation a `BookUpdate` instance holds a value for every field: the ones the client sent, and `None` for the ones it did not. The endpoint has to tell those apart. ## What `sqlmodel_update()` does with each argument `db_book.sqlmodel_update(obj, update=...)` is SQLModel's method for copying values onto an existing table object (it arrived in SQLModel 0.0.16; older code loops with `setattr()`). Its behaviour depends on what it is given: | Argument | What gets set | |---|---| | a `dict` | each key that is a field of the table model; other keys are skipped silently | | a Pydantic or SQLModel model | **every** field of that model, whether the client sent it or not | | `update={...}` (extra keyword) | those keys too, taking precedence over the same keys in the first argument | | anything else | nothing: it raises `ValueError` | Passing the `BookUpdate` instance itself is therefore the bug: for a body of `{"year": 1965}` it also writes `None` into `title`. On a nullable column that silently wipes data; on a NOT NULL column the commit fails with an `IntegrityError` and the client gets a 500. ## Picking the right dump The fix is to convert the model to a dict of only what the client sent: ```python changes = book.model_dump(exclude_unset=True) db_book.sqlmodel_update(changes) ``` Pydantic tracks which fields were explicitly provided, and `exclude_unset=True` keeps only those. The neighbouring options look similar and behave differently: | Dump | Body `{"year": null}` gives | Can a client clear `year`? | |---|---|---| | `model_dump()` | `{"title": None, "year": None}` | yes, but it also wipes `title` | | `model_dump(exclude_unset=True)` | `{"year": None}` | **yes**, and nothing else changes | | `model_dump(exclude_none=True)` | `{}` | no: every `None` is dropped | | `model_dump(exclude_defaults=True)` | `{}` | no: `None` equals the default | Only `exclude_unset` distinguishes "not sent" from "sent as null", which is what PATCH needs. ## Edges to guard - **An explicit null on a NOT NULL column.** `BookUpdate` types `title` as `str | None`, so `{"title": null}` validates, survives `exclude_unset`, and is written; the commit then fails with an `IntegrityError`. Check for it in the endpoint and raise an `HTTPException` with a 4xx status before updating. - **No validation on the copy.** `sqlmodel_update()` sets values without running Pydantic validation, so constraints must live on `BookUpdate` (for example `Field(default=None, min_length=1)` on `title`), where FastAPI enforces them with a 422. - **Unknown keys vanish.** A dict key that is not a field of the table model is skipped without an error, so a typo in server-side code silently updates nothing. - **Server-computed values.** Pass them with `update=`, for example a hash computed from a secret the client sent; they win over the same keys from the client. - **Missing rows.** Look the row up with `session.get()` first and raise a 404 if it is `None`. ## The full endpoint 1. Declare `book_id: int` from the path and `book: BookUpdate` from the body, with `response_model=BookPublic`. 2. Load the row with `session.get(Book, book_id)`; raise `HTTPException(status_code=404)` if it is missing. 3. Build `changes = book.model_dump(exclude_unset=True)`. 4. Call `db_book.sqlmodel_update(changes)`, adding `update=` for any server-side values. 5. `session.add(db_book)`, `session.commit()`, `session.refresh(db_book)`, then return the row, which `BookPublic` filters. Three tests pin the behaviour: a body with only `year` changes that column and leaves `title` intact; `{"year": null}` clears `year`; and an empty body `{}` returns 200 with the row unchanged. A version of the endpoint that passes the model itself to `sqlmodel_update()` fails the first test.

  • What happens when a client sends `{"title": null}` and the `title` column is NOT NULL?
    `BookUpdate` types `title` as `str | None`, so the body validates, `exclude_unset` keeps the explicit null, and `sqlmodel_update()` writes it. The commit then fails with an `IntegrityError` and the client sees a 500. Check the changes for a null on a required column and raise an `HTTPException` with a 4xx status before updating.
  • How do you set a server-computed value during the same update?
    Pass it through the `update` argument: `db_book.sqlmodel_update(changes, update=extra)`, where `extra` is a dict the server builds, for example a hash of a secret the client sent. Keys in `update` win over the same keys in `changes`, and only fields of the table model are set.

A change-of-address slip lists only the lines that change. Filing a blank form over the old record instead would erase every line the person left empty, which is what copying a whole update model does.

  • sqlmodel_update skips fields the client did not send, whatever you pass it.
  • exclude_none=True works the same as exclude_unset=True for PATCH.
  • sqlmodel_update validates each value against the table model before setting it.
  • exclude_unset drops a null the client sent on purpose.
  • PATCH needs one endpoint per field to avoid overwriting data.