Diagnosing N+1 Queries in AdonisJS Lucid with .load() Eager Loading

Your AdonisJS API endpoint returns correct data but response time grows linearly with the number of rows. The query log shows one SELECT for posts followed by dozens of identical SELECTs for authors. That is the recognizable N+1 pattern in Lucid ORM and the condition where .load() eager loading is the intended fix.

Useful takeaway: in AdonisJS v5+ Lucid, .load() performs batch eager loading after the initial query. If the related data is still fetched one-by-one, the load call is missing, mis-targeted, or applied to the wrong version of Lucid.

Recognizable condition

Symptoms you can see in production or local development:

• Endpoint latency increases with page size, e.g. 20 posts ~200ms, 100 posts ~1.2s.
• Database query log shows a base query then repeated SELECTs with different ids for the same related table.
• Model instances have undefined relations until accessed, triggering lazy loads.

Cause and diagnostic table

Version assumption

Lucid .load() was introduced in AdonisJS v5. AdonisJS 4.x uses explicit preload patterns and does not have .load() on the query builder. Verify your stack before applying fixes. Run in project root with read permission:

cat package.json | grep -E '\\\\\"@adonisjs/'

Expected check: adonisjs version 5.x or 6.x indicates .load() support. If 4.x is present, eager loading requires a different approach and escalation is needed.

Ordered checks

1. Confirm query count baseline

Enable query logging in config/database.ts for the connection you use. With logging on, execute the endpoint once and capture the log output. A healthy eager load should show one base query plus one or more batch SELECTs for related tables, not one per parent.

Risk: logging adds overhead; disable in production.

2. Inspect model instance relations

After fetching, inspect the in-memory model:

// start/routes.ts or a controller action
const posts = await Post.query().where('published', true).limit(20)
await posts.load('author')

console.log(Object.keys(posts[0].$relations || {}))

Expected check: 'author' appears in the keys. If missing, .load() did not run or the relation name is incorrect.

3. Validate nested paths

For deep graphs use dot-notation:

await posts.load('author.books', (query) => {
  query.where('published', true)
})

Check that both 'author' and 'author.books' are present in $relations. A typo in the path silently results in no load.

4. Verify batching behavior

Lucid batches ids from the initial result set into a single SELECT with WHERE IN. Check the query log for a SELECT on the related table with an IN clause containing multiple ids. If you see separate SELECTs per id, lazy loading is still occurring.

Fixes tied to findings

If .load() is missing: move the load before serialization. Loading after the response is sent has no effect.

const posts = await Post.query().where('published', true).limit(20)
await posts.load('author')
return posts

If relation name is wrong: confirm the relation method name on the model. Relation names are case-sensitive and must match the method defined on the model.

If nested data is missing: use dot-notation for each level you need. Each dot segment adds a batch SELECT, not a join.

If you are on AdonisJS 4.x: .load() is unavailable. The supported pattern is explicit preload via the model’s preloader or manual query. This requires code changes and is an escalation case.

Limitations and practical verification

.load() issues a secondary SELECT per relation level. It avoids N+1 but can still transfer large data sets. Do not load unbounded collections in list endpoints.

Practical verification steps:

• Run the endpoint with a small limit and count logged queries before and after the .load() change.
• Inspect a sample model instance .relations to confirm the related records are present without triggering additional queries.
• Compare response payload size to ensure you are not over-fetching columns.

Escalation criteria

Escalate to architecture review if:

• Query count remains high after correct .load() usage, indicating a missing index on the foreign key.
• You need to eager load more than three levels deep or multiple large collections, which may require a dedicated read model or selective field projection.
• The project is AdonisJS 4.x and a migration to v5+ is being considered for Lucid API parity.