AdonisJS Lucid Pagination: Start with Offset, Graduate to Keyset

The problem: a product listing that must stay correct as the catalog grows

You're building a product catalog in AdonisJS. The first page loads fast. By page 50, the API response drags. By page 500, it times out. The culprit isn't Lucid — it's how OFFSET pagination works under the hood. Lucid's paginate() gives you a working paginated endpoint in one line, but it relies on two SQL statements per request: a COUNT over the filtered set and a LIMIT/OFFSET fetch for the current page. As the offset grows, the database still scans and discards every skipped row. The COUNT adds its own cost on large filtered tables.

Takeaway: Start with Lucid's built-in paginate() for simplicity and correctness (stable ordering, eager loading, metadata). When page depth or table size makes latency unacceptable, switch to keyset (cursor) pagination — a pattern you implement with a WHERE clause on a stable sort key. Lucid doesn't ship a cursor paginator out of the box, so the migration is a deliberate engineering decision, not a framework upgrade.

How Lucid's paginate works: two queries, one paginator object

Calling Product.query().where('isActive', true).paginate(page, perPage) issues:

1. COUNT query: SELECT COUNT(*) FROM products WHERE is_active = true — returns the total matching rows.
2. FETCH query: SELECT * FROM products WHERE is_active = true ORDER BY ... LIMIT perPage OFFSET (page-1)*perPage — returns the current page's rows.

The result is a Paginator instance (AdonisJS 6) or SimplePaginator (AdonisJS 5) that serializes to JSON with data (the rows), meta (total, perPage, currentPage, lastPage, firstPage, firstPageUrl, lastPageUrl, nextPageUrl, previousPageUrl), and links for building navigation. In AdonisJS 6, the paginator also implements toJSON() so returning it directly from a controller yields the full payload.

If you don't need the total count — for example, an infinite-scroll feed where the client only asks for "next" — use forPage(page, perPage) instead. It applies only the LIMIT/OFFSET and skips the COUNT, saving one round trip.

Worked example: paginated product list with eager loading and stable ordering

Below is a minimal controller for AdonisJS 6 (Lucid v20+) that clamps input, enforces deterministic ordering, and eager-loads a relation to avoid the N+1 problem when rendering category names.

// app/controllers/products_controller.ts
import { HttpContext } from '@adonisjs/core/http'
import Product from '#models/product'
import vine from '@vinejs/vine'

export default class ProductsController {
  // Validation: page ≥ 1, perPage 1–50
  private readonly paginateSchema = vine.compile(
    vine.object({
      page: vine.number().min(1).optional(),
      perPage: vine.number().min(1).max(50).optional(),
    })
  )

  async index({ request, response }: HttpContext) {
    const { page = 1, perPage = 20 } = await request.validateUsing(this.paginateSchema)

    const paginator = await Product.query()
      .where('isActive', true)
      // Stable ordering: created_at DESC, then id DESC as unique tiebreaker
      .orderBy('created_at', 'desc')
      .orderBy('id', 'desc')
      // Eager-load category so each product row has .category without extra queries
      .preload('category')
      .paginate(page, perPage)

    // Returns JSON with { data: [...], meta: {...}, links: {...} }
    return paginator
  }
}

Key points in this snippet:

• Input validation via VineJS (or simple clamping) prevents crafted requests from fetching oversized result sets.
• Dual orderBy ensures page stability. Without the id tiebreaker, inserting a new product with the same created_at can shift rows between pages, causing duplicates or gaps.
• preload('category') runs a second query (SELECT * FROM categories WHERE id IN (...)) and attaches the relation to each product. Rendering product.category.name in a template or API response triggers zero additional queries.
• Returning the paginator directly works because Lucid's paginator implements toJSON(). In Edge templates, you'd iterate paginator.data and use paginator.meta for link generation.

Trade-off: when OFFSET/COUNT stops scaling

OFFSET pagination degrades predictably. For page N with page size P, the database scans N×P index entries (or table rows) before returning P results. The COUNT query scans the entire filtered index. On a 10-million-row table with a selective WHERE, page 1 is instant; page 10,000 can take seconds.

Keyset (cursor) pagination replaces OFFSET with a WHERE clause on the sort key:

// Cursor-based fetch (pseudo-code — you write this)
const cursor = request.input('cursor') // e.g., "2024-01-15T12:00:00.000Z:42"
const [createdAt, id] = cursor.split(':')

const products = await Product.query()
  .where('isActive', true)
  .where((q) => {
    q.where('created_at', '<', createdAt)
      .orWhere((sq) => sq.where('created_at', createdAt).where('id', '<', id))
  })
  .orderBy('created_at', 'desc')
  .orderBy('id', 'desc')
  .limit(perPage + 1) // fetch one extra to know if there's a next page
  .preload('category')

The cursor encodes the last-seen sort key. The next request sends that cursor; the query seeks directly to the position via index, no scan of skipped rows. The trade-off: you lose random-access page jumps (no "jump to page 42"), the client must store the cursor, and the sort key must be unique and immutable (hence created_at + id).

Lucid has no built-in cursor paginator as of AdonisJS 6. The pattern above is what you implement when the metrics justify it.

How to verify the behavior in your app

1. Confirm two queries: Enable Lucid's query log (Database.prettyPrint() or Logger.query) and hit the endpoint. You should see the COUNT and the LIMIT/OFFSET select.
2. Compare with forPage: Swap .paginate(page, perPage) for .forPage(page, perPage) and observe the COUNT disappear from the log.
3. Test page stability: While on page 2, insert a new active product with a created_at that sorts before page 2's first row. Refresh page 2 — with the id tiebreaker, the original rows stay; without it, they shift.
4. Check JSON shape: console.log(paginator.toJSON()) and verify data, meta.total, meta.lastPage, and links.next match your frontend expectations. Property names differ between AdonisJS 5 and 6 — consult the Lucid pagination docs for your pinned version.

Closing: a pragmatic migration path

Use paginate() for admin panels, search results, and any UI that needs page numbers, totals, and jump links. It's correct, eager-loads relations, and returns ready-to-use metadata. Cap perPage at 50 (or your UX limit) and validate page/perPage on every request.

When the product catalog hits hundreds of thousands of rows and deep-page latency appears in your APM, introduce a cursor-based endpoint (/products/feed?cursor=...) for infinite scroll or high-throughput consumers. Keep the offset endpoint for the dashboard. The switch is a query rewrite, not a framework migration — and that's exactly the kind of control Lucid gives you.