Skip to content
LEARNREACTSAAS
Chapter 46Lesson 4

List endpoints: filter, sort, search, paginate

Author the Zod query schema and shared Drizzle query builder behind a list route handler that filters, sorts, searches, and paginates safely.

A GET /api/invoices handler might be asked to serve a URL like this:

/api/invoices?status=sent&sort=-issuedAt&q=acme&cursor=eyJ…&limit=20

Every parameter after the ? arrives as a string: limit=20 is "20", not the number 20, and ?limit=999999 is a perfectly well-formed question. Your handler decides which questions it will answer.

A list endpoint’s query schema is its entire input surface. Get it wrong and a client can bring your database down or read a column you never meant to expose.

Whatever the domain, a list endpoint answers at most four questions, all asked through the query string:

  • Filter: which rows? status=sent
  • Sort: in what order? sort=-issuedAt
  • Search: matching what text? q=acme
  • Paginate: how many, and starting where? cursor=…&limit=20

Each question adds a few fields to one growing schema and a few lines to one growing handler.

/api/invoices resource path
?status=sent Filter
&sort=-issuedAt Sort
&q=acme Search
&cursor=…&limit=20 Paginate
Every list query is some combination of four dimensions: filter, sort, search, and paginate.

Filtering: enum allowlists, not free strings

Section titled “Filtering: enum allowlists, not free strings”

Filtering narrows which rows come back. The instinct is to drop whatever the client sends into a WHERE clause; resist it. A filter parameter is a promise about which columns and values the caller may ask about, and that promise lives in the schema.

Declare each filterable column explicitly with its allowed values as an enum. A single-value filter is z.enum([...]).optional(); one the caller can repeat, asking for sent and overdue, wraps that enum in z.array. Invoices want the second:

lib/schemas/invoice.ts
import { z } from 'zod';


export const listInvoicesQuerySchema = z.object({
  status: z
    .array(z.enum(['draft', 'sent', 'overdue', 'paid']))
    .optional(),
});

The enum is the allowlist: ?status=banana fails the schema and comes back 422, the validation-failure status from the lesson on status codes, because it isn’t one of the four declared values. The schema also caps which columns exist; there is no ?customerId=… filter unless you add a customerId field.

A caller sends more than one status in one of two conventions: repeated-key, ?status=sent&status=overdue, or comma-separated, ?status=sent,overdue. This project picks repeated-key: URLSearchParams has a method built to read it, and it sidesteps values that legitimately contain a comma.

The catch is that the obvious Object.fromEntries(searchParams) keeps only the last value of a repeated key, so ?status=sent&status=overdue becomes { status: 'overdue' } and sent vanishes. Read multi-valued keys with getAll instead:

export function parseListQuery(searchParams: URLSearchParams) {
  const raw = {
    ...Object.fromEntries(searchParams),
    status: searchParams.getAll('status'),
  };
  return listInvoicesQuerySchema.safeParse(raw);
}

The input is a URLSearchParams, the object request.nextUrl.searchParams hands you, every value a string.

export function parseListQuery(searchParams: URLSearchParams) {
  const raw = {
    ...Object.fromEntries(searchParams),
    status: searchParams.getAll('status'),
  };
  return listInvoicesQuerySchema.safeParse(raw);
}

Object.fromEntries collapses the params into a plain object, the right move for every single-valued key (sort, q, limit, cursor).

export function parseListQuery(searchParams: URLSearchParams) {
  const raw = {
    ...Object.fromEntries(searchParams),
    status: searchParams.getAll('status'),
  };
  return listInvoicesQuerySchema.safeParse(raw);
}

But Object.fromEntries keeps only the last value of a repeated key. getAll returns the full array; let it overwrite the single-valued version. This line is the difference between honoring ?status=sent&status=overdue and silently dropping half of it.

export function parseListQuery(searchParams: URLSearchParams) {
  const raw = {
    ...Object.fromEntries(searchParams),
    status: searchParams.getAll('status'),
  };
  return listInvoicesQuerySchema.safeParse(raw);
}

Hand the assembled object to the schema with safeParse, never parse, because the query string is untrusted wire input, the posture from the wire-contracts lesson.

1 / 1

The URL is the user’s surface, so the handler parses defensively. Three cases come up constantly, and all three belong in the schema so the rest of your handler never thinks about them:

  • An empty value, ?status=, means “no status specified,” so drop empty strings before the enum check.
  • A duplicate, ?status=sent&status=sent, shouldn’t widen the query, so dedup the array.
  • An array left empty after stripping should collapse to undefined (no filter), not the empty set, which matches zero rows.

Now build that schema yourself, in pure Zod, watching the fixtures flip in real time:

Make every fixture pass. status is an optional array of the four invoice statuses — banana must be rejected, but an empty-string entry means 'not specified' and must normalize away, not fail the enum check. The starter already has the enum/array shape, so the last empty-string fixture is red. The order is the lesson: strip the empty strings *before* the enum runs — that's the job z.preprocess does. Watch the ^? query: even with the preprocessing in front, the parsed type still reads status?: ('draft' | 'sent' | 'overdue' | 'paid')[].

Booting type-checker…
Test scenario Value
single status {"status":["sent"]}
multi status {"status":["sent","overdue"]}
out-of-enum value {"status":["banana"]}
empty array (what getAll returns) collapses to undefined {"status":[]}
empty string normalized away {"status":[""]}
omitted is fine {}

Sorting: the prefix convention and the column allowlist

Section titled “Sorting: the prefix convention and the column allowlist”

Sorting decides the order rows come back, and the whole dimension fits in one query parameter. Three conventions compete for what goes in it:

  • Prefix form: ?sort=-issuedAt, where a leading - means descending. Shortest, reads straight from the URL. This project’s pick.
  • Field-plus-order pair: ?sortBy=issuedAt&sortOrder=desc. Verbose, but right when each dimension wants its own typed enum.
  • Comma-separated multi-sort: ?sort=-issuedAt,total. Rare in lists; reach for it only when multi-column sort earns its weight.

Parse the string inside the schema with a .transform, so by the time the handler reads parsed.sort it already has the shape { field, direction } and no string-wrangling leaks into the handler body.

The field is an enum, not a free string. Splice a raw ?sort= value into ORDER BY and you get the failure the red version shows:

const column = parsed.sort?.field ?? 'issuedAt';
orderBy: sql.raw(`${column} ${direction}`),

Accepts any column, which is an information leak and a guaranteed table scan. column is whatever string the client sent, spliced straight into the SQL. A caller sorts by passwordHash and learns it exists; a caller sorts by an unindexed column and every page scans the table.
lib/schemas/invoice.ts
const SORTABLE_COLUMNS = {
  issuedAt: invoices.issuedAt,
  total: invoices.total,
  status: invoices.status,
} as const;


export const invoiceSortSchema = z
  .enum(['issuedAt', '-issuedAt', 'total', '-total', 'status', '-status'])
  .default('-issuedAt')
  .transform((value) => ({
    field: value.replace('-', '') as keyof typeof SORTABLE_COLUMNS,
    direction: value.startsWith('-') ? 'desc' : 'asc',
  }));

We pull this out as its own named schema because the full query schema composes it in and the in-app list view reuses it: one schema per intent.

One rule belongs with the database chapters: every sortable column should be backed by a composite index that leads with the tenant column, or every page of the list becomes a full table scan. You’ll build that index later; the rule to hold now is that your sort allowlist and your set of indexed columns should be the same set.

Searching: one bounded string, the data layer picks the operator

Section titled “Searching: one bounded string, the data layer picks the operator”

A caller types acme and expects rows that mention Acme. Hide how that match happens from the schema: to it, q is just a bounded string.

lib/schemas/invoice.ts
export const listInvoicesQuerySchema = z.object({
  status: z.array(z.enum(['draft', 'sent', 'overdue', 'paid'])).optional(),
  sort: invoiceSortSchema,
  q: z.string().min(1).max(100).optional(),
});


// In the read helper, not the handler — the operator is the data layer's call.
const searchPredicate = parsed.q
  ? ilike(invoices.customerName, `%${parsed.q}%`)
  : undefined;

Both bounds earn their place. min(1) normalizes an empty ?q= to “no search” rather than a filter matching nothing, the same way an empty filter does. max(100) caps untrusted free text so a caller can’t paste a megabyte into your WHERE clause.

Turning that string into SQL is the data layer’s decision, across three tiers. The SQL itself lands in the database chapters:

A pattern match with a wildcard on both sides, backed by a trigram index . Good for one column on a few-thousand-row table. The trigram index needs a search term a few characters long to engage, so single-letter search won’t use it.

WHERE customer_name ILIKE '%' || $q || '%'
The default for 'search by name' on a modest table.

Across all three tiers the schema is unchanged: q is a string, and swapping one operator for another touches only the data layer.

Paginating: the envelope and the opaque cursor

Section titled “Paginating: the envelope and the opaque cursor”

Pagination has two halves: the shape you return, and the cursor the client carries between pages.

The envelope

Section titled “The envelope”

The tempting move is to return a bare array, [{...}, {...}], and be done. Don’t. Return a top-level object:

{ data: Invoice[], pageInfo: { nextCursor: string | null, hasMore: boolean } }

A bare array has nowhere to put a total count, the filters you applied, or each row’s search rank. Adding any of those later means switching the response from array to object, which breaks every client already parsing the array. Wrap from day one and those fields drop into pageInfo (or beside data) for free.

One consequence trips people up: a list with no matching rows is 200, not 404. The body is { data: [], pageInfo: { nextCursor: null, hasMore: false } }. The list exists and happens to be empty; 404 would claim there is no such list. This is the status discipline from the previous lesson: 404 is for resources that don’t exist, not queries that found nothing.

The cursor at the wire

Section titled “The cursor at the wire”

The cursor is how the client asks for “the next page after the one I just saw,” and its contract is one word: opaque. The client hands back the string it got from you without ever reading inside it or building one by hand.

You make it opaque by base64url-encoding a small piece of JSON, { sortKey, id }, on the way out and decoding it on the way in. The wrapper isn’t encryption; it signals opacity. A readable ?cursor={"id":"…","issuedAt":"…"} invites clients to construct their own, and the moment they do, the inner shape becomes part of your public contract and you can never change it.

You already built the hard part elsewhere. Keyset pagination , with its (sortKey, id) predicate, the mandatory tiebreaker on id, and the WHERE clause that fetches “everything after this key,” came from the chapter on querying and mutating. This lesson’s job is the encoding and validation at the API edge: decode the base64, parse the inner JSON with a schema (untrusted wire input, so safeParse, never parse), and hand the validated { sortKey, id } to the query helper that owns the predicate.

The cursor is small, but it’s a real input boundary, so it gets a schema of its own:

lib/schemas/invoice.ts
export const cursorSchema = z.object({
  sortKey: z.union([z.string(), z.number()]),
  id: z.uuid(),
});


export const listInvoicesQuerySchema = z.object({
  status: z.array(z.enum(['draft', 'sent', 'overdue', 'paid'])).optional(),
  sort: invoiceSortSchema,
  q: z.string().min(1).max(100).optional(),
  cursor: z.string().optional(),
  limit: z.coerce.number().int().positive().max(100).default(20),
});


export type ListInvoicesQuery = z.infer<typeof listInvoicesQuerySchema>;

The limit line carries one rule: the schema is the ceiling. .default(20) sets the page size when the caller omits it, and .max(100) is the contract’s hard cap, not a nicety. Without it, the first careless ?limit=999999 asks your handler to load a million rows into memory and your database obliges. Every list endpoint needs a ceiling, and this is where it lives.

hasMore and nextCursor both come from the fetch-n+1 trick. You ask the database for one more row than the page size, limit + 1. Get limit + 1 back and there’s a next page: slice off the extra row, set hasMore: true, and encode nextCursor from the last row you’re actually returning. Get limit or fewer and you’re on the last page: hasMore: false, nextCursor: null. One round trip tells you both whether more exists and where the next page starts.

Client GET /api/invoices?limit=20 Handler

No cursor param on the first page.

Page 1: the client GETs the list with no cursor — there's nothing to carry yet.
Handler SELECT … LIMIT 21 Database

Fetch limit + 1 — the 21st row is the probe.

21 rows
+1
The handler asks the database for limit + 1 = 21 rows — one extra, to find out if a next page exists.
Handler 200 response Client

Probe dropped; nextCursor encodes row 20, not the dropped row.

20 returned
20 +1 
{ "data": [ /* 20 invoices */ ],
  "pageInfo": {
    "nextCursor": "eyJ…",  // from row 20
    "hasMore": true
  } }
21 came back, so there's a next page: drop the probe, return 20, and build nextCursor from row 20 — the last row you actually return.
Client GET /api/invoices?cursor=eyJ…&limit=20 Handler

The cursor is the verbatim string from step 3 — opaque to the client.

step 3 → "eyJ…" cursor=eyJ… → step 4
Page 2: the client hands back the exact cursor string it was given — it never looks inside.
Handler 200 response Client

Only 12 rows — fewer than 21, so this is the end.

12 rows 
{ "data": [ /* 12 invoices */ ],
  "pageInfo": {
    "nextCursor": null,
    "hasMore": false
  } }
Last page: fewer than 21 rows come back, so there's no probe to drop — nextCursor is null and hasMore is false. The client stops.

One last decision: cursor or offset? Cursor is the default, because it’s stable across concurrent writes: when invoices are created while a client pages through, cursor pagination won’t double-count or skip a row at the page seam, the way OFFSET does. Reserve offset for small admin tables that want “page 3 of 7” affordances over data that isn’t churning. Cursor by default, offset on purpose.

These pagination calls are judgment, not syntax, the kind of thing a schema can’t check for you. Test yourself:

You’re reviewing a teammate’s new GET /api/invoices list endpoint before it merges. The PR description lists the design decisions below. Which ones would you approve as written? Select all that apply.

A filter that excludes every invoice still responds 200, with data set to an empty array — the route never returns 404 just because the result set is empty.
The handler returns the rows as a top-level JSON array; a comment in route.ts explains the shape so future fields can be bolted on when needed.
The cursor field is an encoded blob with no documented internals, specifically so clients can’t assemble their own page requests against its structure.
When a request matches no rows, the handler replies 404 so the frontend has a clear signal to render its “no invoices yet” empty state.
The schema rejects ?limit=5000 even though the only screen that hits this endpoint requests 20 rows at a time and nothing in the product asks for more.
Page seams are kept consistent under heavy writes by paging with OFFSET, since a remembered-position cursor can point at a row that gets deleted between requests.

One query builder, two callers

Section titled “One query builder, two callers”

The last four sections produced every piece a handler needs to turn the parsed query into a Drizzle query: a where from the filters, an orderBy from the sort, a search predicate, a limit + 1, and a cursor predicate. Assemble them in a pure function, not the route handler.

buildInvoiceListQuery has one job: take the parsed query and return its description, the where, orderBy, and limit. No Request, no Response, no database call.

db/queries/invoices.ts
export function buildInvoiceListQuery(
  parsed: ListInvoicesQuery,
  orgId: string,
) {
  const where = and(
    eq(invoices.orgId, orgId),
    parsed.status ? inArray(invoices.status, parsed.status) : undefined,
    parsed.q ? ilike(invoices.customerName, `%${parsed.q}%`) : undefined,
    cursorPredicate(parsed.cursor, parsed.sort),
  );


  const column = SORTABLE_COLUMNS[parsed.sort.field];
  const orderBy = parsed.sort.direction === 'desc' ? desc(column) : asc(column);


  return { where, orderBy, limit: parsed.limit + 1 };
}

Two lines lean on machinery from other chapters. cursorPredicate is the decode-and-keyset-WHERE helper from the chapter on querying and mutating. The highlighted eq(invoices.orgId, orgId) is the tenant scope; in the real codebase it rides on the tenantDb(orgId) factory from the orgs chapter, which enforces scoping structurally rather than per query.

Because the function is pure, the same parsed and orgId always describe the same query, so two callers can share it from both sides of the wire boundary:

app/api/invoices/route.ts
export async function GET(request: NextRequest) {
  const parsed = parseListQuery(request.nextUrl.searchParams);
  if (!parsed.success) return problem(422, parsed.error);


  const { orgId } = await requireOrgUser();
  const { where, orderBy, limit } = buildInvoiceListQuery(parsed.data, orgId);
  const rows = await db.query.invoices.findMany({ where, orderBy, limit });


  return Response.json(toEnvelope(rows, parsed.data.limit));
}

For external callers. Parses searchParams, builds the query, executes it, and returns the paginated envelope. The wire format is JSON because the caller is over HTTP.

Line 6 is identical in both tabs; everything around it differs, one parses searchParams from a NextRequest and returns JSON, the other awaits the searchParams prop and returns JSX. The wire format is the variable; the query and business logic are the constant.

That line also marks the lesson’s scope. The route handler serves external callers: a mobile app, a partner backend, a BFF , or the rare in-app endpoint a Client Component must fetch. The in-app list, where a Server Component reads the URL directly, is a later chapter built on nuqs; the shared builder is the seam between the two.

  • Directorysrc/
    • Directorydb/
      • Directoryqueries/
        • invoices.ts the shared builder and read helpers: buildInvoiceListQuery, listInvoices
    • Directorylib/
      • Directoryschemas/
        • invoice.ts listInvoicesQuerySchema, cursorSchema
    • Directoryapp/
      • Directoryapi/
        • Directoryinvoices/
          • route.ts the handler: parses, calls the builder, returns the envelope
      • Directoryinvoices/
        • page.tsx (a later chapter), the in-app list, calls the same builder
The query schema and the builder live in shared modules; the route handler and the future in-app page are thin callers of the same code.

Name-once knobs that protect the database

Section titled “Name-once knobs that protect the database”

Field selection (?include=…, ?fields=…) defaults off. Expanding related resources or projecting a subset of columns adds contract complexity, so reach for it only when the API is published externally.

count defaults to omit. A “showing 1–20 of 1,247” total means a second SELECT COUNT(*) that scans the whole table, painful once the table is large. Leave total out of the envelope and let admin views opt in with ?withCount=1; the { data, pageInfo } shape already has room for it, so adding it is no breaking change.

The allowlist and the index set are the same set. Every column a client may filter or sort by needs a composite index leading with the tenant column, (orgId, sortColumn, id), so design the query schema and the indexes together.

External resources

Section titled “External resources”

The query-parsing behavior this lesson leans on, a proven envelope to model yours on, and the authoritative cases behind the pagination and search choices:

URLSearchParams — getAll()
developer.mozilla.org

The exact getAll-vs-single-value behavior the multi-value filter relies on.
Stripe API — pagination
docs.stripe.com

A widely-copied real-world cursor envelope: has_more plus opaque cursors.
Use The Index, Luke! — No Offset
use-the-index-luke.com

Why keyset pagination beats OFFSET — the deeper case behind this lesson's cursor-by-default rule.
PostgreSQL — pg_trgm
postgresql.org

The trigram index that makes the ILIKE search tier fast, straight from the Postgres docs.