Sorting

beginner2 min read

REST API
Node.js

Letting clients control the order of a collection through query parameters, over whitelisted fields and always with a stable tiebreaker.

What you'll understand

How clients specify the order of a collection
Why sorting needs a whitelist just like filtering
Why a stable sort order matters for pagination

Explanation

Sorting lets a client control the order in which records are returned, newest first, alphabetical by name, highest score at the top. Like filtering, it is expressed through query parameters, commonly a sort field and a direction: GET /users?sort=createdAt&order=desc. On the server this becomes an order-by clause in the database query.

Sorting shares filtering security concerns. A client should not be able to sort by any column it names, because the field goes into the query; whitelist the sortable fields and reject or ignore anything else. Map a small set of allowed sort keys to real columns and a validated direction, and build the order-by from that, never from raw input.

Sorting is tightly linked to pagination (see Related Topics). Pagination only makes sense over a deterministic order: if two rows can tie on the sort field, the database may order them differently between requests, causing rows to shift across page boundaries. The fix is a stable tiebreaker, adding a unique field such as id as the final sort key, so the order is fully determined.

Examples

This TypeScript example maps a whitelisted sort key and direction into a Prisma order-by, with id as a stable tiebreaker:

const sortable = new Set(['createdAt', 'email']);
const field = sortable.has(String(req.query.sort)) ? String(req.query.sort) : 'createdAt';
const order = req.query.order === 'asc' ? 'asc' : 'desc';

const users = await prisma.user.findMany({
  orderBy: [{ [field]: order }, { id: 'asc' }],
});

Common mistakes

Sorting by whatever field name the client sends, without a whitelist.
Paginating over a non-unique sort field, so rows drift between pages.
Accepting arbitrary direction values instead of a validated asc or desc.
Forgetting a default sort, so results come back in an unpredictable order.

Best practices

Whitelist the fields a client may sort by and map them to real columns.
Validate the direction to exactly ascending or descending.
Always add a unique tiebreaker (such as id) for stable ordering.
Define a sensible default sort for every collection endpoint.