Practical Offset-Based Pagination with Knex.js: When to Use It and How to Mitigate Its Pitfalls

Understanding the problem

When building a REST API that returns lists of records, clients often need to page through large result sets. A common approach is to ask for a slice of data using limit and offset values. In Knex.js this translates to chaining .limit() and .offset() on a query builder. While the API is simple, the underlying SQL can become inefficient as the offset grows, and concurrent changes can cause missing or duplicate rows.

Thesis: Use offset‑based pagination with Knex.js when the dataset is modest and simplicity outweighs performance concerns, but always pair it with deterministic ordering and a separate count query.

Building a reusable pagination helper

Encapsulating the pattern makes it easier to apply consistently across endpoints.

// paginationHelper.js
const buildPaginatedQuery = (baseBuilder, { limit, offset, orderByColumn = 'id', orderDir = 'asc' }) => {
  return baseBuilder
    .clone() // avoid mutating the original builder
    .orderBy(orderByColumn, orderDir)
    .limit(limit)
    .offset(offset);
};

const getTotalCount = async (knex, baseBuilder) => {
  const { count } = await baseBuilder.clone().count({ count: '*' }).first();
  return Number(count);
};

module.exports = { buildPaginatedQuery, getTotalCount };

The helper receives a base builder that already contains any where, join, or select clauses specific to the endpoint. It adds ordering, limit, and offset, then returns a new builder ready for execution.

Worked example: paginating active users

Suppose we have a users table and we want to return active users in pages of 10.

// routes/users.js
const express = require('express');
const knex = require('./knex'); // initialized elsewhere
const { buildPaginatedQuery, getTotalCount } = require('./paginationHelper');

const router = express.Router();

router.get('/', async (req, res) => {
  const page = Math.max(parseInt(req.query.page ?? '1', 10), 1);
  const limit = 10;
  const offset = (page - 1) * limit;

  // Base query: only active users, selecting needed columns
  const base = knex('users')
    .where('active', true)
    .select('id', 'email', 'created_at');

  try {
    const [rows, total] = await Promise.all([
      buildPaginatedQuery(base, { limit, offset }).then(q => q),
      getTotalCount(knex, base)
    ]);

    res.json({
      data: rows,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

module.exports = router;

To verify the generated SQL, you can inspect the query builder before execution:

const sql = buildPaginatedQuery(base, { limit, offset }).toSQL().toNative();
console.log(sql);
// Example output for PostgreSQL:
// select "id", "email", "created_at" from "users" where "active" = true order by "id" asc limit 10 offset 20

Running this code against a test database (e.g., an SQLite in‑memory instance) lets you confirm that the limit and offset clauses appear as expected. No claim is made that the snippet has been executed in production; the verification step is a suggestion for you to run in your own environment.

Trade‑offs and limitations

• Performance: As offset grows, the database must scan and discard preceding rows, which can become costly on large tables.
• Result drift: If rows are inserted or deleted between page requests, a user may see duplicate entries or skip items entirely.
• Non‑deterministic order: Omitting orderBy leads to arbitrary ordering, making pagination unreliable.

When any of these concerns are significant, consider keyset (seek) pagination using a unique, ordered column (e.g., WHERE id > :lastSeenId ORDER BY id LIMIT :limit). Knex.js supports this pattern just as easily by chaining where and orderBy without offset.

Actionable closing

If your API serves relatively small datasets or you prioritize rapid development, offset‑based pagination with Knex.js is a pragmatic choice. Implement the helper above, always include an explicit orderBy, and fetch a separate count for total pages. Monitor query latency in staging; if you notice slowdowns as offsets increase, prototype a keyset alternative and compare the execution plans. This approach gives you a clear path to evolve your pagination strategy as data volume grows.