Paginate with cursors in FastAPI

Approach

Use keyset pagination on a unique, indexed sort key (created_at, id). Encode the last item's key as an opaque base64 cursor, fetch limit + 1 rows to know if there's another page, and return {items, next_cursor}.

Solution

import base64, json
from datetime import datetime
from typing import Annotated, Generic, TypeVar
from fastapi import APIRouter, HTTPException, Query
from pydantic import BaseModel
from sqlalchemy import select, tuple_

T = TypeVar("T")
router = APIRouter()


class Page(BaseModel, Generic[T]):
    items: list[T]
    next_cursor: str | None


def encode_cursor(created_at: datetime, id_: int) -> str:
    return base64.urlsafe_b64encode(json.dumps([created_at.isoformat(), id_]).encode()).decode()


def decode_cursor(cursor: str) -> tuple[datetime, int]:
    try:
        ts, id_ = json.loads(base64.urlsafe_b64decode(cursor.encode()))
        return datetime.fromisoformat(ts), int(id_)
    except (ValueError, TypeError):
        raise HTTPException(400, "Invalid cursor")


@router.get("/orders")
async def list_orders(db: DB, user: CurrentUser,
                      limit: Annotated[int, Query(ge=1, le=100)] = 20,
                      cursor: str | None = None) -> Page[OrderOut]:
    stmt = (select(Order).where(Order.user_id == user.id)
            .order_by(Order.created_at.desc(), Order.id.desc()).limit(limit + 1))
    if cursor:
        created_at, id_ = decode_cursor(cursor)
        stmt = stmt.where(tuple_(Order.created_at, Order.id) < (created_at, id_))
    rows = list(await db.scalars(stmt))
    next_cursor = encode_cursor(rows[limit - 1].created_at, rows[limit - 1].id) if len(rows) > limit else None
    return Page(items=rows[:limit], next_cursor=next_cursor)

# CREATE INDEX ix_orders_user_created ON orders (user_id, created_at DESC, id DESC);

Pitfalls

• Sorting by a non-unique column alone.
• No composite index matching filter + sort.
• Letting clients choose arbitrary sort keys with cursors.

Variations

• fastapi-pagination library supports cursor and limit/offset pages.
• Offer total only when cheap (or approximate it).

Further reading: Use The Index, Luke: No offset