Skip to content

feat(core): auto-create indexes on foreign key columns#18146

Open
TanayK07 wants to merge 6 commits intosequelize:mainfrom
TanayK07:feat/auto-index-foreign-keys
Open

feat(core): auto-create indexes on foreign key columns#18146
TanayK07 wants to merge 6 commits intosequelize:mainfrom
TanayK07:feat/auto-index-foreign-keys

Conversation

@TanayK07
Copy link
Copy Markdown

@TanayK07 TanayK07 commented Mar 9, 2026

Summary

  • Automatically creates an index on foreign key columns generated by associations (belongsTo, hasOne, hasMany, belongsToMany)
  • 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 }, or pass custom index options via foreignKey: { 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 calling mergeAttributesDefault(), we set index: true on the FK attribute if the user hasn't explicitly configured the index property. This leverages the existing attribute-level index infrastructure in model-definition.ts.

Since hasOne and hasMany internally delegate FK creation to BelongsToAssociation, and belongsToMany creates internal HasMany associations, all association types are covered by this single change point.

Test plan

  • belongsTo: auto-creates index on FK column by default
  • belongsTo: does not override explicit foreignKey: { index: false }
  • belongsTo: preserves custom index options (foreignKey: { index: { unique: true } })
  • hasMany: auto-creates index on FK on target model
  • hasOne: auto-creates index on FK on target model
  • belongsToMany: auto-creates indexes on both FK columns in join table
  • Updated 2 existing tests that asserted exact index arrays (now use deep.include instead of deep.equal)
  • All 2339 unit tests pass, 0 failing

Summary by CodeRabbit

  • New Features

    • Foreign key columns in associations now auto-create database indexes (BelongsTo, HasMany, HasOne, BelongsToMany).
    • Automatic indexing can be disabled per foreign key via foreignKey: { index: false }.
    • Custom index options on foreign keys (e.g., unique) are preserved.
  • Bug Fixes / Behavior Change

    • Primary key attributes no longer receive automatic per-attribute indexes.
    • Join-table FK columns that are part of a primary key no longer get duplicate single-column indexes.

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
@TanayK07 TanayK07 requested a review from a team as a code owner March 9, 2026 10:10
@TanayK07 TanayK07 requested review from WikiRik and sdepold March 9, 2026 10:10
@coderabbitai
Copy link
Copy Markdown
Contributor

coderabbitai bot commented Mar 9, 2026

📝 Walkthrough

Walkthrough

Adds 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

Cohort / File(s) Summary
BelongsTo association
packages/core/src/associations/belongs-to.ts
Defaults foreignKey.index to true when unspecified, applying the index option before merging the FK attribute into the source model.
Attribute normalization
packages/core/src/model-definition.ts
Skips processing per-attribute index definitions for attributes marked primaryKey, preventing automatic single-column indexes for PKs.
BelongsTo tests
packages/core/test/unit/associations/belongs-to.test.ts
Adds "foreign key auto-indexing" tests: default index creation, opt-out via foreignKey: { index: false }, and preserving custom index options (e.g., unique).
HasMany / HasOne tests
packages/core/test/unit/associations/has-many.test.ts, packages/core/test/unit/associations/has-one.test.ts
Adds tests asserting automatic FK index creation on target models when hasMany/hasOne associations are defined.
BelongsToMany tests
packages/core/test/unit/associations/belongs-to-many.test.ts
Adds tests verifying join-table FK single-column indexes are auto-created when FKs are not part of the PK, ensures no duplicate single-column indexes when FKs are PKs, and relaxes exact index-array assertions to deep.include checks for specific index entries.

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
Loading

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~25 minutes

Poem

🐰 I hopped through fields of foreign keys,

set tiny indexes like tiny pegs,
no duplicates where PKs hold sway,
tests snugly check the hops I made—hooray! 🥕

🚥 Pre-merge checks | ✅ 5
✅ Passed checks (5 passed)
Check name Status Explanation
Description Check ✅ Passed Check skipped - CodeRabbit’s high-level summary is enabled.
Title check ✅ Passed The title 'feat(core): auto-create indexes on foreign key columns' accurately and concisely describes the main change in this pull request, matching the primary objective of automatically creating indexes on foreign key columns.
Linked Issues check ✅ Passed The PR successfully implements the core requirement from issue #5042: auto-creating indexes on foreign key columns for improved JOIN and lookup performance, with opt-out capability via index: false or custom index options.
Out of Scope Changes check ✅ Passed All changes are directly related to the stated objective of auto-creating foreign key indexes. Modifications span BelongsTo association logic, model-definition processing, and comprehensive test coverage for all association types, with no unrelated changes detected.
Docstring Coverage ✅ Passed No functions found in the changed files to evaluate docstring coverage. Skipping docstring coverage check.

✏️ Tip: You can configure your own custom pre-merge checks in the settings.

✨ Finishing Touches
🧪 Generate unit tests (beta)
  • Create PR with unit tests
📝 Coding Plan
  • Generate coding plan for human review comments

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

┌──────────────┐
│ Opengrep CLI │
└──────────────┘

�[32m✔�[39m �[1mOpengrep OSS�[0m
�[32m✔�[39m Basic security coverage for first-party code vulnerabilities.

�[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.

❤️ Share

Comment @coderabbitai help to get the list of available commands and usage tips.

Tip

You can disable poems in the walkthrough.

Disable the reviews.poem setting to disable the poems in the walkthrough.

TanayK07 and others added 2 commits March 10, 2026 21:20
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.
Copy link
Copy Markdown
Contributor

@coderabbitai coderabbitai bot left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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

📥 Commits

Reviewing files that changed from the base of the PR and between aa020d6 and 4cfe2d4.

📒 Files selected for processing (2)
  • packages/core/src/model-definition.ts
  • packages/core/test/unit/associations/belongs-to-many.test.ts

Comment thread packages/core/test/unit/associations/belongs-to-many.test.ts Outdated
…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.
Copy link
Copy Markdown
Contributor

@coderabbitai coderabbitai bot left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

🧹 Nitpick comments (1)
packages/core/test/unit/associations/belongs-to-many.test.ts (1)

124-133: Consider extracting the shared helper function.

The hasSingleColumnFkIndex helper is duplicated in both new tests (lines 124-133 and 151-160). Consider extracting it to a shared utility within the describe block 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

📥 Commits

Reviewing files that changed from the base of the PR and between 4cfe2d4 and b2c4153.

📒 Files selected for processing (1)
  • packages/core/test/unit/associations/belongs-to-many.test.ts

@mickhansen
Copy link
Copy Markdown
Contributor

A core maintainer (@mickhansen) explicitly requested this for the next major version.

Please remove this part. I have not been a maintainer of Sequelize for a very long time.

@TanayK07
Copy link
Copy Markdown
Author

@mickhansen Apologies about that — I've updated the PR description to remove the attribution. Thanks for flagging it!

Copy link
Copy Markdown
Contributor

@coderabbitai coderabbitai bot left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

🧹 Nitpick comments (1)
packages/core/test/unit/associations/belongs-to-many.test.ts (1)

141-168: Consider extracting the shared helper function.

The hasSingleColumnFkIndex helper 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

📥 Commits

Reviewing files that changed from the base of the PR and between b2c4153 and d075f91.

📒 Files selected for processing (1)
  • packages/core/test/unit/associations/belongs-to-many.test.ts

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

Option to auto create foreign key indexes

2 participants