Filtering & Sorting

Allow filters, search, and sort via query strings. Keep them explicit.

3 min read Level 2/5 #express#filtering#sorting

What you'll learn

Define an allowed-filter list
Validate sort fields
Avoid SQL injection

Real lists need filtering and sorting. Resist the temptation of “forward all query strings to the DB” — that’s the path to SQL injection and runaway queries.

Define Allowed Filters

import { z } from "zod";

const ListQuery = z.object({
  q:      z.string().optional(),                       // text search
  role:   z.enum(["admin", "user", "guest"]).optional(),
  status: z.enum(["active", "banned"]).optional(),
  cursor: z.string().optional(),
  limit:  z.coerce.number().int().min(1).max(100).default(20),
  sort:   z.enum(["name", "-name", "createdAt", "-createdAt"]).default("-createdAt"),
});

Each filter has an explicit schema. Anything not in the schema is ignored — attackers can’t sneak in password or admin filters.

Apply Them

export async function list(req, res) {
  const { q, role, status, sort, cursor, limit } = req.validQuery;

  const where = {};
  if (q)      where.name   = { contains: q };
  if (role)   where.role   = role;
  if (status) where.status = status;

  const orderBy = parseSort(sort);

  const rows = await db.users.findMany({
    where,
    orderBy,
    take: limit + 1,
    ...(cursor ? { skip: 1, cursor: { id: cursor } } : {}),
  });

  res.json({ data: rows.slice(0, limit) });
}

function parseSort(sort) {
  const dir = sort.startsWith("-") ? "desc" : "asc";
  const field = sort.replace(/^-/, "");
  return { [field]: dir };
}

The - prefix for descending is a common convention (?sort=-createdAt).

Multi-Sort

const ListQuery = z.object({
  sort: z.string().regex(/^-?\w+(,-?\w+)*$/).default("-createdAt"),
});

// ?sort=role,-createdAt
function parseSorts(sortStr) {
  return sortStr.split(",").map((s) => {
    const dir = s.startsWith("-") ? "desc" : "asc";
    return { [s.replace(/^-/, "")]: dir };
  });
}

Don’t Trust Field Names

Even with strict schemas, never pass raw user input to the DB query field. The schema is the whitelist — only fields in the enum make it through.

// BAD — any field name reaches the DB
const orderBy = { [req.query.sortField]: "asc" };

// GOOD — schema constrains the value to an enum
const orderBy = parseSort(req.validQuery.sort);

Search

For text search, the right tool depends on scale:

A few thousand rows: WHERE name LIKE '%...%' is fine
More rows: full-text indexes (Postgres tsvector, MySQL FULLTEXT)
Many millions of rows: a search engine (Meilisearch, Typesense, Algolia, ElasticSearch)

Don’t reach for a search engine until you need it. But know it exists.

Content Negotiation →