Persistent Memory: SQL Databases

Up until now, our API has been stateless. If you restart the server, any data added to our lists or dictionaries vanishes. In the real world, applications need persistence. In this chapter, we will connect our FastAPI application to a real SQL database using SQLModel.

Why SQLModel?

FastAPI works with any database (SQL or NoSQL). However, SQLModel is a library built by the same creator of FastAPI (Tiangolo). It is designed to be the perfect bridge between Pydantic (data validation) and SQLAlchemy (database interaction). With SQLModel, you define a single class that serves as both your Pydantic model for API validation and your SQLAlchemy model for database tables.

Creating Tables Based on Models

First, we need to install the library (pip install sqlmodel). Then, we define our data shape. By inheriting from SQLModel and passing table=True, we tell the library that this class represents a table in the database.

from typing import Optional
from sqlmodel import Field, SQLModel, create_engine

# This class works as a Pydantic model AND a Database Table
class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

# Create the database engine (SQLite for this example)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)

# Helper function to create tables
def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

Establishing Sessions via Dependency Injection

To interact with the database, we need a Session. We want a fresh session for each request, and we want to ensure it closes once the request is finished. This is a perfect use case for FastAPI’s Dependency Injection with yield.

from sqlmodel import Session
from fastapi import Depends, FastAPI

app = FastAPI()

# Dependency
def get_session():
    with Session(engine) as session:
        yield session
        # The session is automatically closed here after the request finishes

Performing CRUD Operations

Now we can implement CRUD (Create, Read, Update, Delete) directly in our path operations.

Create (POST)

We inject the Session dependency, add the object, commit the transaction, and refresh the object to get the generated ID.

@app.post("/heroes/", response_model=Hero)
def create_hero(hero: Hero, session: Session = Depends(get_session)):
    session.add(hero)
    session.commit()
    session.refresh(hero)
    return hero

Read (GET)

We can use select statements to query data. SQLModel is smart enough to convert the database results back into standard Python objects.

from sqlmodel import select

@app.get("/heroes/", response_model=list[Hero])
def read_heroes(session: Session = Depends(get_session)):
    heroes = session.exec(select(Hero)).all()
    return heroes

By integrating the database session as a dependency, your code remains clean, testable, and robust against resource leaks.