In a FastAPI app using SQLModel, what does `table=True` change about a model class, and what is a class without it?
answer
- one class, two libraries
- Pydantic model plus mapped table
- only some classes get tables
- what create_all looks for
basics
~20 sWith table=True, a SQLModel class is a table model: a Pydantic model that is also a SQLAlchemy-mapped table with columns, keys and indexes. Without it, the class is a data model, a Pydantic schema for API bodies that never gets a table.
solid answer
~40 sEvery `SQLModel` subclass is a Pydantic model. Adding `table=True` also maps it to a SQLAlchemy table: each field becomes a column, `Field(primary_key=True)` and `Field(index=True)` add the key and an index, the table name defaults to the lower-cased class name, and `SQLModel.metadata.create_all(engine)` creates it. A class without `table=True` is a **data model**: FastAPI uses it to validate and document bodies, but it has no table, and only table models can declare `Relationship()` attributes. The usual layout is a data-only `BookBase` with the shared fields, `Book(BookBase, table=True)` adding `id` and internal columns, and further data models for create, read and update. The key is declared `id: int | None = Field(default=None, primary_key=True)` so a `Book` can exist in Python before the database assigns its id, while the column itself is created non-null.
code
python · 20 linesfrom sqlmodel import Field, SQLModel, create_engine
class BookBase(SQLModel): # data model: no table
title: str = Field(index=True)
year: int | None = Field(default=None, index=True)
class Book(BookBase, table=True): # table model: table "book"
id: int | None = Field(default=None, primary_key=True)
supplier_cost: float
class BookPublic(BookBase): # data model for responses
id: int
engine = create_engine("sqlite:///catalogue.db")
SQLModel.metadata.create_all(engine) # creates only "book"
print(list(SQLModel.metadata.tables)) # ['book']go deeper
Recall that table=True turns a SQLModel class into a database table and that every other SQLModel class is a data model for API bodies; name Field(primary_key=True) and Field(index=True).
Explain why the key is declared int | None with a None default yet created non-null, and how Field settings on a data-only base reach the table model that inherits them.
Show that you keep table models out of request and response signatures and know create_all only adds missing tables, so schema changes go through migrations.
Decide how much a team's table and API models should share through one base, since a shared base ties column renames to the public contract.
## Two kinds of SQLModel class **SQLModel** is a library from FastAPI's author that lets one Python class be both a **Pydantic model** and a **SQLAlchemy-mapped table**. Every subclass of `SQLModel` is a Pydantic model: FastAPI can use it as a request body, a response model or a return annotation, and it shows up as a schema in `/docs`. Whether the class is *also* a database table depends on one class keyword: - **Table model**: declared with `table=True`, for example `class Book(BookBase, table=True)`. SQLModel maps it to a table, turns its fields into columns and registers it in `SQLModel.metadata`. - **Data model**: any `SQLModel` subclass without `table=True`. It is a Pydantic model with a few SQLModel extras and nothing more: no table, no columns, no session. In a FastAPI app the table model describes the stored row, and data models describe what crosses the API boundary: what a client sends to create or update a book, and what the API sends back. ## What `table=True` adds When a class carries `table=True`, SQLModel does the SQLAlchemy work on top of the Pydantic model: - Each field becomes a **column**, with a SQL type derived from the annotation (`str` becomes a text or `VARCHAR` column, `int` an `INTEGER`). - `Field(primary_key=True)` marks the **primary key**; `Field(index=True)` asks for an **index**, which speeds up lookups filtered by that column; `foreign_key`, `unique` and `nullable` are further column options on `Field()`. - The table name defaults to the **lower-cased class name**, so `Book` maps to a table called `book`. - `SQLModel.metadata.create_all(engine)` creates the tables of every table model it knows about and ignores data models entirely. - Only a table model can declare **relationship attributes** with `Relationship()`, which load related rows (a book's author) through the session. A data model gets none of that. It validates input, filters output and documents the schema, which is exactly the job of request and response models. ## The primary key that starts as `None` The canonical key declaration is: ```python id: int | None = Field(default=None, primary_key=True) ``` The `None` default exists for the **Python object**, not the column. Code creates a `Book` before saving it, when the database has not assigned an id yet; after `session.commit()` and `session.refresh(book)` the object carries the generated value. SQLModel still creates the column as a **non-null `INTEGER`**, because a primary key column is never null in the database. The side effect is that the table model's schema documents `id` as optional, which is one reason the response side uses a separate data model, `BookPublic`, that redeclares `id: int`. ## Inheritance: share fields through a data model The recipe in FastAPI's and SQLModel's docs puts shared fields in a **data-only base** and derives everything from it: 1. `BookBase(SQLModel)` holds `title` and `year`, with their `Field()` settings. 2. `Book(BookBase, table=True)` adds `id` and internal columns such as `supplier_cost`. 3. `BookPublic(BookBase)`, `BookCreate(BookBase)` and an update model reuse the base without becoming tables. `Field(index=True)` declared on `BookBase` does nothing to the base itself, which has no table, but the table model that inherits the field creates the index (`ix_book_title`) when its table is created. SQLModel's docs advise inheriting only from data models, never from a table model: a class derived from a table model drags the mapping and every column along, internal ones included. | | Table model | Data model | |---|---|---| | Declared with | `table=True` | no `table` keyword | | Is a Pydantic model | yes | yes | | Has a database table | yes, named after the class | no | | Created by `create_all` | yes | no | | Can declare `Relationship()` | yes | no | | Typical FastAPI role | the stored row, used through the session | request and response bodies | One more difference matters later: SQLModel's docs say that building a table model directly, as in `Book(...)`, currently does not enforce Pydantic validation, and recommend `Book.model_validate(data)` when input needs checking. Data models validate on construction as any Pydantic model does. ## Creating the tables FastAPI's tutorial calls `SQLModel.metadata.create_all(engine)` when the app starts; in a current app that call belongs in the `lifespan` handler. `create_all` only creates tables that do not exist yet. It never alters an existing table, so a column added to `Book` later is not applied to a database that already has `book`. FastAPI's docs note that production would probably use a migration script run before the app starts, and point to Alembic.
- Why do the SQLModel docs advise inheriting only from data models, never from a table model?A class derived from a table model inherits its mapping and every column, internal ones included, so an API schema built that way exposes storage details and carries SQLAlchemy machinery it does not need. The recipe puts the shared fields in a data-only base such as `BookBase` and derives both the table model and each API model from it, so exactly one class owns the table.
- Is calling `SQLModel.metadata.create_all()` at startup enough for a production FastAPI app?It suits a tutorial, a prototype or a test database: from the `lifespan` handler it creates any table that is missing. It never alters a table that already exists, so a column added to `Book` later is not applied. FastAPI's docs say production would probably run a migration script, for example with Alembic, before the app starts.
- Every SQLModel subclass gets its own database table.
- A data model without table=True cannot be a FastAPI request body.
- Declaring id as int | None makes the primary key column nullable.
- Field(index=True) on a data-only base never reaches the table model.
- Relationship() attributes work on any SQLModel class, table or not.