Paginate, filter, and sort

GET /books returns every book. With ten books that’s fine. With a hundred thousand it’s a multi-megabyte response that gets slower every day. A collection route needs a page size from day one. Adding it later is a breaking change.

Limit, offset, filter, order

The first version takes three query parameters. limit caps the page size, offset says how many rows to skip, author filters. The order is fixed: newest first, with the ID as a tie-breaker.

curl "http://localhost:3000/books?author=Le%20Guin&limit=20&offset=0"

Here is the SQL behind it:

SELECT * FROM books
WHERE author = ?
ORDER BY created_at DESC, id DESC
LIMIT ? OFFSET ?

The id in the ORDER BY matters more than it looks. Two books created in the same millisecond share a created_at, and without a tie-breaker the database may return them in a different order on every query. A row could then appear on two pages, or on none. Deterministic ordering means the same query always returns the same sequence.

Validate before you touch SQL

limit and offset arrive as strings, like everything in a query string. Parse them, reject nonsense, and apply defaults:

const limit = Math.min(Number(c.req.query('limit') ?? 20), 100)
const offset = Number(c.req.query('offset') ?? 0)
if (!Number.isInteger(limit) || !Number.isInteger(offset) || limit < 1 || offset < 0) {
  return problem(c, 400, 'Invalid pagination parameters')
}

Default to 20, cap at 100, reject limit=abc, limit=-5 and offset=1.5. The cap is the important line. Without it a client asks for limit=1000000 and you’re back to the unbounded query.

Filter values go through bound parameters. Sort fields, if clients may choose, go through the allowlist from the previous lesson.

Tell the client how to continue

A page on its own is a dead end. Return enough to ask for the next one:

{
  "books": [...],
  "limit": 20,
  "offset": 0,
  "next": "/books?author=Le%20Guin&limit=20&offset=20"
}

When the client reaches the end, return 200 with an empty books array and next set to null. Not a 404. The request was valid, there’s just nothing left.

The problem with offsets

Offset pagination is easy to understand, and that’s why we start with it. It has one flaw. If someone creates a book between page one and page two, every row shifts by one, and the client sees the last item of page one again at the top of page two. Deterministic ordering doesn’t prevent that, because the data itself moved.

For a busy collection the fix is a cursor: instead of “skip 20 rows”, the client says “give me rows after this created_at and id”. Inserts elsewhere don’t shift the page. It’s more work, and for the Books API offsets are enough. Whichever you pick, the next value is part of the contract.

Indexes come last. Add one on (author, created_at, id) when you’ve measured that the filter query is slow. Every index makes inserts slower and the file bigger.

Wire up validation and the parameterized query now, create 25 books, and request two pages of 20. Confirm the second page has five books and next is null.