skip to content

A FastAPI service builds SQLModel rows with `Book(**data)` or takes `Book` as the body, and bad input reaches the database; why, and what fixes it?

level: seniorimportance: should knowfreq 25%

answer

  1. construction is not validation
  2. table models versus data models
  3. a 500 where a 422 was due
  4. validate first, then build the row

basics

~20 s

In SQLModel 0.0.48, building a table model directly, or binding a FastAPI body to one, skips Pydantic validation, so wrong types and missing fields reach the database. Accept a data model such as BookCreate and build the row with Book.model_validate().

solid answer

~40 s

SQLModel builds table models through its own initializer, which does not run Pydantic validation; its docs say direct construction currently does not enforce constraints and recommend `model_validate()`. So `Book(title="Dune", year="soon")` succeeds, and a FastAPI parameter typed as the `Book` table model takes the same path: a string sent for an integer field can be stored on SQLite, and a missing required field fails at INSERT with an `IntegrityError`, a 500 instead of a 422. Data models validate normally. The fix is to accept `BookCreate` as the body, so FastAPI answers 422 and the client cannot send an `id`, and to build the row with `Book.model_validate(book)`, which validates. `sqlmodel_update()` does not validate either, so field constraints belong on `BookCreate` and `BookUpdate`, with database constraints as the last line.

code

python · 28 lines
python
from pydantic import ValidationError
from sqlmodel import Field, SQLModel


class BookBase(SQLModel):
    title: str
    year: int | None = None


class Book(BookBase, table=True):
    id: int | None = Field(default=None, primary_key=True)


class BookCreate(BookBase):
    pass


print(Book(title="Dune", year="soon"))  # no error: title='Dune' year='soon' id=None

try:
    BookCreate(title="Dune", year="soon")
except ValidationError as exc:
    print(exc.errors()[0]["type"])  # int_parsing

try:
    Book.model_validate({"title": "Dune", "year": "soon"})
except ValidationError as exc:
    print(exc.errors()[0]["type"])  # int_parsing

go deeper

for a junior

Remember that request bodies should be data models such as BookCreate and that rows are built with Book.model_validate(), not by calling Book directly.

for a middle

Explain which SQLModel construction paths validate and which do not, and why a missing field then shows up as a database error rather than a 422.

for a senior

Diagnose the 500s from the IntegrityError back to an unvalidated table-model body, fix the signature and constructor, and add tests that pin the 422.

for a principal

Turn the fix into a team rule enforced in review and tests, and track the SQLModel release that changes construction behaviour before relying on it.

## The symptom A catalogue service on FastAPI and SQLModel works in happy-path tests, then production shows odd failures: - POST requests with a missing field return **500** with an `IntegrityError` in the log, where a **422** naming the field was expected. - On SQLite, a `year` column declared `int` holds strings such as `"soon"`; a stricter database rejects such a value at INSERT with a driver error, which is still a 500. - A `Field(ge=0)` on `copies` does not stop negative values. - Clients occasionally set their own `id` on new books. The code either takes the table model directly as the body, `def create_book(book: Book, ...)`, or builds rows from request data with `Book(**data)`. ## Why: table models are built without validation SQLModel builds **table models** (classes with `table=True`) through its own initializer, which copies values onto the object so SQLAlchemy can track them, and does not run Pydantic validation. SQLModel's docs say it plainly: constructing a table model directly currently does not enforce Pydantic constraints, this will change in a future version, and `Model.model_validate(data)` is the call to use when validation is needed. In SQLModel 0.0.48: - `Book(title="Dune", year="soon")` succeeds and leaves the string in `year`. - A FastAPI body parameter typed as `Book` goes through the same unvalidated construction, so wrong types pass, a missing required field surfaces only at INSERT, and an `id` in the body is accepted. - **Data models** (no `table=True`) validate like any Pydantic model, on construction and as FastAPI bodies. | How the object is built | Validated? | Bad input becomes | |---|---|---| | body typed `BookCreate` (data model) | yes | a 422 from FastAPI | | `BookCreate(**data)` | yes | a `ValidationError` | | body typed `Book` (table model) | no | a 500 at INSERT, or a bad row | | `Book(**data)` | no | a 500 at INSERT, or a bad row | | `Book.model_validate(data)` | yes | a `ValidationError` | | `db_book.sqlmodel_update(data)` | no | a 500 at commit, or a bad row | ## The fix 1. **Accept a data model as the body.** `def create_book(book: BookCreate, session: SessionDep)`. FastAPI validates it and answers 422 for wrong types, missing fields and failed constraints, before the endpoint runs. `BookCreate` has no `id`, so the client cannot choose one. 2. **Build the row with `model_validate()`.** `db_book = Book.model_validate(book)` validates against the table model and creates an object SQLAlchemy can save. Server-side values go in through `update=`: `Book.model_validate(book, update={"added_by": user_id})`, where the table model declares that field. 3. **Put constraints where validation runs.** Declare `Field(ge=0)` and similar rules on the shared base or on `BookCreate` and `BookUpdate`, not only on the table model. `sqlmodel_update()` copies values without validating, so PATCH relies on `BookUpdate`'s rules. 4. **Keep database constraints as the last line.** NOT NULL, CHECK and foreign keys still matter for writers that bypass the API. ```python class BookBase(SQLModel): title: str copies: int = Field(default=1, ge=0) class Book(BookBase, table=True): id: int | None = Field(default=None, primary_key=True) class BookCreate(BookBase): pass @app.post("/books/", response_model=BookPublic) def create_book(book: BookCreate, session: SessionDep): db_book = Book.model_validate(book) session.add(db_book) session.commit() session.refresh(db_book) return db_book ``` ## Hardening it in review and tests - Search path operation signatures for table models and treat each one as a defect. - Flag every `Book(**...)` built from request data; a validated `BookCreate` passed through `model_validate()` is the pattern. - Add a test per create and update endpoint that sends a wrong type and a missing field and asserts a **422**; with a table-model body the same test gets a 200 or a 500. - When upgrading SQLModel, read its release notes for this behaviour: the docs describe direct construction without validation as current behaviour expected to change. ## Why this is easy to miss The table model *looks* like a Pydantic model, appears in `/docs` with required fields marked, and passes every test that sends valid data. Nothing fails until invalid input arrives, and then the failure is a database error far from its cause. The generated schema even advertises the problem: a table-model body lists `id` as an accepted input field, which is the first thing to look for when reviewing an endpoint in `/docs`.

  • Does adding `Field(ge=0)` to a column of the table model protect it?
    Not on the paths that skip validation: `Book(copies=-5)` and a table-model body both accept it. `Book.model_validate()` enforces it and raises a `greater_than_equal` error. Put the rule on the shared base so `BookCreate` enforces it with a 422, and add a database CHECK constraint if it must hold for every writer.
  • How would you catch this class of bug in review and in tests?
    In review, treat any table model in a path operation signature, and any `Book(**...)` built from request data, as a defect; the pattern is a data-model body plus `model_validate()`. In tests, send a wrong type and a missing field to each create and update endpoint and assert a 422; a table-model body returns a 200 or a 500 instead.

  • FastAPI validates every body with Pydantic, table models included.
  • Book(**request_dict) is safe because Book declares the field types.
  • Field(ge=0) on a table model rejects negative values when the object is built.
  • An IntegrityError 500 means the schema is wrong, not the input path.
  • sqlmodel_update validates values before setting them on the row.