Paginated List Endpoints in AdonisJS with Lucid ORM

Returning an entire table as one JSON response exhausts memory and makes clients unusable as the table grows. The practical fix is a GET collection endpoint that returns one page of rows plus pagination metadata, with server‑side clamping of page size and an explicit deterministic order so pages do not repeat or skip rows under concurrent writes.

Desired outcome

A GET endpoint that accepts page and perPage query parameters, returns data for that page and pagination metadata, and behaves predictably for out‑of‑range pages, bad input, and growing tables. The response shape is produced by Lucid’s paginator, not by hand‑building a count query.

Prerequisites

• AdonisJS 5 or 6 project with Lucid ORM configured and a working database connection.
• A Lucid model and its migration, for example User with an indexed created_at column.
• A controller file and routes file where the endpoint can be registered.

AdonisJS 6 is ESM and TypeScript‑first with a different package layout from AdonisJS 5. Confirm the import paths and paginator metadata keys for the installed major version before freezing an API contract.

Controller and route wiring

Validate, clamp, and paginate in the controller

Read page and perPage from the query string, coerce to numbers, clamp perPage to a server maximum, and always order the query. Pagination without ORDER BY is non‑deterministic.

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

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

    let page = Number(rawPage)
    let perPage = Number(rawPerPage)

    if (!Number.isFinite(page) || page < 1) page = 1
    if (!Number.isFinite(perPage) || perPage < 1) perPage = 10

    const MAX_PER_PAGE = 100
    if (perPage > MAX_PER_PAGE) perPage = MAX_PER_PAGE

    const users = await User.query()
      .orderBy('created_at', 'desc')
      .orderBy('id', 'desc')
      .paginate(page, perPage)

    return users
  }
}

Run this controller in the app process. No elevated permissions are required beyond normal database read access. Risk: an uncapped perPage allows a client to request the whole table and increase DB load and response size.

Register the route

Add the route in start/routes.ts. The route maps GET /users to the controller method.

import router from '@adonisjs/core/router'
import UsersController from '#controllers/users_controller'

router.get('/users', [UsersController, 'index'])

Eager load relations correctly

If the response needs relations, preload them on the paginated query instead of looping over rows. Looping turns one page fetch into N+1 queries.

const users = await User.query()
  .preload('posts', q => q.orderBy('created_at', 'desc').limit(5))
  .orderBy('created_at', 'desc')
  .paginate(page, perPage)

Expected checks

• Metadata reports total rows, page size, current page and last page. Exact key names differ between AdonisJS 5 and 6, so verify against the installed version.
• A page past the end returns an empty data array with HTTP 200, not an error.
• perPage above the server cap is reduced to MAX_PER_PAGE.
• Ordering is explicit and deterministic, preventing row repetition or skipping.

Recovery and limitations

Non‑numeric or negative page values fall back to page 1. You can also return a 400 validation error depending on API style. Offset pagination is not stable under concurrent inserts or deletes; total counts are approximate on busy tables.

For very large offsets, offset cost grows linearly. Switch to keyset cursor pagination ordered by an indexed unique column when deep paging is required. Do not assume a built‑in cursor helper exists; verify whether the installed Lucid version provides one.

Verification

1. Start the app and request /users?page=1&perPage=10, /users?page=2&perPage=10, and a page past the end. Confirm data length, metadata fields and HTTP status match expectations.
2. Inspect generated SQL via query logging to confirm LIMIT and OFFSET values and the presence of ORDER BY.
3. Check package.json for AdonisJS and Lucid versions and read the matching pagination docs for exact metadata key names.
4. Seed more rows than one page and compare the union of all pages against a full unpaginated fetch to detect skipped or duplicated rows.
5. Send page=abc and perPage=100000 and confirm the endpoint clamps or rejects rather than erroring or returning the whole table.

Limitations include version‑sensitive paginator metadata, approximate totals under write load, and offset drift on large tables. Keep ordering explicit and perPage capped to maintain predictable performance.