Pagination, Filtering, and Sorting

Published by Programming Line in FastAPI. Last updated .

Pagination, filtering, and sorting turn an unbounded collection into a predictable API response. In FastAPI, the work begins at the query string: clients send parameters such as limit, offset, status, sort, or cursor, and the endpoint converts those parameters into validated Python values before asking storage for a bounded slice.

The outcome is not just a shorter response. A well-designed collection endpoint protects the server from huge scans, gives clients repeatable navigation, documents valid choices in OpenAPI, and prevents malformed sort or filter values from becoming database errors or unsafe dynamic queries. This belongs in FastAPI API design because collection endpoints are among the first contracts clients depend on, and small mistakes become painful once datasets grow.

Purpose and Result

A collection endpoint should answer three questions: which records match, in what order they appear, and how many records are returned now. Filtering answers the first question, sorting answers the second, and pagination answers the third. A good response also tells the client enough to ask for the next slice without guessing.

For example, GET /orders?status=paid&sort=-created_at&limit=25 should mean: return at most twenty-five paid orders, ordered by creation time descending. If status is unknown, limit is too large, or sort names a private field, the API should reject the request before the database query is built.

How FastAPI Handles Query Parameters

FastAPI reads query parameters from the ASGI request and matches them to function parameters. Type hints and Query metadata tell FastAPI how to parse and validate each value. A parameter annotated as int becomes an integer or returns a validation error. A parameter constrained with ge and le enforces lower and upper bounds. A Literal restricts values to a known set, which is especially useful for filters and sort keys.

That validation happens before your endpoint body runs. This is the right place to enforce syntactic rules: page size, offset range, enum-like filter values, and public sort names. Semantic rules, such as whether a user may see archived rows, usually belong in the service or repository layer because they depend on identity and authorization.

Offset pagination uses two numbers: offset says how many matching rows to skip, and limit says how many to return. It is simple and works well for small or moderately sized lists. Its weakness is instability during concurrent writes. If a new row is inserted before the current page, a later offset can skip or duplicate records.

Cursor pagination uses a position marker instead of a count of skipped rows. The cursor is usually derived from the last item on the current page, such as created_at plus id. The next query asks for rows after that position. This is more stable for feeds, timelines, and large tables, but the cursor format must be treated as an API contract.

API Anatomy

A practical FastAPI collection endpoint has four parts. First, query parameter declarations describe the public API. Second, allowlists map public filter and sort names to internal fields. Third, query construction applies filters before sorting and pagination. Fourth, the response shape includes the items and navigation metadata.

Do not pass arbitrary query parameter names directly into an ORM order_by or raw SQL string. Public API names should be translated through a dictionary or explicit branch. That lets you expose created while sorting by orders.created_at, and it prevents a caller from sorting by an internal column such as cost_basis or deleted_at.

Example 1: Offset Pagination

This first example uses an in-memory list so the mechanics are visible. The endpoint accepts limit and offset, caps the limit at 50, and returns pagination metadata with the items.

from typing import Annotated

from fastapi import FastAPI, Query

app = FastAPI(title="Collection API")

PRODUCTS = [
    {"id": 1, "name": "Notebook", "status": "active", "price": 9.50},
    {"id": 2, "name": "Pencil", "status": "active", "price": 1.25},
    {"id": 3, "name": "Desk lamp", "status": "archived", "price": 32.00},
    {"id": 4, "name": "Backpack", "status": "active", "price": 48.00},
]

@app.get("/products")
def list_products(
    limit: Annotated[int, Query(ge=1, le=50)] = 20,
    offset: Annotated[int, Query(ge=0)] = 0,
):
    page = PRODUCTS[offset : offset + limit]
    return {
        "items": page,
        "limit": limit,
        "offset": offset,
        "total": len(PRODUCTS),
    }

Calling /products?limit=2&offset=1 returns products with ids 2 and 3, plus limit, offset, and total. Calling /products?limit=500 does not reach the endpoint body; FastAPI returns a validation error because the declared maximum is 50.

Example 2: Filtering and Validated Sorting

The next version adds a public filter and an allowlisted sort parameter. The leading minus sign means descending order. The endpoint supports only the public sort values named in the type annotation.

from typing import Annotated, Literal

from fastapi import FastAPI, Query

app = FastAPI(title="Filtered Collection API")

PRODUCTS = [
    {"id": 1, "name": "Notebook", "status": "active", "price": 9.50},
    {"id": 2, "name": "Pencil", "status": "active", "price": 1.25},
    {"id": 3, "name": "Desk lamp", "status": "archived", "price": 32.00},
    {"id": 4, "name": "Backpack", "status": "active", "price": 48.00},
]

SortKey = Literal["name", "-name", "price", "-price"]
StatusFilter = Literal["active", "archived"]

@app.get("/products/search")
def search_products(
    status: StatusFilter | None = None,
    sort: SortKey = "name",
    limit: Annotated[int, Query(ge=1, le=50)] = 20,
    offset: Annotated[int, Query(ge=0)] = 0,
):
    rows = PRODUCTS
    if status is not None:
        rows = [row for row in rows if row["status"] == status]

    descending = sort.startswith("-")
    field = sort.removeprefix("-")
    rows = sorted(rows, key=lambda row: row[field], reverse=descending)

    return {
        "items": rows[offset : offset + limit],
        "limit": limit,
        "offset": offset,
        "count": len(rows),
    }

Calling /products/search?status=active&sort=-price&limit=2 returns Backpack first and Notebook second, because only active products are considered and the remaining rows are sorted by price descending. Calling /products/search?sort=deleted_at returns a validation error because deleted_at is not part of the public sort contract.

Example 3: Stable Cursor Pagination

Offset pagination is easy to understand, but it can drift when new records are inserted. Cursor pagination avoids counting skipped rows. This example orders events by descending id and uses the last id from the current page as the next cursor.

from typing import Annotated

from fastapi import FastAPI, Query

app = FastAPI(title="Cursor Collection API")

EVENTS = [
    {"id": 105, "message": "payment captured"},
    {"id": 104, "message": "invoice sent"},
    {"id": 103, "message": "order packed"},
    {"id": 102, "message": "order created"},
]

@app.get("/events")
def list_events(
    after_id: int | None = None,
    limit: Annotated[int, Query(ge=1, le=100)] = 2,
):
    rows = EVENTS
    if after_id is not None:
        rows = [row for row in rows if row["id"] < after_id]

    page = rows[:limit]
    next_cursor = page[-1]["id"] if len(page) == limit else None
    return {"items": page, "next_after_id": next_cursor}

The first call to /events?limit=2 returns ids 105 and 104 with next_after_id set to 104. The next call to /events?after_id=104&limit=2 returns ids 103 and 102. If a new event with id 106 appears between those calls, the second call still asks for records older than 104, so it does not duplicate the newly inserted row.

Design Choices and Trade-offs

Use offset pagination when clients need simple page numbers, the result set is not enormous, and minor drift is acceptable. Administrative tables often fit this model. Use cursor pagination when rows are frequently inserted, when clients scroll forward through a feed, or when large offsets would force the database to scan and discard many rows.

Expose a small sort vocabulary. Sorting by every database column sounds flexible, but it ties the public API to storage internals and can force expensive indexes. Pick fields that clients actually need and that the database can support efficiently. For stable ordering, add a deterministic tie-breaker such as id when multiple rows can share the same visible sort value.

Filtering should also be explicit. Equality filters such as status=active are easy to validate and index. Text search, date ranges, and multi-value filters need more design. Decide whether repeated parameters, comma-separated values, or structured names such as created_after and created_before best match your API style.

Failure Modes and Troubleshooting

A common symptom is duplicated or missing rows between pages. With offset pagination, the cause is often concurrent insertions or deletions before the requested offset. Diagnose it by logging the requested offset, limit, sort key, and first and last ids returned. Correct it by using cursor pagination for changing feeds or by documenting that offset pages are snapshots only when backed by a consistent transaction.

Another symptom is slow requests at high page numbers. The cause is usually a large offset that makes the database walk many matching rows before returning a small page. Check query plans and latency by offset size. Correct it by capping offset, adding indexes for the filter and sort combination, or switching to cursor pagination.

A third symptom is a 500 error when a client sends an unexpected sort value. The cause is usually dynamic query construction without validation. Reproduce the request with the exact query string, inspect whether the endpoint type allows arbitrary strings, and correct it with Literal, an enum, or an explicit allowlist that maps public names to internal fields.

Security, Performance, and Reliability

Collection parameters are untrusted input. Even when an ORM quotes identifiers safely, arbitrary sort and filter fields can disclose internal structure, create expensive queries, or bypass intended policy. Keep field allowlists close to the API boundary and apply authorization rules before returning rows.

Performance depends on the combination of filters and sorting, not just on page size. A filter on status followed by a sort on created_at may need a composite index in real storage. Returning total can also be expensive for large filtered tables, so consider whether clients truly need exact totals or whether has_more and a cursor are enough.

Reliability improves when responses are deterministic. Always define a default sort. Avoid returning database natural order, because it is not a contract. When sorting by a non-unique field such as name or timestamp, include a second unique sort key in real database queries so page boundaries do not shift unpredictably among tied rows.

Hands-on Lab

Prerequisites: Python with FastAPI installed, plus a way to run an ASGI app such as Uvicorn. Create a file named main.py with this complete application.

from typing import Annotated, Literal

from fastapi import FastAPI, Query

app = FastAPI(title="Pagination Lab")

ORDERS = [
    {"id": 10, "status": "paid", "total": 120.00},
    {"id": 9, "status": "pending", "total": 15.50},
    {"id": 8, "status": "paid", "total": 42.00},
    {"id": 7, "status": "cancelled", "total": 8.00},
]

OrderStatus = Literal["paid", "pending", "cancelled"]
OrderSort = Literal["id", "-id", "total", "-total"]

@app.get("/orders")
def list_orders(
    status: OrderStatus | None = None,
    sort: OrderSort = "-id",
    limit: Annotated[int, Query(ge=1, le=25)] = 2,
    after_id: int | None = None,
):
    rows = ORDERS
    if status is not None:
        rows = [row for row in rows if row["status"] == status]
    if after_id is not None:
        rows = [row for row in rows if row["id"] < after_id]

    descending = sort.startswith("-")
    field = sort.removeprefix("-")
    rows = sorted(rows, key=lambda row: row[field], reverse=descending)
    page = rows[:limit]
    next_cursor = page[-1]["id"] if len(page) == limit else None
    return {"items": page, "next_after_id": next_cursor, "limit": limit}
  1. Run the application with uvicorn main:app --reload.
  2. Open /docs and inspect the generated controls for status, sort, limit, and after_id.
  3. Call /orders?status=paid&sort=-id&limit=1. Verify that the first item has id 10 and that next_after_id is 10.
  4. Call /orders?status=paid&after_id=10&sort=-id&limit=1. Verify that the next item has id 8.
  5. Call /orders?sort=customer_email. Verify that FastAPI returns a validation error instead of sorting by an unsupported field.

Cleanup is simple: stop Uvicorn and remove the temporary main.py file if you created it only for the lab. If you adapted an existing project, rollback means removing the route and any temporary test data you added.

Assessment Exercises

  • An endpoint returns duplicate rows when users move from page 1 to page 2 during a busy import. Explain why offset pagination can cause this and redesign the endpoint with a cursor.
  • Choose public sort keys for an invoices endpoint. Which keys would you expose, which would you hide, and what indexes would you expect storage to need?
  • Write a FastAPI query parameter declaration that permits limit values from 1 through 100 and rejects anything outside that range.
  • A product manager asks for exact totals on every filtered search. Describe the performance trade-off and propose a response shape that can still support infinite scrolling.
  • Given sort=-created_at, explain why a real database query should also sort by a unique key such as id.

Summary

Pagination bounds how much data a collection endpoint returns, filtering defines which rows are eligible, and sorting makes the order intentional. FastAPI gives you strong boundary tools through type hints, Query constraints, Literal values, and generated OpenAPI documentation. The deeper design work is choosing the right pagination model, exposing only supported filters and sort keys, and making page boundaries deterministic under real data changes.