Fixing N+1 Queries in AdonisJS with Lucid Preloading

The N+1 Trap Hiding in Your Loops

You fetch a list of posts, pass them to a view, and everything feels fine until you check the database logs. Instead of one query for posts and one for authors, you see twenty queries—one for every single post. This is the N+1 query problem, and it is one of the most common performance issues in AdonisJS applications using Lucid ORM.

The root cause is lazy loading. When you access a relationship property like post.author inside a loop, Lucid executes a separate database query for each parent record. A page with 50 posts triggers 50 additional queries. Under load, that adds up quickly.

Preloading: The Direct Fix

Lucid provides a preload method on model query builders to eager load relationships. Instead of querying authors one by one, you tell Lucid upfront which relationships you need:

const posts = await Post.query().preload('author').exec()

Under the hood, Lucid typically issues a second query to fetch the related rows and stitches them together in memory. It does not generate a SQL JOIN by default. This distinction matters because a JOIN would change the shape of your result set, while preloading preserves your model instances.

Preloading also supports nested relationships through dot notation. If each author has a profile, you can load it in the same pass:

const posts = await Post.query().preload('author.profile').exec()

You can also filter related rows using a closure, which is useful when you only want active authors or published profiles:

const posts = await Post
  .query()
  .preload('author', (authorQuery) => {
    authorQuery.where('isActive', true)
  })
  .exec()

A Worked Example: Paginated Posts with Authors

Preloading without pagination can be dangerous. If you have 10,000 posts, preloading authors fetches 10,000 author rows into memory at once. The safer pattern is to paginate first, then preload for the current page only.

Assume you are running AdonisJS 6 with Lucid ORM installed. Create or edit a route file—typically under start/routes.ts—and add a controller action. You will need write access to the project directory.

router.get('/posts', async ({ request, response }) => {
  const page = request.input('page', 1)
  const perPage = 20

  const posts = await Post.query()
    .preload('author')
    .paginate(page, perPage)

  return response.ok(posts)
})

In this example, page and perPage are placeholders you should adjust based on your API contract. The key decision is calling paginate alongside preload so the database only fetches authors for the posts on the current page.

To verify the behavior, check your installed Lucid version first. Run this command in your project root:

npm ls @adonisjs/lucid

Where to run it: your terminal, inside the AdonisJS project directory. Permissions: read access to node_modules and package.json. Expected check: the output should list a version compatible with your AdonisJS release. AdonisJS 6 uses a newer Lucid line than AdonisJS 5, and query builder APIs can differ between major versions. Risk: if the command exits with an error, your dependencies may be corrupted or the package may be scoped differently in your setup; pause and confirm before relying on preload signatures.

Next, inspect the generated SQL. You can temporarily enable query logging in config/database.ts by setting debug: true in your connection block, or attach a listener with db.on('query', ...). You should see two queries: one for the paginated posts and one for the related authors. If you see one query per post, preloading is not active. Remember to disable the debug setting when finished; leaving verbose query logging enabled in production can slow down requests and leak sensitive data into logs.

Trade-offs and Limitations

Preloading is not free. It fetches all matching related rows upfront, which increases memory usage and response payload size. If your API consumer only needs the author name for the first three posts, preloading every author is wasteful. In those cases, lazy loading a single record—or restructuring the endpoint—may be better.

Deeply nested preloads add complexity. A chain like author.profile.socialLinks can generate multiple queries and large in-memory object graphs. Lucid handles the stitching, but your serialization layer still has to walk that graph. Keep nesting shallow unless the relationship is genuinely required.

Another limitation is that preloading does not replace SQL JOINs when you need to filter or sort parent records based on child columns. If you want "posts that have at least one comment," preloading alone will not help; you need a join or a subquery.

Practical Takeaways

Start by auditing your templates and controllers for relationship access inside loops. Replace those patterns with preload on the query builder. Always pair preloading with pagination for list endpoints. Verify your Lucid version and inspect query logs to confirm the N+1 pattern is gone. When payload size becomes a concern, reconsider whether the relationship belongs in the list response at all.