Stop 09 · API DesignTopics · 4/12

Filtering & Sorting

In short

Nobody scrolls through 5,000 products. They tap 'under 500 rupees' and 'cheapest first'. Your API has to understand those choices from the URL, safely and quickly.

Letting API consumers narrow and reorder result sets via query parameters — a core expectation for any listing endpoint in a real API.

The blob character sieves mangoes into baskets lined up by size.

Think of it like this

Sorting mangoes at the mandi

The fruit seller first sieves out the unripe ones, then lines up the ripe ones from small to big. Filtering removes what you don't want. Sorting arranges what's left. A query string like ?ripe=true&sort=size asks your API to do both.

Watch it happen

GET /products?maxPrice=500&sort=price&limit=2

799

249

450

1200

499

1/5Six products in the table. These are their prices in rupees.

The big ideas

What you need to know

Filters are query params

?category=shoes&maxPrice=500 reads well and works with caching and bookmarks. Keep names simple and documented.

Sort with a whitelist

Accept sort=price or sort=-price, but only for columns you allow. Never paste user input into ORDER BY.

Parameterise every value

Pass filter values as SQL parameters ($1, $2) so a crafted URL can't inject SQL.

Index what people filter

If most searches filter by category and sort by price, an index on (category, price) keeps it quick.

See it in code

Walk through the code

1const SORTABLE = { price: "price", rating: "rating", newest: "created_at" } as const;2 3app.get("/products", async (req, res) => {4  const maxPrice = Number(req.query.maxPrice) || 1_000_000;5  const raw = String(req.query.sort ?? "newest");6  const dir = raw.startsWith("-") ? "DESC" : "ASC";7  const col = SORTABLE[raw.replace("-", "") as keyof typeof SORTABLE] ?? "created_at";8 9  const { rows } = await pool.query(10    `SELECT id, name, price FROM products WHERE price <= $1 ORDER BY ${col} ${dir} LIMIT 20`,11    [maxPrice]12  );13  res.json(rows);14});

1/3A whitelist maps public sort names to real column names. Anything else is ignored.

Column names can't be SQL parameters, which is exactly why the whitelist matters. Try ?sort=-price&maxPrice=500.

Save yourself the pain

Common mistakes

Oops

Building ORDER BY straight from the raw ?sort= value.

Do this instead

That's an SQL injection hole. Map allowed sort keys to known column names and ignore everything else.

Oops

Inventing a different filter style on every endpoint.

Do this instead

Use one convention across the API, like ?field=value and sort=-field, and document it once.

Your mission

Add filters to a product list

~30 min

Quick check

Why can't you pass the sort column as a $1 SQL parameter?

Go deeper

1Add filter and sort query params to a listing endpoint, validating allowed fields.
2Prevent sort-injection by whitelisting sortable columns rather than trusting raw input.
3Design a consistent query param convention, e.g. `?sort=-createdAt`, across your API.