AdonisJS Pagination: When Offset `paginate()` Is Enough and When to Build a Cursor

The dashboard that stalls on page 200

You ship an admin screen listing orders. With a few hundred rows it feels instant. Six months later the table holds hundreds of thousands of records, page 200 takes seconds to return, and support tickets mention duplicate or missing rows after a refresh. The bottleneck is not AdonisJS itself; it is the pagination strategy underneath the Lucid query builder.

The practical takeaway: use Lucid’s built-in paginate() when result sets are bounded and users need to jump to arbitrary pages. Move to a manual cursor (keyset) strategy when tables grow large or writes are frequent. The ORM gives you the first path almost for free; the second requires deliberate engineering.

Offset pagination is the fast path for bounded lists

Lucid is AdonisJS’s Active Record ORM, layered on top of Knex. Active Record means each model class wraps a database table and exposes a fluent query builder. For back-office tools, internal reports, or any list where the total row count is modest, the query builder’s paginate(page, perPage) method is usually the right default. It returns a serialized envelope containing both the rows and metadata such as total counts and page numbers.

Because the builder is chainable, you can compose filters and relationships before paginating:

// Run inside an AdonisJS controller
const page = Number(ctx.request.input('page')) || 1
const perPage = 20

const orders = await Order.query()
  .where('status', 'pending')
  .preload('customer')
  .paginate(page, perPage)

return orders.serialize()

This assumes Order is a Lucid model and ctx is the HTTP context. The serialized shape of the result can vary across AdonisJS major versions, so inspect the actual object in your project rather than hard-coding metadata keys in client code.

Why deep offsets stall and drift

Offset pagination asks the database to generate every skipped row and then throw it away. On a large table, requesting page 5000 with 20 rows per page means the database effectively scans and discards roughly 100,000 rows before returning the final 20. Latency spikes at deep page numbers are a database-level effect, not an ORM bug.

Concurrent writes make the problem worse. If a row is inserted or deleted between two page requests, the offset window shifts. A record that appeared on page 2 can move to page 1, causing duplicates or gaps in the user’s view. For audit logs, activity feeds, or any high-write table, this instability quickly becomes a correctness issue rather than just a performance one.

Building cursor pagination by hand

Lucid does not ship a first-class cursor paginator. You implement keyset pagination manually by adding a where clause on a unique, monotonic column—typically the primary key or a timestamp with a tie-breaker—and encoding the last seen value into an opaque cursor. The database seeks directly to the indexed key instead of scanning discarded rows.

// Assume `cursor` is decoded from the previous page's last row
const lastId = decodeCursor(ctx.request.input('cursor'))

const query = Order.query()
  .where('status', 'pending')

if (lastId) {
  query.where('id', '>', lastId)
}

const orders = await query
  .orderBy('id', 'asc')
  .limit(perPage + 1) // fetch one extra to detect a next page

const rows = orders.slice(0, perPage)
const nextCursor = rows.length === perPage
  ? encodeCursor(rows[rows.length - 1].id)
  : null

The trade-off is complexity. You must manage cursor encoding and decoding, handle ties on non-unique columns, and accept that users cannot jump to arbitrary page numbers. For infinite-scroll feeds or API consumers that only move forward, that restriction is usually acceptable.

Whitelisting columns and enforcing tenant boundaries

When sort or filter parameters arrive from a URL, raw column names passed to orderBy or select can become SQL injection vectors. Whitelist the allowed columns before handing them to the query builder:

const allowedSorts = ['created_at', 'total_amount', 'id']
const sort = allowedSorts.includes(ctx.request.input('sort'))
  ? ctx.request.input('sort')
  : 'created_at'

const orders = await Order.query().orderBy(sort, 'desc').paginate(page, perPage)

For multi-tenant applications, global scopes or the @beforePaginate hook can inject tenant filters automatically. This reduces the risk of leaking records across tenants when developers forget to add a where clause on every endpoint.

Limitations and how to verify in your project

• Deep offset pages cause latency spikes because databases do not optimize skipping large numbers of rows.
• Offset pagination can silently skip or duplicate records when rows are inserted or deleted between page loads.
• Cursor pagination demands a strictly ordered, unique key; ties or non-monotonic values break the cursor contract.
• The serialized shape of paginate results may vary across AdonisJS major versions, so client code should not hard-code the metadata structure.

Because pagination behavior and serialization are version-sensitive, verify against your installed release. Run node ace list in your project root to confirm available commands, then inspect node_modules/@adonisjs/lucid to see the exact pagination API present in your dependency tree. Consult the official AdonisJS documentation for your installed version.

To validate behavior under load, create a local test with a seeded table and compare paginate() output against a manual keyset query while inserting or deleting rows between requests. That experiment will show you exactly where offset drift appears in your schema.

Start simple, then measure

Begin with paginate() for admin screens and bounded lists. Instrument slow queries. When a table crosses the threshold where deep pages stall or duplicate rows appear, extract a cursor path for that specific endpoint. Keep the offset path where jumping to page 12 is a feature, not a bug. Pagination is a trade-off between simplicity and stability, and AdonisJS gives you the tools to choose deliberately.