Paginate Lucid query results in AdonisJS

Problem and takeaway

You need an API endpoint that returns a page of database rows and enough metadata for a frontend to render page numbers. Passing raw `page` and `perPage` query parameters straight into Lucid can cause crashes, unexpected empty results, or expensive full-table scans.

Use `Model.query().paginate(page, perPage)` after coercing and clamping the inputs to positive integers. Offset pagination is simple and fits page-number interfaces, but response time grows as you request deeper pages on large tables.

Desired outcome

A `GET /posts` endpoint that accepts `page` and `perPage`, runs a safe offset query through Lucid, and returns JSON containing the current page of rows plus pagination metadata such as total count and current page.

Prerequisites

• An AdonisJS v5 or v6 project with Lucid installed and a database connection configured.
• A Lucid model (for example, `Post`) mapped to a table that already contains rows.
• Access to the project terminal and the ability to start the development server.

Procedure

1. Confirm the installed major version

AdonisJS v5 and v6 both support `.paginate()`, but route registration syntax differs. Run this from the project root:

npm list @adonisjs/core

Expected output shows the installed major version. The examples below use v6 syntax (`router`); for v5, replace `router.get` with `Route.get` and adjust imports.

2. Create the controller

From the project root, run:

node ace make:controller Posts

This creates a controller file in your project's HTTP controller directory. Open it and add an `index` method:

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

export default class PostsController {
  async index({ request, response }: HttpContext) {
    const rawPage = request.input('page', 1)
    const rawPerPage = request.input('perPage', 20)

    const page = Math.max(1, Number(rawPage) || 1)
    const perPage = Math.min(100, Math.max(1, Number(rawPerPage) || 20))

    const paginated = await Post.query().paginate(page, perPage)

    return response.ok(paginated.serialize())
  }
}

Where: project root. Permissions: normal user; no sudo required. Placeholders: replace `Post` with your model name and `100` with your preferred maximum page size. Risk: `make:controller` overwrites an existing file with the same name.

3. Register the route

Open `start/routes.ts` and add:

router.get('/posts', [PostsController, 'index'])

Import the controller at the top of the file if it is not imported already. In v6, this typically looks like:

import { router } from '@adonisjs/core/services'
import PostsController from '#controllers/posts_controller'

Start the server:

node ace serve

4. Understand what paginate does

Calling `.paginate(page, perPage)` on a Lucid query builder runs two queries: a `COUNT` query for the total and a `LIMIT`/`OFFSET` query for the current page. You can chain `.where()`, `.orderBy()`, `.select()`, and `.with()` before it. The count respects filters, but ordering and limits apply only to the row query.

Expected checks

1. Run the endpoint:

curl -s 'http://localhost:3333/posts?page=2&perPage=10'

Expected: an HTTP 200 response with a JSON body. Inspect the actual serialized shape; it typically contains a `data` array and a `meta` object with fields such as `total`, `perPage`, `currentPage`, and `lastPage`. Exact keys depend on your installed version and paginator type. If your version does not expose `serialize()` directly, return the paginator object and let Adonis serialize it, or consult your installed type definitions.

1. Change `page` and confirm `currentPage` and the `data` array change accordingly.
2. Send `page=0` or `perPage=-5` and confirm the server still responds with a valid page instead of a 500 error.
3. Request a deep page (for example, `page=1000`) and compare response time against page 1. Slower deep pages are the expected offset-pagination trade-off.

Engineering decision: offset versus cursor pagination

Factor	Offset pagination (.paginate)	Cursor pagination (manual where/limit)
Page-number UI	Natural fit	Awkward; better for infinite scroll
Inserts and deletes between pages	Rows can shift or repeat	Stable when keyed on an indexed, monotonic column
Deep page cost	Grows as OFFSET increases	Roughly stable with an indexed cursor column
Lucid support	Built-in `.paginate()`	Manual query builder with `where` and `limit`

If users rarely go beyond the first few pages and need page numbers, offset pagination is appropriate. If users scroll through thousands of rows on a large table, implement cursor-style pagination using an indexed column such as `id` or `created_at`.

Limitations

• Offset pagination does not guarantee constant response time as page numbers grow.
• The serialized JSON shape (`meta`, `links`, `data`) can differ between AdonisJS major versions and paginator types; do not hard-code assumptions.
• The `COUNT` query can be slow on very large tables without proper indexes.
• Never pass raw user input directly to `page` or `perPage`; always coerce and clamp first.

Practical verification

Seed a few hundred rows into your model's table, call `Post.query().paginate(2, 10)` in a temporary script or endpoint, and log the returned object. Inspect its properties directly, then check the actual HTTP response body to confirm the serialized shape for your installed version. After any AdonisJS major-version upgrade, run your test suite and diff API responses to catch pagination shape changes.

Recovery and rollback

This procedure changes application code but not the database schema. To revert, remove the route from `start/routes.ts` and delete or restore the controller file. If you use Git:

git checkout -- start/routes.ts app/Controllers/Http/posts_controller.ts

Then restart the server. If you created a migration as part of this work, roll it back with `node ace migration:rollback`.