Managing Large Datasets in AdonisJS with Lucid Pagination

The Memory Wall in Data Retrieval

\n

When building an API, the instinct is often to fetch all records matching a query and return them to the client. This works during development with ten rows of seed data, but it creates a critical failure point in production. Loading thousands of database records into Node.js memory—known as the \"memory wall\"—leads to increased garbage collection cycles, high latency, and eventually, OutOfMemory crashes.

\n

The solution is to shift the burden of data slicing from the application server to the database engine. In AdonisJS, the Lucid ORM provides a built-in .paginate() method that automates the calculation of SQL LIMIT and OFFSET clauses, ensuring the server only handles a small, manageable slice of data at any given time.

\n\n

How Lucid Handles Pagination

\n

Lucid's pagination isn't just a wrapper around an array slice; it is a query builder integration. When you call .paginate(page, limit), Lucid performs two distinct operations: it counts the total number of records matching your criteria and fetches the specific subset for the requested page.

\n

This process returns a PaginatedResponse object. Unlike a standard array, this object includes a meta property containing the total count, the current page, and the total number of pages available. This allows the frontend to render pagination controls without the backend having to manually calculate these values for every request.

\n\n

Implementation Example

\n

To implement pagination, you typically integrate it within a controller method. This example assumes you have a User model and a route that accepts page and limit as query parameters.

\n\n

// start/routes.ts\nrouter.get('/users', 'UsersController.index')\n

\n\n

// app/controllers/users_controller.ts\nimport { HttpContext } from '@adonisjs/core/http'\nimport User from '#models/user'\n\nexport default class UsersController {\n  async index({ request, response }: HttpContext) {\n    // 1. Extract parameters with sensible defaults\n    const page = request.input('page', 1)\n    const limit = request.input('limit', 10)\n\n    // 2. Execute paginated query\n    // .orderBy is critical to ensure consistent results across pages\n    const users = await User.query()\n      .where('status', 'active')\n      .orderBy('createdAt', 'desc')\n      .paginate(page, limit)\n\n    return response.ok(users)\n  }\n}\n

\n\n

Verification and Diagnostics

\n

To verify this is working as intended, run your application with the TS_NODE_TRANSPILE_ONLY=true environment variable and enable SQL logging in your database configuration. You should see two queries in the console for every request:

\n

\n
• A SELECT count(*) query to determine the total record set.
\n
• A SELECT ... LIMIT 10 OFFSET 0 query (for page 1) to retrieve the data.
\n

\n\n

The resulting JSON response will follow this structure:

\n

{\n  \"data\": [...],\n  \"meta\": {\n    \"total\": 500,\n    \"perPage\": 10,\n    \"currentPage\": 1,\n    \"lastPage\": 50\n  }\n}\n

\n\n

The Trade-off: Offset vs. Performance

\n

While .paginate() is the standard approach, it relies on Offset-based pagination. This introduces a performance bottleneck known as \"Deep Paging\". As the OFFSET value increases (e.g., requesting page 1,000), the database must still scan through all previous records before discarding them to reach the desired slice.

\n

Additionally, if records are inserted or deleted while a user is navigating pages, they may see the same record twice or skip a record entirely. To mitigate this, always include a unique tie-breaker in your .orderBy() clause, such as the primary key id, to ensure a deterministic sort order.

\n\n

Practical Summary

\n

Use Lucid's .paginate() whenever a table is expected to grow beyond a few hundred rows. It protects your Node.js process from memory exhaustion and provides the necessary metadata for client-side navigation. For extremely large datasets where users only move \"next\" rather than jumping to specific page numbers, consider implementing cursor-based pagination using .where('id', '>', lastId) as a high-performance alternative.