Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 50 ยท Databases

SQLModel

Use SQLModel to define a database table and its Pydantic schema in one class, build Create, Public and table models, and serve them from FastAPI.

In SQLAlchemy with FastAPI every entity needed two classes before it did anything useful: an ORM model for the table and a Pydantic model for the JSON. The fields were written twice, and a rename had to be remembered in both places. SQLModel is a small library by the author of FastAPI that removes much of that duplication. A single class can be a Pydantic model, an SQLAlchemy model, or both at once.

SQLModel is not a new database engine and not a rival to SQLAlchemy. It sits on top of SQLAlchemy (for tables, sessions and queries) and Pydantic (for validation and schemas). Knowing both foundations makes SQLModel easy to learn, and also tells you where its limits are.

One class, two jobs

SQLModel is a layer

SQLAlchemy Tables, sessions, queries.
Pydantic Validation and JSON schema.
SQLModel One class that is both.

Same engines, same drivers, less repetition.

1 / 2

A class that inherits from SQLModel is a Pydantic model. Adding table=True additionally registers it as a database table, with columns generated from the field types. Field(...) accepts both Pydantic options (min_length, ge) and database options (primary_key, index, unique).

Install it like the other packages (pip install sqlmodel locally; the examples below install it with micropip in the browser).

Python A table model is also a Pydantic model

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The same Hero supplies the table (__table__), the validation schema (model_json_schema) and the dictionary conversion (model_dump). Notice that queries use session.exec(select(...)), SQLModel's own session method, which returns objects directly rather than rows.

Every table model registers itself in one global SQLModel.metadata, and a table name can only be registered once. In a script that is no problem, but in this page the Python session stays alive between runs, so the examples call SQLModel.metadata.clear() first to let you press Run repeatedly. You will not need that line in a real project.

Several models per entity

One class for everything sounds neat but breaks down at the API boundary, for the same reasons as before: clients must not send the id, and responses must not include a password hash. The SQLModel convention is a small family of classes built by inheritance:

Class table=True? Purpose
HeroBase no Fields shared by every variant
Hero(HeroBase, table=True) yes The database table, adds id and private columns
HeroCreate(HeroBase) no Request body for creating
HeroPublic(HeroBase) no Response model, guarantees an id
HeroUpdate(SQLModel) no All fields optional for PATCH

This is the Create/Read/Update inheritance from Response models and nested data, with the table class added. Fields are written once in HeroBase.

Python The model family inside a FastAPI app

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Trace the secret: it travels in on HeroCreate, is stored in the Hero table, and never leaves, because HeroPublic simply does not declare it. Hero.model_validate(data) builds the table object from the validated create schema. The PATCH handler uses model_dump(exclude_unset=True) to apply only the fields the client sent, the technique explained in PUT, PATCH and partial updates; sqlmodel_update copies those values onto the row.

A gotcha: table models do not validate

Here is the most surprising SQLModel behavior. A model without table=True validates on creation like any Pydantic model. A model with table=True does not: you can write Hero(name="x", power="y", age="not a number") and no error is raised, because SQLAlchemy needs to build instances without Pydantic getting in its way.

Python Validation: plain model versus table model

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The practical rule: validate with a non-table class (the Create schema as the request body) and convert with Hero.model_validate(...), as the app above does. Never build a table object straight from raw input.

When to choose SQLModel

SQLModel shines for small and medium services where the tables and the API look alike: less code, one place for field definitions, and the docs and editor help of both libraries. Be aware of the trade-offs:

  • It is younger and moves more slowly than SQLAlchemy, and it tracks both libraries' versions, so check compatibility when you upgrade.
  • Advanced SQLAlchemy features still exist but come through the SQLAlchemy objects underneath, so you need to understand SQLAlchemy anyway.
  • Large systems with a rich domain often prefer plain SQLAlchemy models plus separate Pydantic schemas, because the two evolve independently.

Both choices are respectable. The patterns in the rest of this track, such as the session dependency, the schema family and 404 handling, work the same way with either.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does table=True do on a SQLModel class?

    Without table=True the class is just a Pydantic data model. With it, SQLModel also creates the SQLAlchemy table mapping.
  2. 2. Which class should be used as the request body when creating a hero?

    Table models skip validation on creation, so validate with a plain schema first, then build the table object from it.
  3. 3. How does a secret column stay out of the API response?

    response_model filters the output to the fields the public schema declares.
  4. 4. Which foundations does SQLModel build on?

    SQLModel combines SQLAlchemy for the database side with Pydantic for validation and schemas.

Summary

  • SQLModel merges SQLAlchemy and Pydantic: SQLModel classes are Pydantic models, and table=True makes them tables.
  • Use a family of classes: a base with shared fields, the table, Create, Public and Update variants.
  • Query with session.exec(select(...)), and apply partial updates with model_dump(exclude_unset=True) and sqlmodel_update.
  • Table models do not validate on creation; validate with a non-table schema first.
  • The library suits small and medium APIs; large systems often keep separate SQLAlchemy models and schemas.

Next: CRUD, filtering and pagination builds a complete data layer with search, paging and proper error codes.