Skip to content

SQLModel Basics

In previous sections, we've used Python dictionaries to store our data in memory. If our application restarts, all of our data disappears. To persist our data, we need a database.

While we could write raw SQL queries as Python strings to interact with a database, that approach can become brittle and error-prone as our application grows. Instead, we'll use an Object-Relational Mapper (ORM).

If you've worked with Hibernate (Java), Entity Framework (.NET), ActiveRecord (Rails), or Sequelize (Node.js), the concept will be familiar. An ORM lets you work with database rows as language-native objects: queries return instances of your classes, attribute writes turn into UPDATE statements, and relationships traverse via attribute access instead of JOINs you write by hand.

SQLAlchemy and SQLModel

In the Python ecosystem, SQLAlchemy 2.0 is the undisputed industry standard for ORMs. It's incredibly powerful and flexible, powering some of the largest data systems in the world.

However, since we are building our application with FastAPI, we have an even better option that leverages the power of SQLAlchemy while keeping our code clean and concise: SQLModel.

Created by the same author as FastAPI, SQLModel is built directly on top of both SQLAlchemy and Pydantic. It's designed to give you the best of both worlds:

  1. Pydantic's data validation:

    Just like the request/response schemas we built earlier, SQLModel classes validate data automatically.

  2. SQLAlchemy's power:

    SQLModel objects are fully compatible with SQLAlchemy, so they can directly define and query database tables.

By using SQLModel, we eliminate the need to write separate Pydantic schemas and SQLAlchemy models that look almost identical. SQLModel subclasses are also Pydantic models.

The same class can validate inbound JSON, render outbound JSON, and define the database table, depending on whether table=True is set.

How SQLModel Works

When you define a SQLModel class, you're essentially providing a blueprint. You use standard Python type hints to define the attributes, and SQLModel translates those into database columns.

Here's a quick preview of what that looks like:

Python
from sqlmodel import SQLModel, Field


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

By adding table=True, you instruct SQLModel that this class isn't just for data validation. It should also be translated into a table in your database.