Convert SQL Junction Tables to Sequelize belongsToMany

Many-to-Many Relationships in Sequelize

A many-to-many relationship in SQL uses a junction table (also called a join table or pivot table) with foreign keys to both related tables and often a composite primary key. The converter detects this pattern and generates the appropriate models.

SQL Junction Table Pattern

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL
);

CREATE TABLE student_courses (
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id INTEGER NOT NULL REFERENCES courses(id),
  enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, course_id)
);

Generated Models

The converter generates a model for the junction table with both foreign keys:

StudentCourse.init({
  student_id: {
    type: DataTypes.INTEGER, allowNull: false,
    references: { model: 'students', key: 'id' },
  },
  course_id: {
    type: DataTypes.INTEGER, allowNull: false,
    references: { model: 'courses', key: 'id' },
  },
  enrolled_at: { type: DataTypes.DATE, defaultValue: DataTypes.NOW },
}, {
  sequelize,
  indexes: [{ fields: ['student_id', 'course_id'], unique: true, name: 'PRIMARY' }],
});

Setting Up belongsToMany

After the models are generated, wire up the many-to-many association:

Student.belongsToMany(Course, {
  through: StudentCourse,
  foreignKey: 'student_id',
  otherKey: 'course_id',
});

Course.belongsToMany(Student, {
  through: StudentCourse,
  foreignKey: 'course_id',
  otherKey: 'student_id',
});

Extra Columns on the Junction Table

Unlike simple many-to-many relationships, the junction table often has extra columns (like enrolled_at above). Sequelize's through model preserves these, and you can access them via instance.StudentCourse.enrolled_at on the joined result.

Composite Primary Key

The composite primary key PRIMARY KEY (student_id, course_id) is converted to a unique index entry in the Sequelize model's indexes array, ensuring uniqueness of the combination at the database level.