How to Fix Broken API Pagination

The bug: every page returns the same rows

Offset pagination slices results with SQL LIMIT (page size) and OFFSET (how many to skip). The classic bug is computing the offset as page * limit:

// BUG: page 1 -> OFFSET 10 (skips the first page!), and the math is off for every page
const offset = page * limit;

With page starting at 1, page * limit skips a whole page on page 1 and is wrong everywhere. If page is treated as 0-based elsewhere, you get the same rows repeated.

The fix: (page - 1) * limit

Pages are 1-based; the first page should skip nothing:

const limit = 10;
const offset = (page - 1) * limit;   // page 1 -> 0, page 2 -> 10, page 3 -> 20
const rows = await db.query(
  "SELECT * FROM products ORDER BY id LIMIT $1 OFFSET $2",
  [limit, offset]
);

Two things that matter: - Always ORDER BY a stable column - without it, OFFSET can return overlapping or missing rows because row order isn't guaranteed. - Return total count (SELECT count(*)) so clients can render page numbers.

Validate the edges

• page=1 -> first N rows (offset 0).
• page=2 -> the next N rows (no overlap with page 1).
• Past the last page -> empty list, not an error.

When to use cursor pagination instead

Offset pagination gets slow on big tables (the database still scans + discards all the skipped rows) and can skip/duplicate rows if data changes between pages. For large or fast-changing datasets, use a cursor: pass the last seen id and query WHERE id > $cursor ORDER BY id LIMIT $n. It's O(1) per page and stable under inserts.