Skip to content

Commit aa020d6

Browse files
TanayK07TanayK07
committed
feat(core): auto-create indexes on foreign key columns
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 #5042
1 parent cae4c9c commit aa020d6

5 files changed

Lines changed: 116 additions & 16 deletions

File tree

‎packages/core/src/associations/belongs-to.ts‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -201,6 +201,12 @@ export class BelongsToAssociation<
201201
newForeignKeyAttribute.onUpdate ??= newForeignKeyAttribute.onUpdate ?? 'CASCADE';
202202
}
203203

204+
// Auto-index foreign key columns for query performance (#5042).
205+
// Users can opt out per-association via foreignKey: { index: false }.
206+
if (newForeignKeyAttribute.index === undefined) {
207+
newForeignKeyAttribute.index = true;
208+
}
209+
204210
this.source.mergeAttributesDefault({
205211
[this.foreignKey]: newForeignKeyAttribute,
206212
});

‎packages/core/test/unit/associations/belongs-to-many.test.ts‎

Lines changed: 36 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -103,6 +103,29 @@ describe(getTestDialectTeaser('belongsToMany'), () => {
103103
expect(AB.options.validate).to.deep.equal({});
104104
});
105105

106+
it('auto-creates indexes on join table FK columns', () => {
107+
const User = sequelize.define('User');
108+
const Project = sequelize.define('Project');
109+
110+
User.belongsToMany(Project, { through: 'UserProject' });
111+
Project.belongsToMany(User, { through: 'UserProject' });
112+
113+
const ThroughModel = sequelize.models.getOrThrow('UserProject');
114+
const indexes = ThroughModel.modelDefinition.getIndexes();
115+
116+
const userFkIndex = indexes.find(idx =>
117+
idx.fields?.some(f => (typeof f === 'string' ? f : 'name' in f ? f.name : null) === 'userId'),
118+
);
119+
const projectFkIndex = indexes.find(idx =>
120+
idx.fields?.some(
121+
f => (typeof f === 'string' ? f : 'name' in f ? f.name : null) === 'projectId',
122+
),
123+
);
124+
125+
expect(userFkIndex).to.not.be.undefined;
126+
expect(projectFkIndex).to.not.be.undefined;
127+
});
128+
106129
it('should not override custom methods with association mixin', () => {
107130
const methods = {
108131
getTasks: 'get',
@@ -1243,14 +1266,13 @@ describe(getTestDialectTeaser('belongsToMany'), () => {
12431266
].sort(),
12441267
);
12451268

1246-
expect(Through.getIndexes()).to.deep.equal([
1247-
{
1248-
name: 'table_user_group_with_very_long_name_id_group_very_long_field_id_user_very_long_field_unique',
1249-
unique: true,
1250-
fields: ['id_user_very_long_field', 'id_group_very_long_field'],
1251-
column: 'id_user_very_long_field',
1252-
},
1253-
]);
1269+
const indexes = Through.getIndexes();
1270+
expect(indexes).to.deep.include({
1271+
name: 'table_user_group_with_very_long_name_id_group_very_long_field_id_user_very_long_field_unique',
1272+
unique: true,
1273+
fields: ['id_user_very_long_field', 'id_group_very_long_field'],
1274+
column: 'id_user_very_long_field',
1275+
});
12541276

12551277
// @ts-expect-error -- this property does not exist after normalization
12561278
expect(Through.getAttributes().id_user_very_long_field.unique).to.be.undefined;
@@ -1297,14 +1319,12 @@ describe(getTestDialectTeaser('belongsToMany'), () => {
12971319
expect(MyUsers.through.model === UserGroup);
12981320
expect(MyGroups.through.model === UserGroup);
12991321

1300-
expect(UserGroup.getIndexes()).to.deep.equal([
1301-
{
1302-
name: 'custom_user_group_unique',
1303-
unique: true,
1304-
fields: ['id_user_very_long_field', 'id_group_very_long_field'],
1305-
column: 'id_user_very_long_field',
1306-
},
1307-
]);
1322+
expect(UserGroup.getIndexes()).to.deep.include({
1323+
name: 'custom_user_group_unique',
1324+
unique: true,
1325+
fields: ['id_user_very_long_field', 'id_group_very_long_field'],
1326+
column: 'id_user_very_long_field',
1327+
});
13081328

13091329
// @ts-expect-error -- this property does not exist after normalization
13101330
expect(UserGroup.getAttributes().id_user_very_long_field.unique).to.be.undefined;

‎packages/core/test/unit/associations/belongs-to.test.ts‎

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -179,6 +179,54 @@ describe(getTestDialectTeaser('belongsTo'), () => {
179179
expect(Log.getAttributes().singularId).to.exist;
180180
});
181181

182+
describe('foreign key auto-indexing', () => {
183+
it('auto-creates an index on the foreign key column by default', () => {
184+
const User = sequelize.define('User');
185+
const Task = sequelize.define('Task');
186+
187+
Task.belongsTo(User);
188+
189+
const indexes = Task.modelDefinition.getIndexes();
190+
const fkIndex = indexes.find(idx =>
191+
idx.fields?.some(
192+
f => (typeof f === 'string' ? f : 'name' in f ? f.name : null) === 'userId',
193+
),
194+
);
195+
expect(fkIndex).to.not.be.undefined;
196+
});
197+
198+
it('does not override explicit foreignKey index: false', () => {
199+
const User = sequelize.define('User');
200+
const Task = sequelize.define('Task');
201+
202+
Task.belongsTo(User, { foreignKey: { name: 'userId', index: false } });
203+
204+
const indexes = Task.modelDefinition.getIndexes();
205+
const fkIndex = indexes.find(idx =>
206+
idx.fields?.some(
207+
f => (typeof f === 'string' ? f : 'name' in f ? f.name : null) === 'userId',
208+
),
209+
);
210+
expect(fkIndex).to.be.undefined;
211+
});
212+
213+
it('preserves custom index options on foreignKey', () => {
214+
const User = sequelize.define('User');
215+
const Task = sequelize.define('Task');
216+
217+
Task.belongsTo(User, { foreignKey: { name: 'userId', index: { unique: true } } });
218+
219+
const indexes = Task.modelDefinition.getIndexes();
220+
const fkIndex = indexes.find(idx =>
221+
idx.fields?.some(
222+
f => (typeof f === 'string' ? f : 'name' in f ? f.name : null) === 'userId',
223+
),
224+
);
225+
expect(fkIndex).to.not.be.undefined;
226+
expect(fkIndex?.unique).to.be.true;
227+
});
228+
});
229+
182230
describe('association hooks', () => {
183231
let Projects: ModelStatic<any>;
184232
let Tasks: ModelStatic<any>;

‎packages/core/test/unit/associations/has-many.test.ts‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,19 @@ describe(getTestDialectTeaser('hasMany'), () => {
6464
expect(Object.keys(User.associations)).to.deep.eq(['tasks']);
6565
});
6666

67+
it('auto-creates an index on the foreign key on the target model', () => {
68+
const User = sequelize.define('User');
69+
const Task = sequelize.define('Task');
70+
71+
User.hasMany(Task);
72+
73+
const indexes = Task.modelDefinition.getIndexes();
74+
const fkIndex = indexes.find(idx =>
75+
idx.fields?.some(f => (typeof f === 'string' ? f : 'name' in f ? f.name : null) === 'userId'),
76+
);
77+
expect(fkIndex).to.not.be.undefined;
78+
});
79+
6780
describe('optimizations using bulk create, destroy and update', () => {
6881
const vars = beforeAll2(() => {
6982
class User extends Model<InferAttributes<User>> {

‎packages/core/test/unit/associations/has-one.test.ts‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,6 +76,19 @@ describe(getTestDialectTeaser('hasOne'), () => {
7676
expect(Object.keys(User.associations)).to.deep.eq(['task']);
7777
});
7878

79+
it('auto-creates an index on the foreign key on the target model', () => {
80+
const User = sequelize.define('User');
81+
const Task = sequelize.define('Task');
82+
83+
User.hasOne(Task);
84+
85+
const indexes = Task.modelDefinition.getIndexes();
86+
const fkIndex = indexes.find(idx =>
87+
idx.fields?.some(f => (typeof f === 'string' ? f : 'name' in f ? f.name : null) === 'userId'),
88+
);
89+
expect(fkIndex).to.not.be.undefined;
90+
});
91+
7992
it('does not use `as` option to generate foreign key name', () => {
8093
// See HasOne.inferForeignKey for explanations as to why "as" is not used when inferring the foreign key.
8194
const User = sequelize.define('User', { username: DataTypes.STRING });

0 commit comments

Comments
 (0)