Implementing Server-Side Pagination in AdonisJS with the Lucid Query Builder

The Problem: Memory Exhaustion from Large Datasets

Fetching an entire database table into memory to handle pagination on the client side leads to application crashes and slow response times as your data grows. To maintain performance, you must implement server-side pagination, where the database only returns the specific slice of data required for the current view.

The takeaway: Use the paginate() method provided by Lucid (the AdonisJS ORM) to automatically handle LIMIT and OFFSET calculations and generate the metadata required for frontend pagination controls.

Prerequisites

• An AdonisJS project (Version 5.x or 6.x) with Lucid ORM configured.
• A defined Model (e.g., User) linked to a database table containing more records than your intended page size.
• Basic familiarity with async/await patterns in JavaScript.

Implementing the Pagination Logic

The paginate method replaces the manual use of .limit() and .offset(). When you call paginate(page, perPage), Lucid executes two queries: one to count the total records matching your criteria and one to fetch the specific subset of rows.

Run this logic within a controller method. Ensure you have the necessary permissions to execute queries against your database user.

// app/Controllers/Http/UsersController.ts
import User from 'App/Models/User'
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'

export default class UsersController {
  public async index({ request, response }: HttpContextContract) {
    // 1. Extract pagination parameters from the query string
    // Use defaults to prevent errors if the client omits these values
    const page = request.input('page', 1)
    const perPage = request.input('perPage', 10)

    // 2. Chain query builder methods before calling paginate
    const users = await User.query()
      .where('status', 'active')
      .orderBy('createdAt', 'desc')
      .paginate(page, perPage)

    return response.ok(users)
  }
}

Understanding the Paginator Response

Unlike .exec() or .all(), which return an array of models, .paginate() returns a Paginator instance. This object contains the data and a meta object essential for the UI.

Property	Description	Example Value
data	Array of Model instances for the current page.	[{ id: 1, ... }, { id: 2, ... }]
meta.total	Total records matching the query across all pages.	150
meta.currentPage	The current page index.	1
meta.lastPage	The total number of pages available.	15
meta.perPage	Number of items requested per page.	10

Critical Engineering Decisions

Avoiding the Double-Limit Trap

A common mistake is calling .limit() before .paginate(). Because paginate() internally calculates the limit and offset based on the perPage argument, adding a manual .limit() will conflict with the paginator, often resulting in empty sets or incorrect row counts.

Handling Eager Loading (N+1 Prevention)

If you need related data (e.g., User profiles), use .preload() before calling .paginate(). This ensures that the related data is fetched in a single optimized query for the paginated subset, rather than one query for every row returned.

// Correct: Preload then Paginate
await User.query().preload('profile').paginate(page, perPage)

Verification and Diagnostics

To verify the implementation, execute a request to your endpoint using a tool like cURL or Postman:

GET /users?page=2&perPage=5

Expected Result: The response should contain a data array with exactly 5 items (unless the total count is less) and a meta object where currentPage is 2.

To debug the actual SQL being generated, enable the query logger in your config/database.ts or use a database proxy. You should see a SELECT COUNT(*) query followed by a SELECT ... LIMIT 5 OFFSET 5 query.

Limitations

• Performance on Massive Tables: For tables with millions of rows, OFFSET becomes slow because the database must still scan through the skipped rows. In such cases, consider "cursor-based pagination" (using where('id', '>', lastId)) instead of paginate().
• Count Overhead: The automatic count query can be expensive on complex joins. If performance dips, you may need to implement a custom count or cache the total.