skip to content

In FastAPI with SQLModel, why should a table model not be the response model, and how do Base, Create, Public and Update models fix it?

level: middleimportance: must knowfreq 45%

answer

  1. one class per job
  2. what leaks, what reads as optional
  3. a data-only base for shared fields
  4. the response model reads attributes

basics

~20 s

Returned as the response, a SQLModel table model sends every column and documents id as optional; as a body it lets clients send id. Data models fix it: a shared Base, a Create model for input, a Public model for output and an all-optional Update model.

solid answer

~40 s

`Book(table=True)` describes the row, not the API. As the response it sends every column, including internal ones such as `supplier_cost`, and its `id: int | None` appears as optional, so generated clients must null-check it. As the body it lets a client pick the `id`, and SQLModel builds table models without Pydantic validation, so bad input reaches the database instead of getting a 422. The recipe puts shared fields in a data-only `BookBase`, then derives `Book(BookBase, table=True)` for storage, `BookCreate` for input, `BookPublic` with `id: int` for output and `BookUpdate` with every field optional. The endpoint builds the row with `Book.model_validate(book)`, returns the table object and declares `response_model=BookPublic`, so FastAPI reads the object's attributes and sends only the public fields.

code

python · 43 lines
python
from typing import Annotated

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


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


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


class BookCreate(BookBase):
    supplier_cost: float


class BookPublic(BookBase):
    id: int


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.post("/books/", response_model=BookPublic)
def create_book(book: BookCreate, session: SessionDep):
    db_book = Book.model_validate(book)  # validated, id left to the database
    session.add(db_book)
    session.commit()
    session.refresh(db_book)
    return db_book  # a Book; FastAPI sends only BookPublic's fields

go deeper

for a junior

Remember the four roles: a shared base, a table model for storage, a Create model for input and a Public model for output, plus an all-optional Update model.

for a middle

Explain how response_model=BookPublic filters a returned Book by reading its attributes, why it is used instead of -> BookPublic, and why BookPublic redeclares id: int.

for a senior

Show what a table-model signature costs in production: leaked columns, client-chosen keys, unvalidated bodies, and recursive relationship nesting.

for a principal

Weigh how strictly the team separates storage and API models, and whether shared bases are worth the coupling they create between columns and the public contract.

## One class for everything is the problem A SQLModel **table model** such as `Book(table=True)` describes the stored row: its key, every column, internal ones included. FastAPI will happily accept that same class as a request body and return it as the response, and the first version of the FastAPI tutorial does exactly that. It works, and it has four defects: - **It leaks columns.** Returned as is, a `Book` is serialized with every field, so an internal column such as `supplier_cost` reaches every client. - **It documents `id` as optional.** The table model declares `id: int | None` so an unsaved object can exist; the generated schema copies that, and clients generated from the schema must null-check an id that is always present. - **It lets the client choose the key.** As a body, `Book` has an `id` field, so a client can send `{"id": 42, ...}` and pick, or collide with, a primary key that the database should assign. - **It skips validation.** SQLModel builds table models without running Pydantic validation, and a body parameter typed as a table model takes that path too: a string sent for an integer field or a missing required field reaches the database and fails as a 500 instead of a 422. ## The four-model split The recipe in FastAPI's and SQLModel's docs gives each job its own class and shares fields through inheritance from a **data-only base**: | Model | Declared as | Fields | Used for | |---|---|---|---| | `BookBase` | `class BookBase(SQLModel)` | shared: `title`, `year` | inheritance only | | `Book` | `class Book(BookBase, table=True)` | base plus `id: int \| None` and `supplier_cost` | the table and the session | | `BookCreate` | `class BookCreate(BookBase)` | base plus `supplier_cost`, no `id` | the POST body | | `BookPublic` | `class BookPublic(BookBase)` | base plus `id: int` | every response | | `BookUpdate` | `class BookUpdate(SQLModel)` | every field optional, default `None` | the PATCH body | Only `Book` has `table=True`, so `create_all` creates only one table. `BookPublic` redeclares `id: int`, which makes `id` required in the response schema: a contract that every book a client reads has an integer id. `BookUpdate` redeclares its fields because their types and defaults genuinely differ from the base. ## Returning a table object through `response_model` The create endpoint reads a `BookCreate`, builds the row with `Book.model_validate(book)`, adds, commits and refreshes it, then returns the **table object**: ```python @app.post("/books/", response_model=BookPublic) def create_book(book: BookCreate, session: SessionDep): db_book = Book.model_validate(book) ... return db_book ``` FastAPI validates the return value against `BookPublic`, reading the declared fields from the `Book` object's attributes, and sends only those. If the object's `id` were still `None`, validation would fail with `ResponseValidationError` and the client would see a 500. Why `response_model=BookPublic` and not `-> BookPublic`? Both make FastAPI filter the response, and when both are present `response_model` takes priority. The function really returns a `Book`, so a `-> BookPublic` annotation would make type checkers report a mismatch. `response_model` tells FastAPI what to send without misdescribing the function. ## Relationships need their own public models Relationship attributes declared with `Relationship()`, such as `Book.author`, are not Pydantic fields, so neither `BookPublic` nor a bare returned `Book` includes them. To return a book with its author: 1. Declare `AuthorPublic(AuthorBase)` with `id: int`. 2. Declare `BookPublicWithAuthor(BookPublic)` with `author: AuthorPublic | None = None`. It is a data model, so it uses a plain annotation, not `Relationship()`. 3. Use it as the read-one endpoint's `response_model`; FastAPI reads `author` from the returned table object, which loads it through the session. Do not nest both directions. If the author's public model listed books and each book's model listed its author, serialization would loop until Python's recursion limit. Nest one level: the book's model carries `AuthorPublic`, the author's model carries a list of `BookPublic`. ## Keeping the split cheap - Shared fields live once, in the base; a column rename touches one place. - `BookCreate` can be a bare `pass` subclass if it needs nothing extra; the name still makes `/docs` clearer than reusing `BookBase`. - Inherit only from data models; a class derived from the table model would bring its columns back. - Do not build an elaborate inheritance tree to save two lines; SQLModel's docs advise duplicating a few fields when that is easier to reason about.

  • How do you return a book together with its author without nesting both directions?
    Declare a data model `BookPublicWithAuthor(BookPublic)` with `author: AuthorPublic | None = None` and use it as the read-one endpoint's `response_model`; FastAPI reads `author` from the returned table object. Data models use plain annotations, not `Relationship()`. Nesting `AuthorPublic`, which has no books list, stops the book, author, books loop that would otherwise recurse until Python's recursion limit.
  • What happens if an endpoint returns a `Book` whose `id` is still `None` under `response_model=BookPublic`?
    `BookPublic` declares `id: int`, so validating the returned object fails, FastAPI raises `ResponseValidationError`, and the client sees a 500. It usually means the code returned the object before `session.commit()` and `session.refresh()` gave it the id the database generated.
  • Why does `BookUpdate` redeclare every field instead of inheriting them from `BookBase`?
    Each field changes: `title: str` becomes `title: str | None = None`, so a PATCH body can carry any subset. Inheriting from `BookBase` would keep the required fields required. SQLModel's docs treat this as a case where an independent model is clearer than a deeper inheritance tree.

A library keeps a staff catalogue card with the supplier and the price paid, and prints a public catalogue entry with title, year and shelf number. Both describe the same book, but visitors get the public entry; nobody photocopies the staff card and hopes they skip the back.

  • response_model only changes the docs; the returned table object is sent as is.
  • FastAPI ignores return annotations, so response_model is the only filter.
  • A table model body is safe because FastAPI validates every body with Pydantic.
  • BookPublic should inherit from the Book table model to reuse its columns.
  • Adding Relationship() to a public model makes FastAPI include related rows.