Limit and Offset Behavior in Knex.js: Unresolved Pagination API Decision

Direct answer

Knex.js should not add a magic .paginate() to core. Keep .limit() and .offset() as low-level primitives, and provide pagination as an opt-in helper or plugin that composes them. A built-in helper that always returns a total count would couple the query builder to expensive, racy COUNT(*) semantics that many applications do not need.

Confirmed facts vs. likely explanation

• Confirmed: Knex query builders expose .limit(n) and .offset(n) and emit dialect-appropriate SQL. There is no core method that returns both the page slice and a total record count.
• Likely explanation: Because LIMIT/OFFSET returns only the slice, totals require a separate COUNT(*) query or a window function. Under concurrent writes, the row set and the total can be inconsistent unless both run inside one transaction with a consistent snapshot.

Safe pagination with current Knex

1. Use .limit(n).offset(m) for bounded slices when deep paging is rare.
2. If exact totals are required, execute the page query and COUNT(*) in the same transaction so the numbers agree.
3. For large tables or infinite scroll, switch to keyset/cursor pagination on a stable, unique, indexed sort key:
   

   SELECT * FROM items
   WHERE (sort_key, id) < (:last_key, :last_id)
   ORDER BY sort_key DESC, id DESC
   LIMIT :n;

4. Detect hasMore by requesting n + 1 rows and discarding the extra, avoiding COUNT(*) entirely.
5. Verify generated SQL with .toSQL() and run EXPLAIN/ANALYZE on your target dialect before shipping.

If a helper is introduced

Use explicit parameters—limit/offset, page/perPage, or cursor/after—and never silently clamp or mutate inputs. A practical return shape is:

{
  data: [...],
  pagination: {
    limit, offset, page, perPage,
    total, totalPages, hasMore, nextCursor
  }
}

Compute total and totalPages only when requested. Validate non-negative integers, enforce a maximum limit, reject unsupported dialect combinations, and fail clearly when a cursor is requested without a usable sort key.

Open question that changes the design

The single missing diagnostic that alters the recommendation is whether your consumers need exact total record counts or only a hasMore signal. Admin UIs with page numbers may justify transactional COUNT(*); infinite-scroll consumers should omit it by default.