Skip to content

SQLModel

The SQLModel backend pages a table model over an async session, with one query for the items and one for the count.

Install

$ pip install 'fastapi-object-pagination[sqlmodel]'

The extra installs SQLModel and sqlalchemy[asyncio]. Add the async driver of your database: asyncpg for PostgreSQL, aiosqlite for SQLite.

An endpoint

from collections.abc import AsyncIterator
from typing import Annotated

from fastapi import Depends, FastAPI, Query
from sqlalchemy.ext.asyncio import create_async_engine
from sqlmodel import Field, SQLModel
from sqlmodel.ext.asyncio.session import AsyncSession

from fastapi_object_pagination import Page
from fastapi_object_pagination.sqlmodel import paginate


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


engine = create_async_engine("sqlite+aiosqlite:///books.db")
app = FastAPI()


async def get_session() -> AsyncIterator[AsyncSession]:
    async with AsyncSession(engine) as session:
        yield session


@app.get("/books")
async def list_books(
    session: Annotated[AsyncSession, Depends(get_session)],
    current_page: Annotated[int, Query(ge=1)] = 1,
    items_by_page: Annotated[int, Query(ge=1, le=100)] = 10,
    author: str | None = None,
) -> Page[Book]:
    return await paginate(
        model=Book,
        async_session=session,
        current_page=current_page,
        items_by_page=items_by_page,
        filters={"author__iexact": author} if author else None,
        ordering=["-year", "title"],
    )
  • Book is an ordinary SQLModel table model: the page carries it as it is.
  • get_session opens an AsyncSession per request, as in the SQLModel tutorial; paginate only reads through it and leaves it open, so the endpoint can keep using it.
  • current_page and items_by_page are validated by FastAPI (Query(ge=1)): the library uses the values it is given as they are, and a page past the end comes back with no items.
  • filters is None when no author is given, which reads every book.

With six books by Jane Austen in the table, GET /books?author=jane austen&current_page=2&items_by_page=2 returns:

{
  "items": [
    {"author": "Jane Austen", "title": "Emma", "id": 4, "year": 1815},
    {"author": "Jane Austen", "title": "Mansfield Park", "id": 3, "year": 1814}
  ],
  "item_count": 6,
  "page_count": 3,
  "current_page": 2,
  "items_by_page": 2,
  "previous_page_url": "?current_page=1&items_by_page=2",
  "next_page_url": "?current_page=3&items_by_page=2"
}

The previous and next URLs carry current_page and items_by_page, not the author filter: a client that follows them keeps its own filter parameters.

The call

await paginate(
    model=Book,
    async_session=session,
    current_page=1,
    items_by_page=10,
    filters=None,
    ordering=None,
)
Argument Default Meaning
model the SQLModel table model to read, and the type of the items
async_session an open sqlmodel.ext.asyncio.session.AsyncSession
current_page 1 the page to return, starting at 1
items_by_page 10 the size of a page
filters None a dictionary of "field__lookup": value filters, see filters
ordering None a list of field names, each descending when it starts with -

What it runs

paginate builds select(Book), adds a WHERE clause for the filters and an ORDER BY for the ordering, then sends two queries on the session:

  1. the items: the same statement with OFFSET (current_page - 1) * items_by_page and LIMIT items_by_page;
  2. the count: SELECT count(*) over the statement as a subquery, so the filters narrow the count too.

Filters and ordering

page = await paginate(
    model=Book,
    async_session=session,
    filters={"author__iexact": "jane austen", "year__gte": 1814},
    ordering=["-year", "title"],
)

page.items holds the four books by Jane Austen published in 1814 or later, the most recent first, and page.item_count is 4.

Keys are field names of the model, followed by a lookup. The filters page lists the lookups and the SQL each one becomes.

Errors are raised before any query is sent:

  • a filter on a field the model does not have raises ValueError (Attribute 'nickname' not found in Book);
  • an unknown lookup raises ValueError (Unknown lookup: sounds_like);
  • an in filter whose value is not a list, a tuple or a set raises ValueError;
  • an ordering on a field the model does not have raises AttributeError.

A FastAPI endpoint that builds its filters from user input should therefore choose the field names itself, as list_books does with author.

PostgreSQL

On a PostgreSQL session, {"id__in": [1, 2, 3]} compiles to book.id = ANY(%(param_1)s), the list bound as a single array parameter, instead of book.id IN (...) with one parameter per value, which plans better on long lists. The dialect is read from the session, nothing has to be configured. An empty list matches no row, on every dialect.