Pagination, Filtering & Sorting

A GET /users that returns every row works fine with 50 records and falls over with 5 million. Production list endpoints must paginate — return a bounded page at a time — and usually let clients filter and sort too. All three live in the query string. This page covers the two pagination strategies (offset vs cursor), their trade-offs, and how to expose them consistently.

Offset pagination — page & limit

app.get('/users', async (req, res) => {
  // Parse + clamp — never trust client numbers:
  const page  = Math.max(1, Number(req.query.page) || 1)
  const limit = Math.min(100, Math.max(1, Number(req.query.limit) || 20))
  const offset = (page - 1) * limit

  const [rows, total] = await Promise.all([
    db.users.find({ offset, limit }),
    db.users.count(),
  ])

  res.json({
    data: rows,
    pagination: {
      page, limit, total,
      totalPages: Math.ceil(total / limit),
      hasNext: page * limit < total,
    },
  })
})

Always clamp `limit` — an unbounded page size is a DoS vector

If a client can request `?limit=10000000`, one request can exhaust memory and hammer the database — an easy denial-of-service. Always enforce a **maximum** (e.g. 100) and a sane default (e.g. 20), and coerce non-numeric input. Likewise floor `page` at 1. The clamps above (`Math.min`/`Math.max`) are not optional polish — they're a safety control.

GET /users?page=2&limit=20
{
  "data": [ ...20 users... ],
  "pagination": { "page": 2, "limit": 20, "total": 137, "totalPages": 7, "hasNext": true }
}

The offset problem at scale

`OFFSET` gets slow on deep pages and skips/repeats rows under writes

Offset pagination has two flaws. **Performance:** `OFFSET 1000000` makes the database scan and discard a million rows — deep pages get progressively slower. **Consistency:** if rows are inserted/deleted while a user pages through, the offset shifts, so they can *miss* or *see duplicate* items between pages. For large or fast-changing datasets, prefer cursor pagination.

Cursor pagination — scalable and stable

// Client passes the last item's sort key as a cursor; we fetch "after" it.
app.get('/events', async (req, res) => {
  const limit = Math.min(100, Number(req.query.limit) || 20)
  const cursor = req.query.cursor    // e.g. an id or timestamp, opaque to client

  // Fetch one extra to know if there's a next page:
  const rows = await db.events.find({
    where: cursor ? { id: { gt: Number(cursor) } } : {},
    orderBy: { id: 'asc' },
    limit: limit + 1,
  })

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

  res.json({ data, nextCursor })
})

Cursors page by a key, not an offset — O(1) deep pages

Instead of "skip N rows," a cursor says "give me rows *after* this key" (`WHERE id > cursor`), which the database serves instantly via an index regardless of depth. Inserts/deletes elsewhere don't shift the window, so no skips or duplicates. The trade-off: you can't jump to an arbitrary page number — only next/previous. Make the cursor opaque (base64-encode it) so clients don't depend on its format.

Offset vs cursor — choosing

	Offset	Cursor
Jump to page N	Yes	No (next/prev only)
Deep-page speed	Degrades	Constant
Stable under writes	No	Yes
Total count available	Easy	Expensive/omitted
Best for	Small data, admin tables, page numbers	Large/live feeds, infinite scroll

Filtering

// GET /products?category=books&minPrice=10&inStock=true
app.get('/products', async (req, res) => {
  const where = {}
  if (req.query.category) where.category = req.query.category
  if (req.query.minPrice) where.price = { gte: Number(req.query.minPrice) }
  if (req.query.inStock === 'true') where.stock = { gt: 0 }

  res.json({ data: await db.products.find({ where }) })
})

Whitelist filter fields — don't pass query params straight to the DB

Build the query from an **explicit allow-list** of fields, as above. Never spread `req.query` into a database filter or interpolate it into SQL — that invites injection and lets clients query columns you never meant to expose. Validate and coerce each accepted filter ([request validation](/nodejs/request-validation)), and ignore unknown params.

Sorting

// GET /products?sort=-price,name   (- = descending, comma = tie-breaker)
const SORTABLE = new Set(['price', 'name', 'createdAt'])

const orderBy = (req.query.sort || 'createdAt')
  .split(',')
  .map((token) => {
    const desc = token.startsWith('-')
    const field = desc ? token.slice(1) : token
    return { field, dir: desc ? 'desc' : 'asc' }
  })
  .filter((s) => SORTABLE.has(s.field))   // drop anything not whitelisted

A leading `-` for descending is the common convention

The widely-used pattern: `sort=field` ascending, `sort=-field` descending, comma-separated for multiple keys (`sort=-price,name`). Crucially, validate each field against a **whitelist** of sortable columns — sorting by an arbitrary client-supplied column name is both an information leak and a performance trap (sorting an unindexed column scans the table).

Putting it together

Default and cap limit; floor page/clamp all numbers.
Return a consistent envelope: data + pagination (or nextCursor).
Offset for page-numbered admin views; cursor for large/live feeds.
Whitelist filterable and sortable fields — never trust raw params.
Index the columns you filter and sort on, or pagination crawls.

Evolve your API without breaking existing clients: [API Versioning](/nodejs/api-versioning).