sequelize/sequelize

Option to auto create foreign key indexes

Open

#5042 opened on Dec 14, 2015

View on GitHub
 (11 comments) (9 reactions) (0 assignees)TypeScript (29,527 stars) (4,271 forks)batch import
existing workaroundgood first issuestatus: understood

Description

If you create a foreign key constraint no further index will be auto created by postgres (I'm not sure what other DBs will do, I did not check), since there are a couple of different ways to create a index in that case.

But I'd say the average use case would be improved if sequelize would just add a normal btree index on the foreign key if you pass a option to the global sequelize instance. That way new tables would always benefit from that and you do not always have to think about it.

The performance difference is significant in that case.

So I'd say it would be a nice feature to add.

Contributor guide

Tech stack
typescriptjavascriptsqlpostgresql
Domain
backenddatabase
Issue type
feature
DifficultyEstimated implementation difficulty for a new contributor, from 1 for very small changes to 5 for expert-level work.
3
Estimated timeA rough time range for an experienced contributor to investigate, implement, test, and prepare a pull request.
3-5 days
Activity statusHow available the issue appears right now: fresh, active, stale, blocked, or waiting on maintainer input.
fresh
ClarityHow clearly the issue explains the expected change, acceptance criteria, and next step.
clear
Prerequisites
Knowledge of Sequelize internalsUnderstanding of database indexesFamiliarity with Sequelize configuration
Newbie friendlinessA 1-100 score estimating how approachable this issue is for first-time contributors.
30
Research direction
Examine the Sequelize source code to understand how foreign key constraints are created. Look at the model definition and dialect specific files (e.g., lib/dialects/postgres/connection manager.js) to identify where indexes could be automatically added. Review the global configuration options to determine how to add a new flag. Consider the discussion in the issue comments for alternative approaches and potential pitfalls.