Designing Filter, Sort and Field-Selection Query Params Without Reinventing SQL

Every list endpoint eventually gets a feature request that starts “can the client just filter by…” and ends, six months later, with a query string that is a worse version of SQL bolted onto a public API.

There are two ways to get this wrong, and most teams manage both in sequence. The first is to invent a bespoke filter syntax one parameter at a time — ?min_price=, then ?max_price=, then ?price_between= — until the endpoint has forty query parameters and no coherent grammar. The second is to over-correct by adopting a full query language like OData or RSQL, handing the client the ability to filter and sort on any column it can name, and discovering later that you have effectively exposed your database schema and your index gaps to the internet. The right answer sits between them: a small, explicit grammar that is powerful enough to be useful and bounded enough that it can only ever touch columns you have deliberately allowed.

The grammar, before the code

Decide the shape of the query string first, because it is the part you cannot change later without breaking clients. Three parameters carry the whole load.

• Filtering uses an operator suffix in brackets: ?price[gte]=100&price[lte]=500&status[eq]=active. The bracket carries the operator, so a field can appear more than once with different operators and the meaning stays obvious. A bare ?status=active is treated as equality for convenience.
• Sorting is a single comma-separated list with a direction prefix: ?sort=-created_at,name means created_at descending, then name ascending. Multi-key sort falls out naturally from the ordering of the list.
• Field selection — sparse fieldsets — is another comma-separated list: ?fields=id,name,price. The client asks for the columns it needs and you stop serialising the ones it does not.

This is a deliberately small operator set: eq, gte, lte, gt, lt and in. That covers the overwhelming majority of real list-endpoint filtering. Resist the urge to add like or free-text substring matching to this grammar on day one — unanchored LIKE '%term%' cannot use a standard B-tree index and turns a cheap query into a full table scan. Search is a different feature with different infrastructure; do not let it sneak in through the filter parameter.

The allowlist is the whole game

The single decision that makes this safe is that clients never name columns — they name entries in an allowlist you control. A filter parameter of price[gte] is not a column reference; it is a lookup key. If price is not in the map, the request is rejected with a 400 before a query is ever built. This is what closes the injection surface: user input selects which pre-defined column object to use, never the SQL text itself. There is no string interpolation into a query anywhere in the design.

The allowlist does a second job that matters just as much for performance. Every column you allow for filtering or sorting should be backed by an index. The allowlist is therefore not just a security boundary — it is a promise about what the database can answer cheaply. Allowing a client to sort by a non-indexed column is how you end up with a ?sort= parameter that quietly runs a filesort over a million rows on every request. Keep the filterable and sortable sets narrow, and make “is there an index for this?” a required question in code review whenever someone adds an entry. This is exactly the kind of decision that quietly shapes your API long before anyone calls it a design choice.

A FastAPI dependency that parses into a validated query

Here is the parser as a FastAPI dependency, targeting FastAPI 0.115, SQLAlchemy 2.0 and PostgreSQL 17. It reads the raw query string, validates every key against the allowlists, and returns pre-built SQLAlchemy expression objects. The endpoint then applies them to a select() and never touches user strings again.

import re
from dataclasses import dataclass, field
from typing import Any

from fastapi import Depends, FastAPI, HTTPException, Request
from sqlalchemy import Select, select
from sqlalchemy.orm import InstrumentedAttribute

from .models import Product  # a SQLAlchemy 2.0 mapped class

# Public param name -> the actual mapped column. Only index-backed
# columns belong here; the map is the security and performance boundary.
FILTERABLE: dict[str, InstrumentedAttribute[Any]] = {
    "price": Product.price,
    "status": Product.status,
    "created_at": Product.created_at,
}
SORTABLE = {"price", "created_at", "name"}      # each has a supporting index
SELECTABLE = {"id", "name", "price", "status", "created_at"}

# Operator suffix -> a factory returning a SQLAlchemy expression.
OPERATORS = {
    "eq":  lambda col, v: col == v,
    "gte": lambda col, v: col >= v,
    "lte": lambda col, v: col <= v,
    "gt":  lambda col, v: col > v,
    "lt":  lambda col, v: col < v,
    "in":  lambda col, v: col.in_(v.split(",")),
}

_FILTER_KEY = re.compile(r"^(?P<field>[a-z_]+)[(?P<op>[a-z]+)]$")


@dataclass
class ListQuery:
    filters: list = field(default_factory=list)
    order_by: list = field(default_factory=list)
    columns: list[str] = field(default_factory=list)


def parse_list_query(request: Request) -> ListQuery:
    filters, order_by = [], []

    for key, value in request.query_params.multi_items():
        if key in ("sort", "fields"):
            continue
        match = _FILTER_KEY.match(key)
        if match is None:                 # bare key -> equality shortcut
            if key in FILTERABLE:
                filters.append(FILTERABLE[key] == value)
                continue
            raise HTTPException(400, f"Field not filterable: {key}")
        name, op = match.group("field"), match.group("op")
        if name not in FILTERABLE:
            raise HTTPException(400, f"Field not filterable: {name}")
        if op not in OPERATORS:
            raise HTTPException(400, f"Unknown operator: {op}")
        filters.append(OPERATORS[op](FILTERABLE[name], value))

    sort = request.query_params.get("sort")
    if sort:
        for token in (t.strip() for t in sort.split(",") if t.strip()):
            descending = token.startswith("-")
            col_name = token.lstrip("+-")
            if col_name not in SORTABLE:
                raise HTTPException(400, f"Field not sortable: {col_name}")
            col = getattr(Product, col_name)
            order_by.append(col.desc() if descending else col.asc())

    fields = request.query_params.get("fields")
    columns = []
    if fields:
        for col_name in (c.strip() for c in fields.split(",") if c.strip()):
            if col_name not in SELECTABLE:
                raise HTTPException(400, f"Field not selectable: {col_name}")
            columns.append(col_name)

    return ListQuery(filters, order_by, columns or sorted(SELECTABLE))


app = FastAPI()


@app.get("/products")
async def list_products(q: ListQuery = Depends(parse_list_query), session=Depends(get_session)):
    stmt: Select = select(*[getattr(Product, name) for name in q.columns])
    for clause in q.filters:
        stmt = stmt.where(clause)
    if q.order_by:
        stmt = stmt.order_by(*q.order_by)
    stmt = stmt.limit(100)  # always bound the result set
    rows = (await session.execute(stmt)).mappings().all()
    return list(rows)

A few things are load-bearing. The parser reads from request.query_params.multi_items() so that a repeated key like price appearing under two operators is preserved rather than collapsed. Every branch either produces a validated expression object or raises a 400 — there is no path where an unrecognised field silently does nothing. And the endpoint always applies a limit(). An unbounded list endpoint is a denial-of-service waiting for a client to omit pagination; couple this grammar with proper keyset pagination and a hard ceiling.

Why not just expose RSQL or OData

Because a full query language is a support and security commitment you are unlikely to have costed. RSQL and OData are genuinely good specifications, and there are mature parsers for both. But adopting one means you now own the behaviour of arbitrary client-composed predicates — nested boolean logic, joins across relationships, functions the client can invoke — against a schema that will change. The moment a client can filter on any column, every index you drop and every column you rename becomes a potential breaking change or a performance cliff, and you find out in production. You also inherit the parser’s own attack surface.

Expose a full query language only when the API is genuinely a query product — an analytics or reporting surface where flexible querying is the feature — and when you have the appetite to govern it as such. For an ordinary list endpoint, the bounded grammar above gives clients ninety-five per cent of what they actually ask for while keeping the schema, the indexes and the query planner firmly on your side of the boundary.

This is the same discipline that keeps the rest of an API honest: the point of idempotency keys on POST or of scoping and hashing API keys is to make the safe path the only path a client can take. A filter grammar is no different. Give clients an expressive, well-documented syntax — and make it structurally impossible for that syntax to reach a column you did not choose to expose.

The test of a good filter parameter is not how much it can do. It is that you can read the allowlist and know, exactly, everything a client could ever ask your database to do.