Sequelize limit behavior with one-to-many eager loading

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.