Efficiently Loading Related Data with AdonisJS Lucid ORM Eager Loading and Pagination

Problem: Slow API responses when listing posts with their comments

When building a blog API, a common endpoint returns a list of posts together with their comments. Naively fetching each post and then looping to load its comments results in the N+1 query problem: one query for the posts and an additional query for each post’s comments. As the list grows, response time and database load increase sharply.

Thesis: Using Lucid’s .preload() for eager loading combined with .paginate() lets you retrieve paged parent records with their related children in a predictable number of queries, improving performance without over‑fetching.

How eager loading works in Lucid

Lucid’s .preload() method issues a separate SELECT for each relation instead of a JOIN. This avoids the Cartesian product explosion that can occur with deep joins while still loading the related rows in a single extra query per relation.

// app/Models/Post.js
class Post extends Model {
  comments() {
    return this.hasMany('App/Models/Comment')
  }
}

In a controller you can eager‑load comments like this:

await Post.query().preload('comments').fetch()

Lucid will generate two queries: one for posts and one for comments where the comment’s post_id matches the IDs returned from the first query.

How pagination works in Lucid

The .paginate(page, perPage) helper adds LIMIT and OFFSET clauses and returns a pagination meta object containing total, perPage, currentPage and lastPage. It works on the query builder, so you can chain it after .preload().

await Post.query().preload('comments').paginate(1, 10)

Worked example: paginated post list with comments

Assume a fresh AdonisJS 5.x project with the Post and Comment models defined as above. The following controller action returns JSON for the first page of posts, each post containing its comments.

// app/Controllers/Http/PostController.js
const Post = use('App/Models/Post')

class PostController {
  async index ({ request, response }) {
    const page = request.input('page', 1)
    const limit = request.input('limit', 10)

    const posts = await Post.query()
      .preload('comments')
      .paginate(page, limit)

    return response.json(posts.toJSON())
  }
}
module.exports = PostController

To verify the generated SQL, enable the Lucid query logger in start/app.js:

const Logger = use('Logger')
Database.on('query', (query) => {
  Logger.info(query.query + ' | bindings: ' + query.bindings)
})

Running the endpoint with page=2 and limit=5 produces output similar to:

• select * from "posts" limit 5 offset 5
• select * from "comments" where "comments"."post_id" in (?, ?, ?, ?, ?)

The response body includes a pagination object and a data array where each post contains a comments array:

{
  "pagination": {
    "total": 42,
    "perPage": 5,
    "currentPage": 2,
    "lastPage": 9
  },
  "data": [
    {
      "id": 6,
      "title": "Second page post",
      "comments": [
        { "id": 12, "content": "Nice!" },
        { "id": 13, "content": "Thanks" }
      ]
    }
    // … more posts
  ]
}

Trade‑offs and limitations

• Offset‑based pagination cost: As the page number grows, the OFFSET value increases, causing the database to skip more rows. For very large tables this can become slow; consider cursor‑based pagination (e.g., using a lastId parameter) when deep paging is required.
• Eager loading depth: Each additional .preload() adds another query. Deeply nested relations (e.g., .preload('author').preload('author.posts').preload('author.posts.comments')) can lead to many queries, increasing latency. Evaluate whether every level is needed for the endpoint.
• Memory usage: Preloading a relation that returns thousands of rows (e.g., a post with many comments) loads all those rows into memory. If the child relation itself is large, paginate the child relation separately or apply limits.

Actionable closing

Start by adding .preload() for the relations you need and wrap the query with .paginate(). Enable the query logger to confirm that Lucid emits exactly one extra SELECT per relation and a single LIMIT/OFFSET for pagination. Monitor response times as you increase the page number; if you notice degradation, experiment with a cursor‑based approach for the parent table while keeping eager loading for the child relations. This combination gives you predictable query counts, avoids the N+1 problem, and keeps API responses fast for typical list endpoints.