In FastAPI with SQLModel, how do you write a paginated list endpoint and a read-one endpoint that answers 404 for a missing row?
answer
- statement first, then the session
- two calls chained on select()
- cap the page size at the edge
- what get() returns when absent
basics
~10 sBuild 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 sThe 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 linesfrom 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 bookgo deeper
Recall select(Book).offset().limit(), session.exec(...).all(), session.get returning None, and raising HTTPException with 404 when it does.
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.
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.
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.