skip to content

In FastAPI with SQLModel, how do you write a paginated list endpoint and a read-one endpoint that answers 404 for a missing row?

level: juniorimportance: should knowfreq 35%

answer

  1. statement first, then the session
  2. two calls chained on select()
  3. cap the page size at the edge
  4. what get() returns when absent

basics

~10 s

Build select(Book).offset(offset).limit(limit), run it with session.exec(...).all(), and bound limit with Query(le=100). For one row, session.get(Book, book_id) returns None when nothing matches, so raise HTTPException with status 404.

solid answer

~30 s

The list endpoint takes `offset: int = 0` and `limit: Annotated[int, Query(le=100)] = 100` from the query string, builds `select(Book).offset(offset).limit(limit)` and returns `session.exec(statement).all()` under `response_model=list[BookPublic]`. The `le=100` bound makes FastAPI answer 422 for `?limit=101`, so no client pulls the whole table. Adding `.order_by(Book.id)` keeps pages stable, because SQL promises no row order without it. The read-one endpoint takes `book_id: int` from the path, calls `session.get(Book, book_id)`, and raises `HTTPException(status_code=404, detail="Book not found")` when it gets `None` back; a non-integer id has already been rejected with a 422 before the function runs.

code

python · 44 lines
python
from typing import Annotated

from fastapi import Depends, FastAPI, HTTPException, Query
from sqlmodel import Field, Session, SQLModel, create_engine, select


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


class BookPublic(SQLModel):
    id: int
    title: str


engine = create_engine("sqlite:///catalogue.db", connect_args={"check_same_thread": False})


def get_session():
    with Session(engine) as session:
        yield session


SessionDep = Annotated[Session, Depends(get_session)]
app = FastAPI()


@app.get("/books/", response_model=list[BookPublic])
def read_books(
    session: SessionDep,
    offset: Annotated[int, Query(ge=0)] = 0,
    limit: Annotated[int, Query(ge=1, le=100)] = 100,
):
    statement = select(Book).order_by(Book.id).offset(offset).limit(limit)
    return session.exec(statement).all()


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

go deeper

for a junior

Recall select(Book).offset().limit(), session.exec(...).all(), session.get returning None, and raising HTTPException with 404 when it does.

for a middle

Explain that Query(le=100) is enforced with a 422 rather than clamped, and why the list needs an ORDER BY for pages to be stable.

for a senior

Show the production view: bounds on both paging inputs, the cost of deep offsets, and response models on every read so internal columns never leak.

for a principal

Set the team's paging contract, such as maximum page size and when large lists switch from offsets to filtering on the last seen key.

## The two read endpoints of a catalogue API Almost every FastAPI service backed by SQLModel starts with two reads: a **list endpoint** that returns a page of rows and a **read-one endpoint** that returns a single row by its key, or a 404. Both get a SQLModel `Session` from a yield dependency (here aliased as `SessionDep`); how that session is created and closed is a separate topic. What matters here is how the query is built, how the page size is bounded, and how a missing row becomes an HTTP error. ## The list endpoint, piece by piece 1. **Declare the paging inputs as query parameters.** `offset: int = 0` and `limit: Annotated[int, Query(le=100)] = 100`. Because they are simple types with defaults and not in the path, FastAPI reads them from the query string (`/books/?offset=20&limit=10`) and converts them to `int`. 2. **Build a statement with `select()`.** `select(Book)` comes from `sqlmodel` and produces a statement typed for `Book`. `.offset(offset)` skips rows and `.limit(limit)` caps how many come back; both translate to SQL `OFFSET` and `LIMIT`. 3. **Run it through the session.** `session.exec(statement)` executes the statement and returns a result; `.all()` collects the `Book` objects into a list. 4. **Shape the output.** Declare `response_model=list[BookPublic]` so FastAPI validates each returned `Book` against the public data model and sends only its fields, never internal columns. ## Bounding the page with `Query` `Query(le=100)` is a **validation constraint**, not a clamp. A request for `?limit=101` never reaches the function: FastAPI answers **422** with a `less_than_equal` error located at `query`, `limit`. Without a bound, any client can ask for the whole table in one request, which costs memory, database time and bandwidth. Two more bounds are worth adding in a real service: - `Query(ge=1)` on `limit`, so `?limit=0` is rejected instead of returning an empty page that looks like the end of the data. - `Query(ge=0)` on `offset`, so a negative offset is rejected at the edge with a 422 rather than being passed to the database. The bounds are not only enforced: FastAPI also writes them into the generated OpenAPI schema (`maximum: 100` on `limit`), so `/docs` and any generated client show the page-size limit before anyone hits it. ## Reading one row by key The read-one endpoint declares `book_id: int` in the path (`/books/{book_id}`). FastAPI converts it before the function runs, so `/books/abc` gets a 422 `int_parsing` error and never touches the session. Inside the function: - `session.get(Book, book_id)` looks the row up by primary key and returns the `Book`, or **`None`** when no row has that key. It does not raise. - The endpoint checks the result and raises `HTTPException(status_code=404, detail="Book not found")`, which FastAPI turns into a 404 with the body `{"detail": "Book not found"}`. - With `response_model=BookPublic`, a found row is filtered to the public fields like the list. | Request | What the client gets | |---|---| | `GET /books/?offset=20&limit=10` | 200, up to 10 books after the first 20 | | `GET /books/?limit=101` | 422, `less_than_equal` at `query`, `limit` | | `GET /books/abc` | 422, `int_parsing` at `path`, `book_id` | | `GET /books/999` with no such row | 404, `{"detail": "Book not found"}` | ## Practical cautions - **Order the rows.** The tutorial's query has no `ORDER BY`, and SQL guarantees no order without one, so two requests for consecutive pages can overlap or skip rows. `select(Book).order_by(Book.id).offset(offset).limit(limit)` makes pages stable. - **Deep pages get slower.** With `OFFSET`, the database still walks past every skipped row, so page 5,000 costs more than page 1. Very large lists usually move to filtering on the last seen key, which is a pagination design choice rather than a SQLModel feature. - **Never return a bare list of table objects without a response model.** FastAPI would serialize every column of `Book`, including internal ones. - **Return a total when clients need one.** A page alone does not say how many rows exist; a separate count query with the same filters can feed a wrapper data model, for example `BooksPage` with `items: list[BookPublic]` and `total: int`, used as the response model. - **Keep the 404 explicit.** FastAPI does not translate a `None` return into a 404; returning `None` under `response_model=BookPublic` fails response validation and becomes a 500.

  • Why add `.order_by(Book.id)` when the tutorial's list query has none?
    SQL returns rows in no guaranteed order unless the query says `ORDER BY`, so `OFFSET 20 LIMIT 10` can return different rows on two calls, and a client walking pages may see a book twice or miss one. Ordering by the primary key gives every page a fixed position in one sequence.
  • What does a client get for `GET /books/abc` on an endpoint declaring `book_id: int`?
    A 422 before the function runs: FastAPI converts path parameters first and reports an `int_parsing` error at `path`, `book_id`. `session.get()` is never called, so only a well-formed integer with no matching row reaches the endpoint's own 404 branch.

  • Query(le=100) silently clamps a larger limit to 100.
  • session.get raises an exception when the row does not exist.
  • FastAPI turns a None return value into a 404 automatically.
  • offset and limit give stable pages without any ORDER BY.
  • Returning table objects without a response model is fine for reads.