Diagnosing Sequelize Eager‑Loading Pagination Duplicates

Recognizable condition

When you call Model.findAll({ include: [...], limit: N, offset: M }) on a Sequelize model that has a hasMany or belongsToMany association, the returned array may contain the same primary instance multiple times—once for each matching associated row.

Cause and quick diagnostic table

Check	What to look for
Generated SQL	JOIN between primary and associated table, with LIMIT/OFFSET placed after the JOIN.
Query without include	Duplicates disappear.
Association type	Duplicates only appear for multi‑valued associations (hasMany, belongsToMany).

Ordered checks

1. Enable Sequelize logging to see the exact SQL.
2. Inspect the SQL for a JOIN clause followed by LIMIT/OFFSET.
3. Run the same query without the include option; verify that each primary row appears once.
4. Confirm the association cardinality in your model definitions (hasMany or belongsToMany).

Fixes tied to findings

Use a subquery for pagination (subQuery: false)

In Sequelize v6 and later you can force the ORM to rewrite the query so that LIMIT/OFFSET is applied inside a subquery that selects only the primary model’s primary key.

const rows = await Model.findAll({
  include: [{ association: 'associationName' }],
  limit: 20,
  offset: 0,
  subQuery: false,   // forces subquery strategy
  logging: console.log   // optional: to verify generated SQL
});

Expected check: the logged SQL should contain something like SELECT * FROM (SELECT \"Model\"\.\"id\" ..., FROM \"Model\" LEFT JOIN \"Assoc\" ... LIMIT 20 OFFSET 0) AS \"Model\".

Deduplicate with distinct: true

If you prefer to keep the default join strategy, add distinct: true to collapse duplicate primary instances.

const rows = await Model.findAll({
  include: [{ association: 'associationName' }],
  limit: 20,
  offset: 0,
  distinct: true,
  logging: console.log
});

Note: this does not reduce the join work; the underlying query still returns the multiplied rows before deduplication.

Split into two queries

Fetch the primary keys paginated first, then eager‑load the associations in a second step.

// 1️⃣ paginate primary model
const primaryIds = await Model.findAll({
  attributes: ['id'],
  limit: 20,
  offset: 0,
  logging: console.log
});
const ids = primaryIds.map(r => r.id);

// 2️⃣ eager‑load associations for those ids
const rows = await Model.findAll({
  where: { id: ids },
  include: [{ association: 'associationName' }],
  logging: console.log
});

This guarantees one row per primary instance but incurs an extra round‑trip.

Limitations and verification

• subQuery: false may produce more complex SQL; test with your data volume and index the join columns.
• distinct: true removes duplicates but does not curb the join multiplication, so query time can still grow with many associated rows.
• Split queries add latency; consider caching the primary‑key list if the page is static.

Practical way to check the result

1. After applying a fix, keep logging enabled and confirm the generated SQL matches the expected pattern (subquery, distinct, or two separate statements).
2. Using a known test set where at least one primary record has two or more associated rows, verify that each primary instance appears exactly once per page.
3. Compare response times before and after the change; if degradation is unacceptable, consider adding indexes on the foreign‑key columns used in the join.

Escalation criteria

• Duplicates persist after applying distinct: true or subQuery: false.
• Query execution time exceeds your service‑level threshold after the fix.
• The generated SQL becomes excessively large or causes planner timeouts.

In these cases, revisit the association design (e.g., add filters to the include, use scoped associations, or redesign the pagination strategy).