Cap and Validate AdonisJS Lucid Pagination Before It Stalls Your Database

Unbounded list endpoints can stall your database

Your /posts endpoint works fine with ten rows. Then a client requests page 500 with perPage=1000, sorted by created_at. The response crawls, database CPU spikes, and a simple list query becomes expensive. The takeaway: never pass request.qs straight into Lucid's paginate(). Coerce and cap page and perPage, and know that every paginate() call runs a COUNT(*) query plus a LIMIT/OFFSET query.

What AdonisJS 5 Lucid paginate() actually does

In AdonisJS 5 with @adonisjs/lucid, calling Model.query().paginate(page, perPage) executes two queries against the database:

• SELECT ... LIMIT ? OFFSET ? to fetch the current page of rows.
• SELECT count(*) as total FROM ... to compute the total row count for the same filters.

It returns a paginated object with data and meta. The meta object contains total, perPage, page, and lastPage. Returning that object from a controller serializes both the rows and the pagination metadata in one JSON response.

meta field	Meaning
total	Total matching rows across all pages
perPage	Requested page size after capping
page	Current page number
lastPage	Calculated from total divided by perPage

Worked example: a safe posts list controller

import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import Post from 'App/Models/Post'

export default class PostsController {
  public async index({ request, response }: HttpContextContract) {
    const page = Math.max(1, parseInt(request.qs.page ?? '1', 10) || 1)
    const perPage = Math.min(100, Math.max(1, parseInt(request.qs.perPage ?? '25', 10) || 25))

    const posts = await Post.query().orderBy('created_at', 'desc').paginate(page, perPage)

    return response.ok(posts)
  }
}

Where to run: Edit app/Controllers/Http/PostsController.ts, or the equivalent controller for your resource. Adjust Post to your actual Lucid model.

Permissions: Write access to the project directory. No migration is required for this change.

Placeholders: Post is your model; created_at is the sort column. Replace them with your schema.

Expected checks: Call GET /posts?page=2&perPage=5 against a table with more than five rows. Assert response.data.length <= 5, response.meta.page === 2, and response.meta.total equals the matching row count.

Risks: parseInt returns NaN for non-numeric input, so the || 1 fallback prevents NaN from reaching paginate(). Negative values are clamped to 1. Lucid parameterizes bindings, so this pattern does not introduce SQL injection, but an uncapped perPage still lets a client inflate payload size and memory usage.

Trade-offs: OFFSET scans and the hidden COUNT

Offset pagination becomes expensive as the page number grows. To serve page 1000 with perPage=25, the database must scan and discard roughly 25,000 earlier rows before returning 25. Add indexes on columns used in ORDER BY and WHERE clauses, but understand that indexes do not eliminate the OFFSET scan cost entirely.

The COUNT(*) query can also be slow on large tables, especially when filters exclude many rows or no supporting index exists. Measure this cost before adopting offset pagination at scale. For deep pagination or infinite feeds, consider keyset pagination—often written as WHERE created_at < ? ORDER BY created_at DESC LIMIT ?—instead of paginate().

Verify before you ship

1. Check package.json for @adonisjs/lucid and @adonisjs/core major versions. This guidance assumes AdonisJS 5; confirm the API surface for your installed version.
2. Enable Lucid query logging or run EXPLAIN on the generated SQL to confirm LIMIT/OFFSET and index usage on the COUNT(*) query.
3. Hit the endpoint with ?page=2&perPage=5 against a seeded table with more than five rows and assert data.length <= 5, meta.page === 2, and meta.total matches the row count.
4. Monitor response time as the page number increases. Linear degradation is a strong signal that OFFSET is the bottleneck.

Closing: cap it, log it, move on

Pagination is a read operation, so there is no rollback script. The practical fix is input validation plus a hard perPage ceiling. Start with a default of 25 and a maximum of 100, measure COUNT(*) cost under real filters, and switch to keyset pagination only when deep pages prove too expensive.