Using AdonisJS Lucid ORM paginate for efficient list responses

Quick answer

Call Model.query().paginate(page, limit) to receive a paginated result set that includes both the data rows and a meta object with total count, current page, per‑page size and last page. This lets you build pagination UI without writing extra count queries.

How it works – a worked example

Assume you have a Post model and want to expose a /posts endpoint that respects query parameters page and limit.

// start/routes/posts.js
Route.get('/posts', async ({ request }) => {
  // read parameters, fallback to sensible defaults
  const page = request.input('page', 1);
  const limit = request.input('limit', 15);

  // Lucid query builder – paginate replaces any existing limit/offset
  const posts = await Post.query().paginate(page, limit);

  // The returned object already contains data and meta
  return posts;
});

The paginate method:

1. Ignores any previously applied limit or offset clauses.
2. Executes a SELECT COUNT(*) query to determine total rows.
3. Runs a limited SELECT for the current page.
4. Returns an object shaped like:

{
  "data": [ /* array of Post instances */ ],
  "meta": {
    "total": 137,
    "per_page": 10,
    "current_page": 3,
    "last_page": 14,
    "first_page": 1,
    "first_page_url": "http://localhost:3333/posts?page=1&limit=10",
    "last_page_url": "http://localhost:3333/posts?page=14&limit=10",
    "next_page_url": "http://localhost:3333/posts?page=4&limit=10",
    "prev_page_url": "http://localhost:3333/posts?page=2&limit=10"
  }
}

Limits and common mistakes

• Default values: If you omit page or limit, Lucid uses page 1 and 15 per page. Forgetting to pass them can lead to unexpected page sizes.
• Overriding clauses: Adding limit or offset before paginate has no effect; those clauses are discarded and the pagination metadata will reflect the values you passed to paginate instead.
• Count query cost: For very large tables the extra COUNT(*) can become a bottleneck. Consider keyset pagination or caching the count when the data set is relatively static.
• Raw SQL: paginate only works on Lucid query builders. Calling it on a raw Database.rawQuery or a already‑executed query will throw an error.
• Non‑numeric input: Passing strings that cannot be cast to numbers results in a validation error; always cast or validate page and limit.

Practical verification

Create a temporary route to inspect the shape:

Route.get('/test-paginate', async () => {
  return await Post.query().paginate(1, 5);
});

Request GET /test-paginate with a tool like curl or a browser and verify that the JSON contains a meta object with fields total, per_page, current_page and last_page. If the meta is missing or the data array length does not match per_page, check that you passed numeric values and that no prior limit/offset was applied.