DB 1/2: using a database for storage

Previously, we saw how to use Pydantic to validate and convert data.
But an API is not very useful if the data disappears at each restart…
👉 It’s time to see how to store data in a database.

(See complete code in python/db-single-table/)

Tool choice: SQLModel¶

To work with an SQL database in FastAPI, the most practical option is to use SQLModel:

based on SQLAlchemy (robust and proven),
compatible with Pydantic (automatic validation and serialization),
simple and modern syntax (Python + types).

Installation:

pip install sqlmodel sqlite

Define a table model¶

A SQLModel model looks a lot like a Pydantic model, with the added capability to describe an SQL table.

from sqlmodel import SQLModel, Field

class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    email: str
    is_active: bool = True

➡️ SQLModel is a subclass of pydantic.BaseModel; you can use all Pydantic features too !
➡️ table=True indicates that this class corresponds to a table.
➡️ id is an auto-incremented primary key (value provided by the database).

Create database and session¶

Now we need to create the database and prepare a session to interact with it.

# typical boilerplate code

from sqlmodel import create_engine, Session

# local SQLite file
database_url = "sqlite:///./users.db"
engine = create_engine(database_url, echo=True)

# create tables
# of course this needs to come AFTER all model definitions...
SQLModel.metadata.create_all(engine)

# utility function to get a session
def get_session():
    with Session(engine) as session:
        yield session

Write CRUD endpoints¶

CRUD = Create, Read, Update, Delete.
Here’s a simple API example to manage User entries.

Create a user¶

# this uses so-called "dependency injection"
# meaning the session is (re-)created on demand by FastAPI

from fastapi import FastAPI, Depends

app = FastAPI()

@app.post("/users/", response_model=User)
def create_user(user: User, session: Session = Depends(get_session)):
    session.add(user)
    session.commit()
    session.refresh(user)  # reload the object with the generated ID
    return user

and that’s it !
➡️ everything else (validation, conversion, storage) is handled automatically !

List users¶

from typing import List

@app.get("/users/", response_model=List[User])
def list_users(session: Session = Depends(get_session)):
    users = session.query(User).all()
    return users

This API in action¶

Create a user:¶

http POST :8000/users/ name="Alice" email="alice@example.com"

Response:

{
  "id": 1,
  "name": "Alice",
  "email": "alice@example.com",
  "is_active": true
}

➡️ because id is the primary key, it is automatically generated by the database.
➡️ also the POST verb here is optional, http infers it from the presence of a body.

List users:¶

http :8000/users/

Response:

[
    {
        "email": "alice@example.com",
        "id": 1,
        "is_active": true,
        "name": "Alice"
    }
]

Using variants of models¶

In practice, we often use different variants of a model for different operations, e.g.:

when creating a user, we don’t provide the id (generated by the database),
when listing users, we don’t want to expose sensitive data like passwords,
when updating a user, we want all fields to be optional, etc.

# we can define multiple models
# based on the same table:

class UserCreate(BaseModel):
    name: str
    email: str
    is_active: bool = True

class UserRead(BaseModel):
    id: int
    name: str
    email: str
    is_active: bool

# etc...

And then use them in the endpoints accordingly.
It’s also possible to use inheritance to avoid code duplication.

Relationships between tables¶

Why is it cool ?¶

With SQLModel, FastAPI makes it easy to connect:

data validation (inherited from Pydantic),
and database persistence (via SQLAlchemy).

The benefits are numerous:

✅ We manipulate a single class for both validation and persistence.
✅ The database retains data between runs.
✅ Easy to illustrate CRUD operations.

What now ?¶

👉 You can now create real APIs capable of storing and retrieving information in an SQL database.

Next step: enrich your models with relationships (e.g., User ↔ Address).