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"],
)
Bookis an ordinary SQLModel table model: the page carries it as it is.get_sessionopens anAsyncSessionper request, as in the SQLModel tutorial;paginateonly reads through it and leaves it open, so the endpoint can keep using it.current_pageanditems_by_pageare 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.filtersisNonewhen no author is given, which reads every book.
With six books by Jane Austen in the table, GET /books?author=jane austen¤t_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:
- the items: the same statement with
OFFSET (current_page - 1) * items_by_pageandLIMIT items_by_page; - 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
infilter whose value is not a list, a tuple or a set raisesValueError; - 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.