Deciding How to Paginate List Endpoints in AdonisJS 6 with Lucid

Every list endpoint eventually faces the same question: how do you return 50,000 users without returning 50,000 users? In AdonisJS, Lucid's query builder ships with a built-in paginate() method that handles the common case well — but the real engineering decision isn't whether to use it. It's where you put the guardrails around it, and when to stop using it.

The short version: use paginate() for most admin panels and content lists, centralize your page-size defaults and caps in one place, and plan an escape hatch to keyset pagination for your hottest, largest tables.

What paginate() actually does

Calling paginate(page, perPage) on a Lucid query builder runs two queries: a COUNT(*) over your filtered result set, and the data query with a LIMIT/OFFSET applied. What you get back is a paginator object containing the rows plus metadata — total count, per-page size, current page, last page, and flags for first/last page.

That metadata is what makes it useful for APIs. When the paginator is serialized (returned from a controller or explicitly via toJSON()), the response takes the familiar shape of a meta object alongside a data array. Your frontend gets everything it needs to render "Page 3 of 42" without a second request.

The same method exists on both model query builders (User.query()) and the raw database query builder, so behavior stays consistent whether you're working with models or plain tables.

The decision that matters: cap the input

The paginator happily accepts whatever numbers you hand it. If you pass client-supplied perPage straight through, a request for ?perPage=100000 becomes a memory and response-size problem on your server. The fix is boring but important: define defaults and a hard ceiling once, and apply them everywhere.

A small helper or a dedicated service keeps this honest:

// app/services/pagination.ts
export default class Pagination {
  static readonly DEFAULT_PER_PAGE = 15
  static readonly MAX_PER_PAGE = 100

  static fromRequest(request: { input: (key: string, def?: any) => any }) {
    const page = Math.max(1, Number(request.input('page', 1)) || 1)
    const requested = Number(request.input('perPage', this.DEFAULT_PER_PAGE)) || this.DEFAULT_PER_PAGE
    const perPage = Math.min(Math.max(1, requested), this.MAX_PER_PAGE)
    return { page, perPage }
  }
}

Then the controller stays thin:

// app/controllers/users_controller.ts
import User from '#models/user'
import Pagination from '#services/pagination'
import type { HttpContext } from '@adonisjs/core/http'

export default class UsersController {
  async index({ request }: HttpContext) {
    const { page, perPage } = Pagination.fromRequest(request)

    return User.query()
      .whereNull('deletedAt')
      .orderBy('createdAt', 'desc')
      .paginate(page, perPage)
  }
}

Run this in a standard AdonisJS 6 controller; no special permissions are needed beyond your normal database access. A quick check that it works: seed a table with a few hundred rows, hit GET /users?page=2, and confirm the response contains a meta object with total, perPage, currentPage, and lastPage matching your seed data. Then request ?perPage=99999 and confirm meta.perPage comes back as your cap, not the requested value.

The trade-off: offset doesn't scale forever

paginate() uses offset-based pagination. To serve page 5,000 at 15 rows per page, the database must scan and discard roughly 75,000 rows before returning anything. On small and medium tables this is invisible. On large, hot tables it becomes the dominant cost of the endpoint — and the COUNT(*) query adds its own cost on top, especially with joins or broad filters.

This is the honest limitation to design around:

• Offset pagination is fine for admin UIs, filtered lists, and anywhere users genuinely jump to arbitrary pages.
• It's the wrong tool for infinite-scroll feeds over millions of rows. There, keyset (cursor) pagination — filtering with where('id', '<', lastSeenId) — keeps query time flat regardless of depth, at the cost of losing "jump to page 37."

You don't have to choose globally. A pragmatic pattern is paginate() everywhere by default, with keyset pagination introduced per-endpoint when profiling justifies it. To find out whether you're near that line, benchmark the endpoint at a deep offset (say, page 5,000) against production-like data volume and watch the query time. If it's flat, stop worrying.

Version note and verification

The paginator API and metadata field names have shifted between AdonisJS major versions. Before copying any example — including this one — check the installed @adonisjs/lucid version in package.json and confirm the paginate() signature and the exact meta field names against the official docs for that major version. The fastest ground truth is empirical: seed data, call paginate(1, 15), and inspect the returned object yourself.

The takeaway

Lucid's paginate() is the right default for most AdonisJS list endpoints: one call, consistent JSON, page navigation metadata included. The engineering work around it is small but non-optional — centralize defaults, cap perPage server-side, and keep keyset pagination in your back pocket for the tables that outgrow offsets. Do those three things and pagination stops being a decision you revisit per endpoint.