Sequelize Pagination: Limit and Distinct behavior during Eager Loading

Answer to the Core Question

Does distinct:true consistently ensure the limit applies to the main model's primary keys across all supported SQL dialects?

In practice, distinct:true is the mechanism Sequelize uses to keep limit and offset on the parent table, regardless of how many child rows are joined. With the default subQuery:true behaviour for a paginated include, Sequelize builds an inner query that selects unique parent primary keys with the limit/offset applied, then joins the associations in an outer query. The exact SQL is dialect-specific: PostgreSQL can use DISTINCT ON ("Parent"."id"), while MySQL, SQLite, and MSSQL rely on equivalent subquery/DISTINCT constructs. In all cases the intent is the same: the outer query returns a unique set of parent rows before the pagination limits are applied.

However, the guarantee only holds when the query keeps its default shape. If you set subQuery:false, use separate:true, or add options that alter the query (custom attributes that trigger a GROUP BY, for example), the distinct logic can be bypassed or weakened, and the limit may end up applied to the joined row set instead.

Therefore:

• With default settings, distinct:true is the documented way to paginate parents reliably when including one-to-many associations.
• Dialect-level SQL differs, so confirm the generated statement on each dialect you support (MySQL's behaviour can also depend on server modes such as ONLY_FULL_GROUP_BY).
• When you change the query shape (subQuery:false, separate:true, custom attributes), re-verify; the guarantee no longer holds automatically.

What Happens When distinct Is Omitted?

Without distinct:true, Sequelize applies the limit to the raw joined result set. A parent with five children produces five joined rows, each consuming one slot of the limit. A page requesting limit: 10 can then contain fewer than ten distinct parents, and the count from findAndCountAll is inflated because it counts joined rows rather than unique parents.

Example: Parent.findAndCountAll({ limit: 2, include: [Child] }) against a dataset where each parent has three children can return just one parent (the first two joined rows) and a count of six (two parents × three children), which is misleading.

Practical Steps to Verify in Your Project

1. Enable logging: pass logging: console.log to the query (or set it on the instance) and inspect the generated SQL for DISTINCT, DISTINCT ON, GROUP BY, or a subquery wrapped around the primary key.
2. Run a test query: await Parent.findAndCountAll({ limit: 1, include: [Child], distinct: true }); — a parent with three children should come back as one parent with all three children.
3. Repeat without distinct:true and observe that the limit is applied to the joined rows, truncating the association.
4. Execute the same queries against each target dialect you support to confirm consistent parent-level pagination.

Recommendation

When paginating over one-to-many associations, always include distinct:true (and use findAndCountAll with the same flag if you need a total count that matches the pages). If you use subQuery:false for performance, check the generated SQL; the safest fallbacks are keeping distinct:true or switching to separate:true, which runs a separate query per association and avoids join inflation entirely, at the cost of single-query atomicity.

Missing Diagnostic Detail

Which SQL dialect are you targeting? That changes the verification steps — for example, MySQL with ONLY_FULL_GROUP_BY enabled behaves differently around GROUP BY/DISTINCT than PostgreSQL's DISTINCT ON.