feat(core): auto-create indexes on foreign key columns#18146
feat(core): auto-create indexes on foreign key columns#18146TanayK07 wants to merge 6 commits intosequelize:mainfrom
Conversation
Automatically adds an index on foreign key columns created by
associations (belongsTo, hasOne, hasMany, belongsToMany). This improves
JOIN and lookup performance, especially on databases like PostgreSQL
that do not auto-index FK columns.
Users can opt out per-association via foreignKey: { index: false }.
Closes sequelize#5042
📝 WalkthroughWalkthroughAdds automatic indexing for foreign key attributes in associations (BelongsTo, HasOne, HasMany, BelongsToMany) by default unless disabled; prevents creating per-attribute indexes for primary key attributes; and updates unit tests to assert FK auto-index behavior and relax exact index-array equality checks. Changes
Sequence Diagram(s)sequenceDiagram
participant Dev as Developer
participant Assoc as Association (BelongsTo/HasOne/HasMany)
participant ModelDef as ModelDefinition
participant DBSchema as Schema/Indexes
Dev->>Assoc: define association (e.g., Task.belongsTo(User))
Assoc->>ModelDef: ensure foreignKey attribute\n(if no index specified -> set index: true)
ModelDef->>ModelDef: normalize attributes (skip index for primaryKey)
ModelDef->>DBSchema: register attribute + index metadata
DBSchema-->>Dev: indexes will be created on sync/migration
Estimated code review effort🎯 3 (Moderate) | ⏱️ ~25 minutes Poem
🚥 Pre-merge checks | ✅ 5✅ Passed checks (5 passed)
✏️ Tip: You can configure your own custom pre-merge checks in the settings. ✨ Finishing Touches🧪 Generate unit tests (beta)
📝 Coding Plan
Warning There were issues while running some tools. Please review the errors and either fix the tool's configuration or disable the tool if it's a critical failure. 🔧 OpenGrep (1.16.4)packages/core/test/unit/associations/belongs-to-many.test.ts┌──────────────┐ �[32m✔�[39m �[1mOpengrep OSS�[0m �[1m Loading rules from local config...�[0m Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out. Comment Tip You can disable poems in the walkthrough.Disable the |
Primary key columns are already indexed by all databases. When belongsToMany sets FK columns as the composite PK (via #makeFkPairUnique), the auto-index feature would create a duplicate index, causing Oracle ORA-01408 errors. Skip index creation in refreshAttributes() when the attribute is already a primary key.
There was a problem hiding this comment.
Actionable comments posted: 1
🤖 Prompt for all review comments with AI agents
Verify each finding against the current code and only fix it if needed.
Inline comments:
In `@packages/core/test/unit/associations/belongs-to-many.test.ts`:
- Around line 122-134: The test is matching any index that mentions
userId/projectId so it can be fooled by a composite index; tighten the
predicates in the UserProject.modelDefinition.getIndexes() checks (userFkIndex
and projectFkIndex) to only match indexes whose fields array has length === 1
and whose single field equals the exact FK name (handle both string entries and
field objects by checking string equality or object.name === 'userId' /
'projectId'); update both userFkIndex and projectFkIndex predicates accordingly
so they only pass for single-column FK indexes.
ℹ️ Review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro
Run ID: 774ab48c-80f7-489f-b643-f06b73358d64
📒 Files selected for processing (2)
packages/core/src/model-definition.tspackages/core/test/unit/associations/belongs-to-many.test.ts
…column non-unique indexes The previous assertions used `find` with `some`, which could match the composite unique index that belongsToMany already creates, leading to false-positive test results. Now we explicitly check for single-column, non-unique indexes only.
There was a problem hiding this comment.
🧹 Nitpick comments (1)
packages/core/test/unit/associations/belongs-to-many.test.ts (1)
124-133: Consider extracting the shared helper function.The
hasSingleColumnFkIndexhelper is duplicated in both new tests (lines 124-133 and 151-160). Consider extracting it to a shared utility within thedescribeblock or test support module to reduce duplication.// At the top of the describe block: const hasSingleColumnFkIndex = (indexes: IndexOptions[], fieldName: string) => indexes.some(idx => { if (idx.fields?.length !== 1 || idx.unique === true) { return false; } const [field] = idx.fields; return (typeof field === 'string' ? field : 'name' in field ? field.name : null) === fieldName; });🤖 Prompt for AI Agents
Verify each finding against the current code and only fix it if needed. In `@packages/core/test/unit/associations/belongs-to-many.test.ts` around lines 124 - 133, The test suite duplicates the helper logic for checking single-column FK indexes; extract the hasSingleColumnFkIndex helper into a shared location and update both call sites to use it: create a single function (e.g., const hasSingleColumnFkIndex = (indexes: IndexOptions[], fieldName: string) => ...) at the top of the describe block or a test-support module, then replace the inline copies in the tests that currently reference indexes.some(...) with calls to hasSingleColumnFkIndex(indexes, 'fieldName'); ensure the helper signature and imports (IndexOptions if needed) match the existing tests so both tests use the same function.
🤖 Prompt for all review comments with AI agents
Verify each finding against the current code and only fix it if needed.
Nitpick comments:
In `@packages/core/test/unit/associations/belongs-to-many.test.ts`:
- Around line 124-133: The test suite duplicates the helper logic for checking
single-column FK indexes; extract the hasSingleColumnFkIndex helper into a
shared location and update both call sites to use it: create a single function
(e.g., const hasSingleColumnFkIndex = (indexes: IndexOptions[], fieldName:
string) => ...) at the top of the describe block or a test-support module, then
replace the inline copies in the tests that currently reference
indexes.some(...) with calls to hasSingleColumnFkIndex(indexes, 'fieldName');
ensure the helper signature and imports (IndexOptions if needed) match the
existing tests so both tests use the same function.
ℹ️ Review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro
Run ID: e1611775-092b-4aa8-bb93-b665755854b2
📒 Files selected for processing (1)
packages/core/test/unit/associations/belongs-to-many.test.ts
Please remove this part. I have not been a maintainer of Sequelize for a very long time. |
|
@mickhansen Apologies about that — I've updated the PR description to remove the attribution. Thanks for flagging it! |
There was a problem hiding this comment.
🧹 Nitpick comments (1)
packages/core/test/unit/associations/belongs-to-many.test.ts (1)
141-168: Consider extracting the shared helper function.The
hasSingleColumnFkIndexhelper is duplicated between this test and the previous one (lines 124-135 and 153-164). Consider extracting it to a shared scope within this describe block or to the test support utilities for reuse.♻️ Proposed refactor to extract shared helper
+ const hasSingleColumnFkIndex = (indexes: ReturnType<typeof Model.prototype.modelDefinition.getIndexes>, fieldName: string) => + indexes.some(idx => { + if (idx.fields?.length !== 1 || idx.unique === true) { + return false; + } + + const [field] = idx.fields; + + return ( + (typeof field === 'string' ? field : 'name' in field ? field.name : null) === fieldName + ); + }); + it('auto-creates indexes on join table FK columns when they are not part of the PK', () => { // ... model definitions ... const indexes = UserProject.modelDefinition.getIndexes(); - const hasSingleColumnFkIndex = (fieldName: string) => - indexes.some(idx => { - // ... implementation ... - }); - - expect(hasSingleColumnFkIndex('userId')).to.be.true; + expect(hasSingleColumnFkIndex(indexes, 'userId')).to.be.true; // ... etc });🤖 Prompt for AI Agents
Verify each finding against the current code and only fix it if needed. In `@packages/core/test/unit/associations/belongs-to-many.test.ts` around lines 141 - 168, Extract the duplicated hasSingleColumnFkIndex helper into a shared scope so both tests reuse it: move the function (the predicate that checks indexes on ThroughModel.modelDefinition.getIndexes()) out of the two individual it blocks and place it either at the top of the describe block wrapping these tests or into a test utility module; update the tests to call the shared hasSingleColumnFkIndex helper and keep all references to ThroughModel, sequelize.models.getOrThrow and getIndexes() unchanged so the logic and identifiers remain identical.
🤖 Prompt for all review comments with AI agents
Verify each finding against the current code and only fix it if needed.
Nitpick comments:
In `@packages/core/test/unit/associations/belongs-to-many.test.ts`:
- Around line 141-168: Extract the duplicated hasSingleColumnFkIndex helper into
a shared scope so both tests reuse it: move the function (the predicate that
checks indexes on ThroughModel.modelDefinition.getIndexes()) out of the two
individual it blocks and place it either at the top of the describe block
wrapping these tests or into a test utility module; update the tests to call the
shared hasSingleColumnFkIndex helper and keep all references to ThroughModel,
sequelize.models.getOrThrow and getIndexes() unchanged so the logic and
identifiers remain identical.
ℹ️ Review info
⚙️ Run configuration
Configuration used: defaults
Review profile: CHILL
Plan: Pro
Run ID: 444cd8e6-4783-4510-a638-4de5f21aca12
📒 Files selected for processing (1)
packages/core/test/unit/associations/belongs-to-many.test.ts
Summary
belongsTo,hasOne,hasMany,belongsToMany)foreignKey: { index: false }, or pass custom index options viaforeignKey: { index: { unique: true } }Closes #5042
Motivation
This is a long-standing request (since 2015, 9 thumbs up). Most databases do not automatically create indexes on FK columns, leading to degraded query performance on JOINs and reverse lookups. This was explicitly requested for the next major version.
Implementation
The change is minimal (~3 lines in
belongs-to.ts). Before callingmergeAttributesDefault(), we setindex: trueon the FK attribute if the user hasn't explicitly configured theindexproperty. This leverages the existing attribute-level index infrastructure inmodel-definition.ts.Since
hasOneandhasManyinternally delegate FK creation toBelongsToAssociation, andbelongsToManycreates internalHasManyassociations, all association types are covered by this single change point.Test plan
belongsTo: auto-creates index on FK column by defaultbelongsTo: does not override explicitforeignKey: { index: false }belongsTo: preserves custom index options (foreignKey: { index: { unique: true } })hasMany: auto-creates index on FK on target modelhasOne: auto-creates index on FK on target modelbelongsToMany: auto-creates indexes on both FK columns in join tabledeep.includeinstead ofdeep.equal)Summary by CodeRabbit
New Features
Bug Fixes / Behavior Change