A Database with SQLModel

Introduction

An in-memory dictionary disappears when the server restarts. A database keeps data safe. SQLModel combines SQLAlchemy (a database toolkit) and Pydantic (validation), so one class can describe a table and its data shape. This lesson uses SQLite, a file-based database that needs no server. In production many teams use PostgreSQL. The same code mostly works with another database, by changing the connection URL and installing a driver.

Install: pip install sqlmodel. The examples were run with SQLModel 0.0.48.

Models

from sqlmodel import Field, SQLModel


class BookBase(SQLModel):
    title: str = Field(min_length=1, max_length=100)
    year: int = Field(ge=1450, le=2100)


class Book(BookBase, table=True):
    id: int | None = Field(default=None, primary_key=True)


class BookCreate(BookBase):
    pass


class BookPublic(BookBase):
    id: int

• Book with table=True is a table model. It becomes a database table.
• BookCreate is what clients send, with no id.
• BookPublic is what we return, with an id.
• BookBase holds the shared fields, so rules are written once.

This separation stops clients from setting the id, and lets you keep private columns (such as a password hash) out of public output.

Engine and tables

from sqlmodel import create_engine, SQLModel

engine = create_engine("sqlite:///library.db", connect_args={"check_same_thread": False})
SQLModel.metadata.create_all(engine)

The engine manages connections. create_all creates missing tables (it does not change existing tables). Real projects use migrations with a tool such as Alembic to change a schema safely over time.

check_same_thread=False is a SQLite setting needed because FastAPI may use different threads. It is not needed for other databases.

Create, read, list, delete

from sqlmodel import select


@router.post("", response_model=BookPublic, status_code=201)
def create_book(data: BookCreate, session: SessionDep):
    book = Book.model_validate(data)
    session.add(book)
    session.commit()
    session.refresh(book)      # loads the generated id
    return book


@router.get("", response_model=list[BookPublic])
def list_books(session: SessionDep, offset: int = 0, limit: int = 10):
    return session.exec(select(Book).offset(offset).limit(limit)).all()


@router.get("/{book_id}", response_model=BookPublic)
def get_book(book_id: int, session: SessionDep):
    book = session.get(Book, book_id)
    if not book:
        raise HTTPException(404, "Book not found")
    return book


@router.delete("/{book_id}", status_code=204)
def delete_book(book_id: int, session: SessionDep):
    book = session.get(Book, book_id)
    if not book:
        raise HTTPException(404, "Book not found")
    session.delete(book)
    session.commit()

Output when I ran these with an in-memory database:

201 {'title': 'Dune', 'year': 1965, 'id': 1}
201 {'title': 'Emma', 'year': 1815, 'id': 2}
200 [{'title': 'Dune', ...}, {'title': 'Emma', ...}]
200 [{'title': 'Emma', 'year': 1815, 'id': 2}]      (limit=1, offset=1)
204
404 {'detail': 'Book not found'}                    (after deleting book 1)
422 ...                                              (empty title, year 1)

Querying

select(Book) builds a query. You can add filters:

statement = select(Book).where(Book.year > 1900).order_by(Book.title)
books = session.exec(statement).all()

session.exec(...).first() returns one row or None, and .all() returns a list.

Updating

For PATCH, load the row, apply only the fields the client sent, and commit. A common approach is a model with all fields optional and model_dump(exclude_unset=True).

Safety notes

• SQLModel uses parameterised queries, which protect against SQL injection when you use its query builder. Never build SQL by concatenating user input.
• Do not commit your database file or secrets to source control.
• Use migrations and backups for real data.

Try it yourself

Add a PATCH /books/{id} endpoint that updates only the fields sent. Add a filter query parameter year_after to the list endpoint.

Summary

• SQLModel classes with table=True become tables, and separate create and public models control input and output.
• Sessions add, commit, get, select and delete rows, and the engine manages connections.
• Use parameterised queries through the query builder, and migrations for schema changes.