Filtering & Pagination#

Choosing keys#

Use CursorFilter("id", limit=20) for one ascending key, or CursorFilter(["created_at", "id"], limit=20) for several ascending keys. For explicit directions, pass pairs such as CursorFilter([("created_at", "desc"), ("id", "desc")], limit=20). You can mix these forms with CursorKey objects when a key needs NULL placement or a result alias. Framework cursor_keys settings accept the same forms.

Choose stable sort columns and make the final key unique for each result row. For example, a timestamp may be shared by many rows, so follow it with the primary key. Keep the keys, directions, and NULL placement the same when you reuse a token.

Select every key column, even when schema_type leaves it out of the response. For a qualified name such as items.created_at, the default result name is created_at. If the query gives that column an alias, name it with CursorKey("items.created_at", "desc", result_name="created"). Grouped, DISTINCT, and set-operation queries must expose all keys as output columns.

NULL values#

For a nullable key, set nulls="first" or nulls="last" on CursorKey. For cursor keys, nulls=None declares that the key is non-nullable. A NULL key value in a returned row raises ImproperConfigurationError. This differs from OrderByFilter, where nulls=None uses the database's NULL order.

Signed cursors#

Pass secret= to CursorFilter to sign tokens with HMAC. Use the same secret for every request that shares tokens, and keep it in server-side configuration. Public endpoints should set a secret: without one, a client can change the values and types carried by a token.

Cursors are encoded, not encrypted. Clients can read the sort-key values, even when schema_type omits those columns from the returned items. A signature prevents changes to the token; the query's own WHERE clause remains the access-control boundary. Apply tenant and user restrictions on every request.

Invalid cursors#

Malformed tokens, bad signatures, and tokens for a different ordering raise sqlspec.exceptions.InvalidCursorError. Catch that error at an application boundary if you use the driver directly. The framework filter providers map it to a validation response: HTTP 400 in Litestar and HTTP 422 in FastAPI.

Custom value types#

Built-in encoders preserve common Python values, including dates, timestamps, UUIDs, decimals, and bytes. Register other types once at application startup with a unique tag and functions that turn the value into text and back:

from ipaddress import IPv4Address
from sqlspec.core import register_cursor_type

register_cursor_type(IPv4Address, "ipv4", str, IPv4Address)

The database driver must still support binding the restored value.

Timestamp precision#

Use key values with the same precision as the stored column. A type that rounds timestamps, such as SQL Server's legacy datetime, can re-match a boundary row when the Python value has finer precision. Prefer a type that preserves the full value, and keep a unique final key to break ties.

Query shape#

The cursor filter replaces the query's ORDER BY, LIMIT, and OFFSET. It fetches one extra row to detect another page, then removes that row from items. Grouped, DISTINCT, and set-operation queries are wrapped in a subquery before cursor predicates are applied. In that case, these changes apply to the outer query; inner LIMIT and OFFSET bounds stay in place and cap the rows eligible for paging. Pass exactly one CursorFilter to paginate(); it applies other filters first, then the cursor filter. When using select() directly, place the cursor filter last and pass its returned rows to build_page(). Do not combine cursor pagination with offset pagination or a separate OrderByFilter.

Qualified Field Names#

Every field-name-bearing filter supports table-qualified field names (e.g. p.name). SQLSpec correctly parses these into qualified SQLGlot column references and sanitizes generated parameter names (e.g. p_name_search), making filters safe to use in joined queries.

# Disambiguate columns in a JOIN
query = sql.select("p.name", "c.name").from_("parent p").join("child c", "p.id = c.parent_id")
filter_obj = SearchFilter(field_name="p.name", value="alice")
# Results in: WHERE p.name LIKE :p_name_search

Expression Mode#

Filters like OrderByFilter support passing a SQLGlot expression instead of a string field name. This allows complex sorting and filtering logic:

from sqlglot import exp

# Sort by COALESCE(lines, 0)
expr = exp.func("COALESCE", exp.column("lines"), exp.Literal.number(0))
filter_obj = OrderByFilter(field_name=expr, sort_order="desc")

Search Patterns#

SearchFilter and NotInSearchFilter expose a like_pattern property that returns the percent-wrapped search value (e.g. %alice%). This is useful when you need to use the pattern construction logic outside of the filter system.

Cursor filter dependencies#

Set pagination_type="cursor" and provide the ordered keys. Keep a signing secret in server configuration and pass it as cursor_secret:

import os
from dataclasses import dataclass
from datetime import datetime
from typing import cast

from litestar import get
from litestar.di import NamedDependency
from litestar.params import SkipValidation
from sqlspec.adapters.asyncpg import AsyncpgDriver
from sqlspec.core import CursorPagination, FilterTypes
from sqlspec.extensions.litestar.providers import create_filter_dependencies
from sqlspec.service import SQLSpecAsyncService

@dataclass
class Item:
    id: int
    name: str
    created_at: datetime

cursor_deps = create_filter_dependencies({
    "pagination_type": "cursor",
    "cursor_keys": [("created_at", "desc"), ("id", "desc")],
    "cursor_secret": os.environ["CURSOR_SECRET"],
    "pagination_size": 20,
})

@get("/items", dependencies=cursor_deps)
async def list_cursor_items(
    db_session: AsyncpgDriver,
    filters: NamedDependency[SkipValidation[list[FilterTypes]]],
) -> CursorPagination[Item]:
    service = SQLSpecAsyncService(db_session)
    page = await service.paginate(
        "SELECT id, name, created_at FROM items", *filters, schema_type=Item
    )
    return cast("CursorPagination[Item]", page)

Clients send cursor and pageSize. The first request omits cursor; subsequent requests pass next_cursor or previous_cursor from the response. The default page size is 20 and the default maximum is 1000; set pagination_max_size to change the cap.

When sort_field is configured, clients may also use orderBy and sortOrder. The provider keeps the unique final cursor key as a tiebreaker. Changing the requested ordering starts a new traversal; an old token for a different ordering fails validation. The returned filter list contains a CursorFilter instead of separate offset and ordering filters.