Stopping the N+1 Leak: Mastering Eager Loading in AdonisJS Lucid

The Hidden Query Spike

You build a simple API endpoint to list users and their associated posts. On your local machine with five users, it feels instant. In production with five hundred users, the database CPU spikes to 100%, and response times crawl. This is the classic N+1 query problem.

The problem occurs when you fetch a primary list of records (1 query) and then loop through them to fetch a related record for each item (N queries). If you have 100 users, you execute 101 queries. In AdonisJS, this happens when you access a relationship property that hasn't been loaded into memory yet.

The solution is Eager Loading: telling Lucid to fetch all related data in a single, optimized batch query before the loop begins.

Using Preload to Flatten Queries

In Lucid, eager loading is handled via the preload method. Instead of letting the ORM fetch relationships lazily as you access them, preload executes a separate query using an IN clause to grab all related records at once.

When you use preload('posts'), Lucid performs two steps: first, it fetches the users; second, it collects all user IDs and runs one query to fetch every post belonging to those IDs. It then maps those posts back to the correct user objects in memory.

Deep Relationships and Constraints

Real-world data is rarely flat. You often need a chain of data, such as Users → Posts → Comments. Lucid handles this through nested preloading. By passing a callback to the preload method, you can dive deeper into the relationship tree or filter the data being loaded.

Constrained loading is particularly useful for performance. Rather than loading every single comment for every post, you can apply a where clause inside the preload callback to only fetch "approved" comments or sort them by the most recent date.

Worked Example: Optimized Data Retrieval

Assume a schema where a User has many Posts, and each Post has many Comments. We want to fetch users, their posts, and only the comments created in the last 30 days.

// Run this within a Controller method
// Required Permissions: Database read access
// Expected Result: A collection of users with nested posts and filtered comments

const users = await User.query() 
  .preload('posts', (postsQuery) => {
    // Sort posts by newest first
    postsQuery.orderBy('createdAt', 'desc')
    
    // Nested preload: Load comments for these posts
    postsQuery.preload('comments', (commentsQuery) => {
      // Constraint: Only load comments from the last 30 days
      commentsQuery.where('createdAt', '>=', luxon.DateTime.now().minus({ days: 30 }).toSQL())
    })
  })

return users

Verification: To verify this is working, enable the SQL logger in your config/database.ts or use a database GUI to monitor active queries. Without preload, you would see a stream of SELECT * FROM posts WHERE user_id = ? queries. With preload, you will see exactly three queries: one for users, one for posts, and one for comments.

The Memory Trade-off

Eager loading isn't a silver bullet. While it solves the database round-trip problem, it shifts the burden to the application server's RAM. Every record fetched via preload is instantiated as a Lucid Model object.

If you preload 1,000 users, each with 50 posts, and each post with 20 comments, you are hydrating 1,000,000+ JavaScript objects into memory. This can lead to JavaScript heap out of memory errors or significant garbage collection pauses.

Practical Limit: If you find yourself preloading more than three levels deep or fetching thousands of related records, consider using .select() to limit the columns retrieved, or switch to a raw SQL join for that specific high-traffic endpoint to avoid the overhead of Model instantiation.

Closing Action

Audit your controllers for any forEach or map loops that access relationship properties. If you see a relationship being accessed inside a loop without a corresponding preload call earlier in the query chain, you have an N+1 leak. Add the preload method and verify the query count in your logs to reclaim your database performance.