Eager Loading Relations with Pagination in AdonisJS Lucid

Problem: N+1 Queries and Pagination Meta Mismatch

The practical takeaway: always chain preload before paginate to keep related data fetching within a single query budget and avoid N+1 patterns in AdonisJS resource endpoints.

When related data is needed alongside paginated lists, the order of method calls determines whether Lucid issues one JOIN query or many separate queries.

Smallest Suitable Design

The most reliable pattern chains preload on the query builder before paginate:

User.query().preload('posts').paginate(page, perPage)

This generates a single SELECT with a LEFT JOIN on the posts table, loading the relation within the same query budget as the pagination meta. The resulting meta object correctly reflects total and perPage without extra query execution.

Why the order matters

When preload is applied after paginate, Lucid issues a second query to fetch the relation for each page of results. This bypasses the pagination limit, can exceed rate limits, and may return more records than perPage if not filtered client-side. The research brief confirms that preloading before pagination keeps the entire operation within one query budget.

Trust and Data Boundaries

Preloaded relation payloads are subject to the target model's fillable guards. If a relation's associated model defines overly permissive fillable attributes, a preloaded payload can be mass-assigned unexpectedly when the framework hydrates the model instance. Always verify that fillable lists are explicit and avoid using @all or wildcard patterns in production endpoints.

Example: A User model with fillable: ['name', 'email'] will reject username during preload hydration, but a model with fillable: ['*'] will accept any key present in the preloaded JSON.

Operational Checks

1. Enable query logging via the adonisjs/core query provider. In config/database.js, set logQueries: true or use Db.beginGlobalQueryLog().
2. Start the application and make a request to an endpoint using User.query().preload('posts').paginate(1, 10).
3. Inspect the console or log output. Confirm the SQL contains a single SELECT with a LEFT JOIN on the posts table, and that the meta object in the response includes total and perPage matching the expected pagination bounds.

If you see multiple SELECT statements or a COUNT query after the data query, the ordering or configuration needs adjustment.

Failure Modes and Version Considerations

AdonisJS v5 restructured Lucid's query builder. The preload method and its interaction with .paginate() may differ from v4. Teams upgrading from v4 should run targeted integration tests comparing query counts and pagination meta before and after the upgrade.

Overly broad preloading—inclusive of large hasMany or belongsToMany collections—increases response size and can push payloads past API rate limits or client-side parsing constraints. Scope preload to the minimal relation set required for the endpoint.

Concurrent request handling with shared preload configurations can lead to unexpected relation caching or stale data if the ORM context isn't isolated per request. Always preload within the request lifecycle, not at the application bootstrap level.

Checking the Result Practically

Write a minimal route test that asserts the expected behavior:

// adonis route handler example
import User from '@ioc:Adonis/Lucid/Orm'

async function index({ request, response }) {
  const page = request.input('page', 1)
  const perPage = request.input('perPage', 10)

  const users = await User.query()
    .preload('posts')
    .paginate(page, perPage)

  return response.ok({
    data: users.serialize(),
    meta: {
      total: users.meta.total,
      perPage: users.meta.perPage,
      currentPage: users.meta.currentPage,
      lastPage: users.meta.lastPage
    }
  })
}

Run the request and check that meta.total equals the total user count, meta.perPage is 10, and only one query pair (SELECT ... JOIN + SELECT COUNT) was executed. If additional queries appear, reorder the chain or narrow the preload scope.

Limitations

This pattern assumes the relation is defined and queryable via Lucid's query builder. Relations involving complex scopes, where clauses, or polymorphic types may require additional syntax. Payload size growth from preloading is a hard limit; monitor response headers and client feedback to adjust the preload set or introduce selective field loading.