Pagination

Returning all rows from /users is a recipe for an OOM crash. Page the results.

Offset / Limit — Simple, Brittle

const ListQuery = z.object({
  page:  z.coerce.number().int().min(1).default(1),
  limit: z.coerce.number().int().min(1).max(100).default(20),
});

export async function list(req, res) {
  const { page, limit } = req.validQuery;
  const total = await db.users.count();
  const rows = await db.users.findMany({
    skip: (page - 1) * limit,
    take: limit,
  });
  res.json({
    data: rows,
    pagination: {
      page, limit, total,
      pages: Math.ceil(total / limit),
    },
  });
}

Issues

• Skipping N rows is slow on big tables — the DB still scans them
• Insertions/deletions cause duplicate or missing items between page hits
• COUNT(*) is expensive for large tables

Fine for admin panels. Bad for big public APIs.

Cursor — Fast, Stable

A cursor is a pointer to the last item on the page. Next page starts after it.

const ListQuery = z.object({
  cursor: z.string().optional(),
  limit:  z.coerce.number().int().min(1).max(100).default(20),
});

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

  const rows = await db.users.findMany({
    take: limit + 1,             // fetch one extra to detect a next page
    ...(cursor ? { skip: 1, cursor: { id: cursor } } : {}),
    orderBy: { id: "asc" },
  });

  const hasMore = rows.length > limit;
  const items = rows.slice(0, limit);
  const nextCursor = hasMore ? items.at(-1).id : null;

  res.json({
    data: items,
    pagination: { nextCursor, limit },
  });
}

URL flow:

• GET /users?limit=20 → 20 items + nextCursor
• GET /users?cursor=<last>&limit=20 → next 20

Why It’s Better

• The DB uses an index — no scanning skipped rows
• New inserts at the top don’t shift the pages you’ve already seen
• No COUNT(*)

Trade-off

You can’t say “give me page 17 directly.” Cursors are sequential — designed for “load more” or infinite-scroll UIs, not jump-to-page navigation.

Hybrid

Some APIs offer both:

• Cursor for public endpoints (/feed)
• Offset for admin pages where jumping by page matters

Returning Total

If clients need a total count, return it once (e.g. on the first page) or via a separate /users/count endpoint. Don’t recompute on every paginated request.

Limits

limit: z.coerce.number().int().min(1).max(100).default(20),

Always cap the limit. Without a max, ?limit=999999 becomes a DoS vector.