AdonisJS Pagination: Lucid paginate() vs. Manual Keyset Queries

When building list endpoints in AdonisJS, your pagination strategy affects both database load and frontend complexity. Lucid's paginate() method is convenient, but using it blindly becomes a bottleneck as tables grow or users request deep page numbers. The practical takeaway: use paginate() for typical page-number UIs, and switch to manual keyset (cursor) pagination when offset cost or COUNT queries dominate.

Choosing Your Strategy

The decision hinges on two constraints: whether your UI needs a total count (\"Page 3 of 42\"), and how deep users will page into the dataset.

Feature	Lucid paginate()	Manual Keyset (Cursor)
Best for	Admin panels, page-number UIs, moderate datasets	Infinite scroll, high-churn or very large tables
SQL issued	Two queries: SELECT + COUNT(*)	One query: SELECT with WHERE clause
Deep-page performance	Degrades (OFFSET scans skipped rows)	Constant time with an indexed key
Metadata	total, perPage, currentPage, lastPage	None; you track the cursor yourself
Code required	Minimal	More (cursor encoding, direction handling)

When to Use Lucid paginate()

The paginate(page, perPage) method on a model query handles offset math and returns a paginator object containing the rows plus metadata. This maps directly onto page-number UI components and JSON API responses, so it is the right default for most applications.

Be aware of two costs. First, the COUNT(*) query can be as expensive as the data query itself when you apply heavy filters or joins. Second, SQL OFFSET (which paginate uses internally) forces the database to scan all preceding rows before reaching the requested page, so response time grows with page number. Neither approach composes any differently with Lucid features: where clauses, preload for eager loading relationships, and orderBy work with both.

Implementation: paginate() with Input Clamping

Lucid does not stop a client from requesting perPage=100000. Always validate and clamp pagination inputs in the controller. The following example targets AdonisJS v6 conventions (ESM imports, #models aliases); adjust import paths for your project layout.

import User from '#models/user'
import type { HttpContext } from '@adonisjs/core/http'

export default class UsersController {
  async index({ request }: HttpContext) {
    const page = Number(request.input('page', 1))
    const requested = Number(request.input('perPage', 10))

    // Clamp to prevent resource exhaustion
    const perPage = Math.min(Math.max(requested, 1), 50)
    const safePage = Math.max(page, 1)

    const users = await User.query()
      .orderBy('createdAt', 'desc')
      .paginate(safePage, perPage)

    // Serializes to JSON with rows plus metadata
    // (total, perPage, currentPage, lastPage)
    return users
  }
}

For stricter input handling, use a VineJS validator with numeric rules and a max on perPage instead of manual clamping. For APIs, either return the paginator directly (it serializes via toJSON()) or map rows through a transformer while forwarding the metadata keys to your frontend pagination component.

When to Use Manual Keyset Pagination

With millions of rows or an infinite-scroll feed, keyset pagination avoids both the COUNT query and OFFSET scanning. Instead of a page number, the client passes the ID (or another indexed, monotonic value) of the last item from the previous response.

import Post from '#models/post'
import type { HttpContext } from '@adonisjs/core/http'

export default class PostsController {
  async index({ request }: HttpContext) {
    const lastId = Number(request.input('after', 0))
    const perPage = Math.min(Number(request.input('perPage', 20)), 50)

    const posts = await Post.query()
      .where('id', '>', lastId)
      .orderBy('id', 'asc')
      .limit(perPage)

    return {
      data: posts,
      // Client sends this back as ?after= on the next request
      nextCursor: posts.length ? posts[posts.length - 1].id : null,
    }
  }
}

Keyset pagination requires an index on the cursor column and a stable sort. If you sort by a non-unique column like createdAt, include the ID as a tiebreaker in both the orderBy and the cursor comparison, or rows can be skipped or duplicated. You also lose the total count, so UIs must use \"load more\" rather than numbered pages.

Verifying the Behavior

Do not take the two-query claim on faith; confirm it against your installed version:

1. Seed a test table and create a route calling Model.query().paginate(1, 10). Inspect the JSON response and note the exact metadata key names. These differ between AdonisJS v5 and v6, so check your installed @adonisjs/lucid type definitions rather than assuming.
2. Enable query logging (set debug: true in your database config for a local run) and confirm paginate() issues exactly two statements: the SELECT and the COUNT.
3. On a large seeded dataset, request a deep page (for example page=10000) and compare the response time against the keyset query. This validates whether the trade-off actually matters for your data volume.

Limitations

Deep offset pagination is slow on large tables regardless of framework; paginate() does not solve it. COUNT queries on heavily filtered joins can cost as much as the data query. And keyset pagination adds real complexity around multi-column cursors and backward navigation. Measure against your actual dataset before committing to either approach.