Sequelize limit behavior with one-to-many eager loading

Question

When implementing pagination in Sequelize, the limit and offset options are used to control the number of records returned from the database. However, a specific behavior occurs when these options are combined with the include property for one-to-many associations.
Because Sequelize generates SQL JOINs for eager loading, the resulting row count includes the joined child records. This often causes the ORM to return fewer parent records than the value specified in the limit option, as the limit is applied to the total number of joined rows rather than the unique parent entities.
While the separate: true option is documented as a way to handle this by executing queries in distinct steps, it changes the execution pattern of the data retrieval.

Under what specific conditions does Sequelize automatically handle the join-induced limit truncation without requiring separate: true?
How does the performance overhead of separate: true compare to using a subquery for pagination on large datasets?

Tasadduq Burney · Answer

Handling Limit and Offset with Eager Loading
Sequelize automatically handles join-induced limit truncation by using a subquery. When you apply a limit to a query that includes a one-to-many association, Sequelize (by default) wraps the primary model selection in a subquery to determine the correct IDs before performing the JOIN. This ensures that the limit applies to the number of root entities rather than the total number of rows returned by the database.

Conditions for Automatic Handling
Sequelize triggers this subquery behavior automatically under the following conditions:

The limit or offset option is present in the query.
  An include is used for a one-to-many or many-to-many association.
  The subQuery option is not explicitly set to false.

If you set subQuery: false, Sequelize will execute a single flat JOIN. In this scenario, the limit is applied to the final result set, and if a parent has multiple children, the limit will be reached prematurely, returning fewer parent records than requested.

Performance: separate: true vs. Subqueries
Choosing between separate: true and the default subquery approach involves a trade-off between query complexity and the number of database round-trips.

Method
      Execution Pattern
      Performance Profile

Subquery (Default)
      One complex query with a nested SELECT.
      Higher CPU load on the DB for large datasets; single round-trip.

separate: true
      Two or more distinct queries (one for parents, one for children).
      Lower DB complexity per query; higher latency due to multiple round-trips.

On very large datasets, separate: true is often more performant because it avoids the overhead of massive JOIN tables and complex subquery optimization in the database engine. However, it prevents you from filtering the parent records based on values in the child table (since the child query is executed after the parent list is already fetched).

Verification Steps
To verify which mechanism is being used, enable SQL logging in your Sequelize instance and check the generated output:
const users = await User.findAll({
  limit: 10,
  include: [{ model: Post }],
  logging: console.log
});

Subquery: You will see SELECT ... FROM (SELECT ... LIMIT 10) AS ...
  Separate: You will see two distinct SELECT statements.

Diagnostic Detail: Are you applying a where clause to the include model? If so, separate: true cannot be used for filtering the root results, and you must rely on the subquery or a manual join.

Sequelize limit behavior with one-to-many eager loading

1 answer

Handling Limit and Offset with Eager Loading

Conditions for Automatic Handling

Performance: separate: true vs. Subqueries

Verification Steps

Your perspective belongs here.

1 question comment

Method	Execution Pattern	Performance Profile
Subquery (Default)	One complex query with a nested SELECT.	Higher CPU load on the DB for large datasets; single round-trip.
separate: true	Two or more distinct queries (one for parents, one for children).	Lower DB complexity per query; higher latency due to multiple round-trips.