Pagination

Pagination

sillo.record.pagination implements no pagination logic of its own. It provides two adapter classes that let a Tortoise queryset plug into the strategies already in sillo.pagination, so that querysets, lists, and any other data source all paginate through one pipeline.

There is also a second, unrelated helper — sillo.record.queries.paginate — which is simpler and has a different error contract. Both are covered here, because picking the wrong one is the most common mistake on this page.

The three layers

Layer	Lives in	Responsibility
Strategy	sillo.pagination	Parse query params, compute offset/limit, build links
Data handler	sillo.record.pagination	Ask a specific data source for a count and a slice
Paginator	sillo.pagination	Run the two together and assemble the response

The whole Tortoise adapter is nine lines:

sillo/record/pagination.py

class TortoiseDataHandler(AsyncDataHandler):
    def __init__(self, queryset):
        self._qs = queryset

    async def get_total_items(self) -> int:
        return await self._qs.count()

    async def get_items(self, offset: int, limit: int) -> list:
        return await self._qs.offset(offset).limit(limit).all()

Anything with count(), offset(), limit(), and all() fits. That means a filtered queryset, a scoped queryset, a queryset with prefetch_related, or one with annotations all work unchanged.

When to reach for this

Use the strategy pipeline when you are building an HTTP API and want consistent query parameters, HATEOAS links, and validation across every listing endpoint. It handles parameter parsing, clamping, and link construction so your handlers do not.

Use sillo.record.queries.paginate for internal code — an admin script, a report, a background job — where you want a page of rows and a total and do not care about links or query-string conventions.

Use neither for infinite-scroll feeds over large tables. Both are offset-based, and offset pagination degrades badly past a few thousand rows. Keyset pagination written by hand is the right answer there; the section on cursors below explains why the built-in CursorPagination does not fill that role.

Page-number pagination

The default for most APIs: ?page=2&page_size=20.

a paginated listing endpoint

from sillo.pagination import AsyncPaginator, PageNumberPagination
from sillo.record.pagination import TortoiseDataHandler


@app.get("/users")
async def list_users(request, response):
    paginator = AsyncPaginator(
        data_handler=TortoiseDataHandler(User.active().order_by("-created_at")),
        pagination_strategy=PageNumberPagination(
            default_page_size=20,
            max_page_size=100,
        ),
        base_url=str(request.url).split("?")[0],
        request_params=dict(request.query_params),
    )
    return response.json(await paginator.paginate())

paginate() returns a plain dict — not an object with attributes:

the response shape

{
    "items": [<User>, <User>, ...],
    "pagination": {
        "total_items": 25,
        "total_pages": 3,
        "page": 2,
        "page_size": 10,
        "links": {
            "prev":  "https://api.example.com/users?page=1&page_size=10",
            "next":  "https://api.example.com/users?page=3&page_size=10",
            "first": "https://api.example.com/users?page=1&page_size=10",
            "last":  "https://api.example.com/users?page=3&page_size=10",
        },
    },
}

result["items"] holds model instances. response.json runs them through sillo’s encoder, which serializes every column — including password_hash if you have one. Project explicitly for anything public:

controlling the payload

result = await paginator.paginate()
return response.json({
    "items": [u.to_dict(include=["id", "email", "name"]) for u in result["items"]],
    "pagination": result["pagination"],
})

Limit-offset pagination

?limit=20&offset=40. Same shape, different parameter names, and no page-number arithmetic.

limit-offset

from sillo.pagination import LimitOffsetPagination

paginator = AsyncPaginator(
    data_handler=TortoiseDataHandler(Post.all().order_by("id")),
    pagination_strategy=LimitOffsetPagination(default_limit=20, max_limit=100),
    base_url=str(request.url).split("?")[0],
    request_params=dict(request.query_params),
)

Prefer this when clients compose their own ranges — bulk exporters, sync clients, and admin tooling usually want offsets rather than pages.

Filters survive into the links

LinkBuilder strips the pagination parameters from request_params and merges the rest into every generated link, so a filtered or searched listing keeps its filters as the client pages through:

filters are preserved

request_params = {"page": "2", "tag": "news", "q": "async"}

# next -> https://api.example.com/posts?tag=news&q=async&page=3&page_size=5

That is the behaviour you want, and it is why request_params should be the full query dict rather than just the pagination keys.

Pass a base_url without a query string

LinkBuilder appends ? unconditionally. If base_url already contains a query string — which str(request.url) does — you get two:

https://api.example.com/posts?tag=news?page=2&page_size=5

Everything after the second ? is parsed as part of the tag value, so the links are broken for any request that arrived with query parameters.

Strip the query string before passing it:

base_url = str(request.url).split("?")[0]

The error contract

This is the part most implementations get wrong, because the three failure modes behave differently.

Input	Result
?page=99 on 25 rows	raises InvalidPageError("Requested offset exceeds total items")
?page=0	raises InvalidPageError("Page number must be at least 1")
?page_size=1000 with max_page_size=20	silently clamped to 20
?page=abc	raises a bare ValueError from int()
any page on an empty table	returns {"items": [], ...} — no error

?page=abc raises ValueError, not a pagination error

The out-of-range cases raise InvalidPageError, a subclass of PaginationError, which you can map to a 400. A non-numeric page raises ValueError straight out of int() and is not part of the pagination error hierarchy. Catching only PaginationError leaves that path returning a 500 to anyone who types a page number wrong.

Handle both, once, in a middleware or an exception handler rather than in every listing endpoint:

mapping pagination errors to 400

from sillo.pagination import PaginationError


@app.add_exception_handler(PaginationError)
async def handle_pagination_error(request, response, exc):
    return response.json({"error": "Invalid pagination", "detail": str(exc)},
                         status_code=400)


@app.add_exception_handler(ValueError)
async def handle_value_error(request, response, exc):
    return response.json({"error": "Invalid parameter", "detail": str(exc)},
                         status_code=400)

If you would rather have an out-of-range page return an empty list than an error — often the friendlier behaviour for a UI that remembers the last page a user visited — turn the check off:

paginator = AsyncPaginator(..., validate_total_items=False)

Cursor pagination

CursorPagination exists in sillo.pagination, and the TortoiseDataHandler docstring lists it as supported. Two problems make that claim untrue as written.

CursorPagination does not work with TortoiseDataHandler

CursorPagination.generate_metadata builds the next cursor with items[-1][self.sort_field] — subscript access, which assumes the items are dicts. TortoiseDataHandler.get_items returns model instances:

TypeError: 'Post' object is not subscriptable

A handler returning dict rows avoids the crash:

class DictDataHandler(TortoiseDataHandler):
    async def get_items(self, offset, limit):
        return await self._qs.offset(offset).limit(limit).values("id", "title")

The cursor value is used as an OFFSET, not a keyset

calculate_offset_limit returns cursor_data[self.sort_field] as the offset. The cursor encodes the last row’s id, and that id is then fed to OFFSET. It appears to work when ids happen to be a dense sequence starting near 1, and breaks as soon as they are not:

ids in the table:  1000, 1003, 1006, 1009, ...
page 1 returns:    1000, 1003, 1006
cursor encodes:    {"id": 1006}
page 2 issues:     OFFSET 1006 on a 10-row table

InvalidPageError: Requested offset exceeds total items

Any table with deleted rows, a non-1 sequence start, a UUID primary key, or a sort_field that is not the primary key will misbehave — sometimes by skipping rows silently, sometimes by raising.

Do not use CursorPagination for keyset pagination. Write it directly against the queryset instead, which is both correct and faster:

real keyset pagination

@app.get("/feed")
async def feed(request, response):
    after = request.query_params.get("after")
    qs = Post.all().order_by("-id")
    if after:
        qs = qs.filter(id__lt=int(after))
    rows = await qs.limit(21)
    has_more = len(rows) > 20
    rows = rows[:20]
    return response.json({
        "items": [r.to_dict() for r in rows],
        "next": str(rows[-1].id) if has_more and rows else None,
    })

Fetching one extra row is how you learn whether a next page exists without a COUNT(*).

sillo.record.queries.paginate

A standalone helper unrelated to the strategy pipeline. One call, one result object, no query-parameter parsing.

the simple helper

from sillo.record import paginate

result = await paginate(Order.all(), page=2, page_size=50, ordering="-created_at")

result.items      # list of Order
result.total      # 1240
result.page       # 2
result.pages      # 25
result.has_next   # True
result.has_prev   # True
result.to_dict()  # a JSON-ready dict, items serialized via to_dict()

PaginatedResult.to_dict() calls item.to_dict() on each row, which for sillo.record.Model instances includes reverse relations — see Models & Mixins. Build the payload yourself if the model has any.

This helper validates nothing

page_size=0 raises ZeroDivisionError from the pages property. page=0 produces a negative offset and raises Tortoise’s ParamsError: Offset should be non-negative number. Neither is a clean 400.

Never pass user input to it directly. Clamp first:

page = max(1, int(request.query_params.get("page", 1)))
page_size = min(100, max(1, int(request.query_params.get("page_size", 20))))

Or use the strategy pipeline, which does this for you.

Sibling query helpers

sillo.record.queries ships three more functions worth knowing about, with one caveat each.

iter_all(queryset, batch_size=500) iterates a whole table in batches so you never hold it all in memory. It pages by offset, so on a large table the later batches get progressively slower, and rows inserted or deleted mid-iteration can be skipped or repeated. Always pass a deterministically ordered queryset, and prefer a keyset loop for tables that change while you read them.

find_by_ids(queryset, ids) is filter(pk__in=ids). Watch the list length — most databases have a parameter limit around 32,000 and practical performance falls off well before that. Chunk large id lists.

count_by(queryset, field) loads every row and counts in Python. On anything but a small table use SQL:

from tortoise.functions import Count

rows = await Order.all().annotate(n=Count("id")).group_by("status").values("status", "n")

explain(queryset) is currently non-functional: it unpacks queryset.sql() into two values, but current Tortoise returns a single string, so it always returns the string "EXPLAIN unavailable: too many values to unpack (expected 2)". Because it catches every exception and returns the message as a normal value, nothing raises to tell you. Run EXPLAIN through the connection instead:

from tortoise import connections

conn = connections.get("default")
plan = await conn.execute_query(f"EXPLAIN QUERY PLAN {queryset.sql()}")

SyncTortoiseDataHandler

Despite the name, it takes a list, not a queryset, and slices it in memory:

SyncTortoiseDataHandler([1, 2, 3, 4, 5])

It exists so SyncPaginator has something to talk to. It never touches Tortoise. Use it to paginate an already-materialised list; do not expect it to run queries.

Performance notes

Every paginate() issues two queries: a COUNT(*) over the filtered set and a SELECT ... LIMIT ... OFFSET. The count is often the more expensive of the two, because it cannot use a covering index the way the limited select can. On tables past a few million rows, consider validate_total_items=False plus an approximate count, or drop the total from the response entirely.

OFFSET n makes the database read and discard n rows. Page 1 is instant; page 5,000 at 20 per page reads 100,000 rows to return 20. This is inherent to offset pagination, not to sillo. If deep pages are a real access pattern, use keyset pagination.

An unordered queryset has no defined row order. Two requests for the same page can return different rows, and a row can appear on two consecutive pages. Always order_by something unique — typically the primary key, or ("-created_at", "id") when sorting by a non-unique column.

prefetch_related composes correctly with the handler and is the fix for N+1 queries in a paginated listing that touches relations.

What not to do

Do not use CursorPagination with model instances. It raises TypeError.

Do not treat CursorPagination as keyset pagination. The cursor is an offset.

Do not paginate an unordered queryset. Rows will repeat and vanish between pages.

Do not pass raw query parameters to queries.paginate. It has no validation and raises ZeroDivisionError on page_size=0.

Do not catch only PaginationError. A non-numeric page raises ValueError.

Do not return result["items"] unprojected from a public endpoint. The encoder emits every column.

Do not call count_by on a large table. It fetches everything.

Do not trust explain(). It returns its error message as a normal string.

API reference

Name	Signature	Notes
TortoiseDataHandler	(queryset)	get_total_items(), get_items(offset, limit)
SyncTortoiseDataHandler	(data: list)	Takes a list, not a queryset
AsyncPaginator	(data_handler, pagination_strategy, base_url, request_params, validate_total_items=True)	await .paginate(**overrides) -> dict
PageNumberPagination	(page_param="page", page_size_param="page_size", default_page=1, default_page_size=20, max_page_size=100)
LimitOffsetPagination	(limit_param="limit", offset_param="offset", default_limit=20, max_limit=100)
CursorPagination	(cursor_param="cursor", page_size_param="page_size", default_page_size=20, max_page_size=100, sort_field="id")	Offset-based; needs dict rows
queries.paginate	(queryset, page=1, page_size=20, *, ordering=None) -> PaginatedResult	No validation
queries.iter_all	(queryset, batch_size=500) -> AsyncIterator	Offset-based
queries.find_by_ids	(queryset, ids) -> list
queries.count_by	(queryset, field) -> dict	Loads every row
queries.explain	(queryset) -> str	Returns an error string; non-functional

Related

• Pagination strategies — the strategy layer, link building, and error types
• Record Overview — setup and connection lifecycle
• Models & Mixins — to_dict() and what it includes
• Scopes & Events — scoping a queryset before paginating it
• Error Handling — registering the exception handlers shown above
• Sending Responses — how response.json encodes what you hand it