Pagination, Filtering & Sorting

A collection endpoint that returns every row in a table is fine on day one and a liability by day ninety. As data grows, unbounded list responses blow up payload sizes, exhaust memory, and time out under load. Pagination, filtering, and sorting are the three controls that keep list endpoints fast and useful: clients ask for a slice of the data, narrowed by criteria, in the order they want. This page implements both offset and cursor pagination, query-string filtering and sorting, and a consistent metadata envelope so clients always know where they are.

Offset (limit/offset) pagination

The simplest scheme exposes page and limit query parameters and translates them into SQL LIMIT/OFFSET (or Mongoose .skip()/.limit()). It is intuitive — clients can jump to any page — and trivial to implement. The key discipline is to validate and clamp the inputs so a client can never request a million rows or a negative offset.

const express = require("express");
const router = express.Router();

function parsePagination(query, { defaultLimit = 20, maxLimit = 100 } = {}) {
  const page = Math.max(1, parseInt(query.page, 10) || 1);
  const limit = Math.min(maxLimit, Math.max(1, parseInt(query.limit, 10) || defaultLimit));
  const offset = (page - 1) * limit;
  return { page, limit, offset };
}

router.get("/products", async (req, res, next) => {
  try {
    const { page, limit, offset } = parsePagination(req.query);

    const [rows, [{ total }]] = await Promise.all([
      db.query("SELECT * FROM products ORDER BY id LIMIT $1 OFFSET $2", [limit, offset]),
      db.query("SELECT COUNT(*)::int AS total FROM products"),
    ]);

    res.json({
      data: rows,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit),
        hasNext: offset + rows.length < total,
        hasPrev: page > 1,
      },
    });
  } catch (err) {
    next(err);
  }
});

module.exports = router;

A request like GET /products?page=2&limit=3 returns the second slice plus a metadata block describing the full set.

Output:

{
  "data": [
    { "id": 4, "name": "Keyboard", "price": 49.0 },
    { "id": 5, "name": "Mouse", "price": 25.0 },
    { "id": 6, "name": "Monitor", "price": 199.0 }
  ],
  "pagination": {
    "page": 2,
    "limit": 3,
    "total": 57,
    "totalPages": 19,
    "hasNext": true,
    "hasPrev": true
  }
}

Gotcha: offset pagination gets slower as the offset grows — the database still scans and discards every skipped row. It also drifts: if a row is inserted while a user pages, items can shift between pages and a record may be skipped or shown twice. For large or write-heavy datasets, prefer cursor pagination below.

Cursor (keyset) pagination

Cursor pagination replaces “skip N rows” with “give me rows after this stable marker.” The cursor is an opaque token encoding the last item’s sort key (often its id or a timestamp). Because the query uses a WHERE id > cursor predicate backed by an index, performance stays constant regardless of how deep the client scrolls, and concurrent inserts no longer cause drift. The trade-off is that clients can only move forward/backward sequentially — there are no random “jump to page 500” links.

// Encode/decode cursors so they're opaque to clients
const encodeCursor = (value) => Buffer.from(String(value)).toString("base64url");
const decodeCursor = (cursor) => Buffer.from(cursor, "base64url").toString("utf8");

router.get("/feed", async (req, res, next) => {
  try {
    const limit = Math.min(100, Math.max(1, parseInt(req.query.limit, 10) || 20));
    const after = req.query.cursor ? decodeCursor(req.query.cursor) : null;

    // Fetch limit + 1 to detect whether another page exists
    const rows = after
      ? await db.query(
          "SELECT * FROM posts WHERE id < $1 ORDER BY id DESC LIMIT $2",
          [after, limit + 1]
        )
      : await db.query("SELECT * FROM posts ORDER BY id DESC LIMIT $1", [limit + 1]);

    const hasNext = rows.length > limit;
    const data = hasNext ? rows.slice(0, limit) : rows;
    const nextCursor = hasNext ? encodeCursor(data[data.length - 1].id) : null;

    res.json({ data, pagination: { limit, nextCursor, hasNext } });
  } catch (err) {
    next(err);
  }
});

The client follows the chain by passing the returned nextCursor back: GET /feed?cursor=MTA0&limit=20.

Offset vs cursor at a glance

Aspect	Offset pagination	Cursor pagination
Random page access	Yes (?page=42)	No, sequential only
Performance at depth	Degrades (scans skipped rows)	Constant (indexed seek)
Stable under inserts	No, items drift	Yes, marker is fixed
Total count / page links	Easy	Hard / usually omitted
Best for	Admin tables, small sets	Feeds, infinite scroll, big data

Filtering with query parameters

Filtering narrows the result set from explicit, whitelisted query parameters. Never interpolate raw query values into SQL — build a parameterized WHERE clause from a known set of allowed fields. This both prevents injection and stops clients from filtering on columns you didn’t intend to expose.

function buildFilters(query) {
  const allowed = { category: "category", minPrice: "price >= ", maxPrice: "price <= " };
  const clauses = [];
  const params = [];

  if (query.category) {
    params.push(query.category);
    clauses.push(`category = $${params.length}`);
  }
  if (query.minPrice) {
    params.push(Number(query.minPrice));
    clauses.push(`price >= $${params.length}`);
  }
  if (query.maxPrice) {
    params.push(Number(query.maxPrice));
    clauses.push(`price <= $${params.length}`);
  }

  const where = clauses.length ? `WHERE ${clauses.join(" AND ")}` : "";
  return { where, params };
}

A call to GET /products?category=audio&minPrice=20 yields WHERE category = $1 AND price >= $2 with params ["audio", 20].

Sorting safely

Sorting works the same way: map a sort parameter onto a whitelist of allowed columns and directions. A common convention is ?sort=-price,name, where a leading - means descending.

function buildOrderBy(sort, allowed = ["id", "name", "price", "createdAt"]) {
  if (!sort) return "ORDER BY id ASC";
  const parts = sort.split(",").map((field) => {
    const desc = field.startsWith("-");
    const col = desc ? field.slice(1) : field;
    if (!allowed.includes(col)) return null; // ignore unknown columns
    return `${col} ${desc ? "DESC" : "ASC"}`;
  }).filter(Boolean);
  return parts.length ? `ORDER BY ${parts.join(", ")}` : "ORDER BY id ASC";
}

Tip: in Express 5, async route handlers that throw or reject are forwarded to your error middleware automatically, so you can drop the try/catch and next(err) boilerplate. The examples above use the explicit 4.x pattern, which runs unchanged on both versions.

Best practices

• Always clamp limit to a sane maximum (commonly 100) and apply a default; never let a client request unbounded rows.
• Whitelist every filterable and sortable field — reject or ignore anything outside the allowed set to avoid injection and accidental column exposure.
• Use parameterized queries; never concatenate query-string values into SQL.
• Return a consistent metadata envelope (page/limit/total for offset, nextCursor/hasNext for cursor) so clients can build navigation reliably.
• Prefer cursor pagination for feeds, infinite scroll, and large or write-heavy tables where offset drift and slow deep pages bite.
• Ensure every sort/cursor column is backed by a database index — pagination is only as fast as the underlying ordering.
• Keep cursors opaque (base64-encoded) so clients treat them as tokens rather than guessable offsets you may later change.

Related Topics

• REST API design
• RESTful routes & CRUD
• Response formatting
• API versioning
• GraphQL with Express