FastAPI Query Filtering and Pagination

Your API returns every row in your database the first time a client asks for data, and filters are either impossible or require mutating URLs by hand. That is a fast track to slow responses, angry frontend developers, and mobile apps that churn through gigabytes of bandwidth. In this lesson you will learn how to handle query filtering and pagination in FastAPI, so your endpoints stay fast, predictable, and scalable — even as your data grows from dozens to millions of rows.

The problem this lesson solves

Picture a GET /items endpoint that does SELECT * FROM items. It works fine when you have 10 rows. When you have 10,000, the response either times out or blows up your server’s memory. Worse, clients that only need red, in-stock items under $20 still get the entire catalog. This isn’t just inefficient — it’s a bad API design that hurts every consumer.

Two problems appear together:

• Filtering — you need a clean, flexible way for clients to narrow results (by category, price range, status, etc.) without writing custom endpoints for every combination.
• Pagination — you need to slice results into manageable pages so responses stay small and predictable.

If you ignore these, you’ll face slow queries, poor user experience, and API consumers who hack around your limitations with client-side filtering — which is always worse.

Core concept / mental model

Think of your database table as a giant bookshelf. Filtering is like asking the librarian for only sci-fi books published after 2000. Pagination is like saying “give me the first 25 from that shelf, not all 500.”

In FastAPI, both are implemented through query parameters — the ?key=value part of a URL. FastAPI automatically parses them into typed function parameters, so a simple endpoint signature can become a powerful, self-documenting query tool.

A key distinction:

• Path parameters (/items/{item_id}) identify a single resource.
• Query parameters (/items?category=scifi&page=2) shape the result set — they filter, sort, and paginate.

FastAPI’s magic: if you declare a function parameter that is not part of the path, it is automatically treated as a query parameter. You don’t write any manual parsing. This is the mental model to hold onto — query parameters are just typed function arguments.

How it works step by step

1. Declare your endpoint — create functions with query parameters like category, min_price, max_price, skip, and limit.
2. Let FastAPI validate — types like str, int, and float are enforced automatically. Invalid inputs return a 422 error with a clear message.
3. Build the query incrementally — start with a base select(), then add conditions only for parameters the client provided.
4. Order matters — filter before paginating, so you count and slice the correct subset.
5. Return a structured response — include the data plus pagination metadata (total, page, page_size) so clients know how to get the next page.

Hands-on walkthrough

Let’s build a real endpoint with both filtering and pagination. We’ll use SQLModel (or SQLAlchemy) for the model and SQLite for simplicity.

1. Define a simple model

from sqlmodel import SQLModel, Field
from typing import Optional

class Item(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    category: str
    price: float
    in_stock: bool = True

2. Endpoint with query parameters

from fastapi import FastAPI, Query, Depends
from sqlmodel import Session, select

app = FastAPI()

def get_session():
    with Session(engine) as session:
        yield session

@app.get("/items")
def list_items(
    session: Session = Depends(get_session),
    category: Optional[str] = Query(None, description="Filter by category"),
    min_price: Optional[float] = Query(None, ge=0, description="Minimum price"),
    max_price: Optional[float] = Query(None, description="Maximum price"),
    in_stock: Optional[bool] = Query(None, description="Filter by stock status"),
    page: int = Query(1, ge=1, description="Page number"),
    page_size: int = Query(25, ge=1, le=100, description="Items per page")
):
    query = select(Item)

    # Filtering
    if category:
        query = query.where(Item.category == category)
    if min_price is not None:
        query = query.where(Item.price >= min_price)
    if max_price is not None:
        query = query.where(Item.price <= max_price)
    if in_stock is not None:
        query = query.where(Item.in_stock == in_stock)

    # Count before pagination
    total = len(session.exec(query).all())

    # Pagination
    offset = (page - 1) * page_size
    items = session.exec(query.offset(offset).limit(page_size)).all()

    return {
        "items": items,
        "total": total,
        "page": page,
        "page_size": page_size,
        "total_pages": (total + page_size - 1) // page_size,
    }

Expected behavior: Request GET /items?category=books&min_price=10&page=2&page_size=5 returns only books priced at $10 or more, showing the second page of 5 items, with metadata to reach the next page.

3. Test it yourself

Run the server and try these URLs in your browser or with curl:

# First page, default size
curl "http://localhost:8000/items"

# Filtered and paginated
curl "http://localhost:8000/items?category=electronics&min_price=100&page=3&page_size=10"

# Invalid input → 422
curl "http://localhost:8000/items?page=0"

Pro tip: Always compute offset as (page - 1) * page_size. If you ever find yourself doing page * page_size, you are skipping the first page.

Compare options / when to choose what

For your first implementation, offset pagination is the right choice — it’s what most teaching materials assume, and it’s perfectly fine for the majority of APIs. As you grow, you can swap in cursor pagination for specific hot paths without changing your API shape drastically.

Pro tip: Keep page_size bounded (<= 100) to prevent clients from requesting 1 million records at once. FastAPI’s Query validation makes this one-line.

Troubleshooting & edge cases

• Filtering doesn’t work for optional values: In Python, if category: fails for empty strings and False for booleans. Use if category is not None: for strings and booleans, and if min_price is not None: for numbers — 0 is a valid price!
• Page count is off: If you compute total_pages before counting, you’ll undercount. Always fetch the total after applying filters but before pagination.
• Slow queries with large offsets: OFFSET 100000 forces the database to scan 100,000 rows. If you hit this, switch to keyset pagination or add indexes.
• 422 Unprocessable Entity errors: Usually means a query parameter has the wrong type or violates a constraint (e.g., page=0 when ge=1). Read the response body — FastAPI tells you exactly which field failed.
• Zero results after filtering: That’s normal. Return items: [] and total: 0 — don’t treat it as an error. Your client can show an empty state.

What you learned & what's next

You now understand how to handle query filtering and pagination in FastAPI: you can turn a blunt GET /items into a precise, scalable query tool using typed query parameters, incremental query building, and offset pagination. You know how to validate inputs, compute pagination metadata, and choose between pagination strategies. These skills appear in virtually every production API — from e-commerce catalogs to analytics dashboards.

Great work! This lesson is part of your FastAPI Backend Development track — you’re building methodically. Up next, we’ll cover response models and serialization, where you’ll learn to shape the data you return per endpoint — hiding internals, excluding secrets, and versioning your API cleanly. Stay on track!