Pagination, Filtering and Sorting

A list endpoint that returns every row works for ten records and collapses at ten thousand. Production APIs let clients ask for a page at a time, narrow results with filters, and choose the order. After this lesson you will be able to parse and clamp these query parameters safely, translate them into Mongoose or Prisma queries, return a consistent response envelope, and switch to cursor-based pagination when offsets stop scaling.

Designing the query contract

Agree on the parameter names once and reuse them across every collection:

| Parameter | Example | Meaning | | --- | --- | --- | | page, limit | ?page=2&limit=20 | Offset pagination (1-based page) | | sort | ?sort=-createdAt | Field name; leading - means descending | | <field> | ?status=open&priority=high | Exact-match filter on whitelisted fields | | q | ?q=milk | Free-text search on a chosen field | | after | ?after=665f... | Cursor pagination, alternative to page |

Every value arrives as a string (or undefined), so the first job is to parse, default and clamp.

Parsing safely

javascript

// src/utils/list-query.js
const SORTABLE = new Set(["createdAt", "title", "priority"]);
const FILTERABLE = new Set(["status", "priority"]);

export function parseListQuery(query) {
  const page = Math.max(1, Number(query.page) || 1);
  const limit = Math.min(100, Math.max(1, Number(query.limit) || 20));

  const raw = String(query.sort ?? "-createdAt");
  const field = raw.replace(/^-/, "");
  const sort = SORTABLE.has(field) ? { [field]: raw.startsWith("-") ? -1 : 1 } : { createdAt: -1 };

  const filter = {};
  for (const key of FILTERABLE) {
    if (typeof query[key] === "string") filter[key] = query[key];
  }
  if (typeof query.q === "string" && query.q.trim()) {
    const escaped = query.q.trim().replace(/[.*+?^${}()|[\]\\]/g, "\\$&");
    filter.title = { $regex: escaped, $options: "i" };
  }

  return { page, limit, sort, filter };
}

Three protections are built in: limit is capped at 100 so one request cannot dump the table, only whitelisted fields can be filtered or sorted (a client cannot sort by password or filter by owner), and regex metacharacters in q are escaped to prevent catastrophic patterns.

Querying and the response envelope

Run the page query and the count in parallel, then return the data together with meta so clients can render page controls:

javascript

// src/services/todo.service.js
export async function list(options, ownerId) {
  const { page, limit, sort, filter } = options;
  const where = { ...filter, owner: ownerId }; // server-side constraint always wins

  const [data, total] = await Promise.all([
    Todo.find(where).sort(sort).skip((page - 1) * limit).limit(limit).lean(),
    Todo.countDocuments(where),
  ]);

  return {
    data,
    meta: { page, limit, total, totalPages: Math.ceil(total / limit), hasNext: page * limit < total },
  };
}

Spreading the parsed filter before the owner constraint guarantees a client cannot override it. The Prisma version uses the same shape with different names:

javascript

const [data, total] = await Promise.all([
  prisma.todo.findMany({ where, orderBy: { createdAt: "desc" }, skip: (page - 1) * limit, take: limit }),
  prisma.todo.count({ where }),
]);

Cursor-based pagination

skip still scans the skipped rows, so page 500 of a large collection gets slow, and inserts between requests shift items across page boundaries. Cursor pagination fixes both by asking for "items after the last one I saw":

javascript

// GET /api/todos?after=<lastId>&limit=20   (sorted by _id ascending)
const where = { owner: ownerId };
if (req.query.after) where._id = { $gt: req.query.after };

const items = await Todo.find(where).sort({ _id: 1 }).limit(limit + 1).lean();
const hasNext = items.length > limit;
const data = hasNext ? items.slice(0, limit) : items;
res.json({ data, meta: { nextCursor: hasNext ? data.at(-1)._id : null } });

Fetching limit + 1 rows tells you whether another page exists without a count query. Prisma offers the same idea with cursor: { id } plus skip: 1. Cursors work best for feeds and infinite scroll; offset pages are easier when users need "jump to page 7".

Tips

Add an index that matches your default sort ({ owner: 1, createdAt: -1 }) or every page query becomes a collection scan.
Validate page and limit with the validation middleware from the previous lesson instead of repeating Number() checks in each controller.
Keep meta names identical across endpoints; frontends build one pagination component and reuse it everywhere.

Key Takeaways

Standardise page, limit, sort, whitelisted filter fields and q across every list endpoint.
Parse, default and clamp every parameter; whitelist sortable and filterable fields; escape regex input.
Run the page query and count in Promise.all and return { data, meta } with total, totalPages and hasNext.
Use cursor pagination (after + limit + 1) for large or fast-changing collections.
Index the fields you sort and filter on.

Next lesson: Error Handling in Express — build the error middleware that turns thrown errors into consistent HTTP responses.