Belongs-To-Many associations

Belongs-To-Many associations are used to connect sources with multiple targets. Furthermore the targets can also have connections to multiple sources.

  1. Project.belongsToMany(User, {through: 'UserProject'});
  2. User.belongsToMany(Project, {through: 'UserProject'});

This will create a new model called UserProject with the equivalent foreign keys projectId and userId. Whether the attributes are camelcase or not depends on the two models joined by the table (in this case User and Project).

Defining through is required. Sequelize would previously attempt to autogenerate names but that would not always lead to the most logical setups.

This will add methods getUsers, setUsers, addUser,addUsers to Project, and getProjects, setProjects, addProject, and addProjects to User.

Sometimes you may want to rename your models when using them in associations. Let's define users as workers and projects as tasks by using the alias (as) option. We will also manually define the foreign keys to use:

  1. User.belongsToMany(Project, { as: 'Tasks', through: 'worker_tasks', foreignKey: 'userId' })
  2. Project.belongsToMany(User, { as: 'Workers', through: 'worker_tasks', foreignKey: 'projectId' })

foreignKey will allow you to set source model key in the through relation.otherKey will allow you to set target model key in the through relation.

  1. User.belongsToMany(Project, { as: 'Tasks', through: 'worker_tasks', foreignKey: 'userId', otherKey: 'projectId'})

Of course you can also define self references with belongsToMany:

  1. Person.belongsToMany(Person, { as: 'Children', through: 'PersonChildren' })
  2. // This will create the table PersonChildren which stores the ids of the objects.

Source and target keys

If you want to create a belongs to many relationship that does not use the default primary key some setup work is required.You must set the sourceKey (optionally targetKey) appropriately for the two ends of the belongs to many. Further you must also ensure you have appropriate indexes created on your relationships. For example:

  1. const User = this.sequelize.define('User', {
  2.   id: {
  3.     type: DataTypes.UUID,
  4.     allowNull: false,
  5.     primaryKey: true,
  6.     defaultValue: DataTypes.UUIDV4,
  7.     field: 'user_id'
  8.   },
  9.   userSecondId: {
  10.     type: DataTypes.UUID,
  11.     allowNull: false,
  12.     defaultValue: DataTypes.UUIDV4,
  13.     field: 'user_second_id'
  14.   }
  15. }, {
  16.   tableName: 'tbl_user',
  17.   indexes: [
  18.     {
  19.       unique: true,
  20.       fields: ['user_second_id']
  21.     }
  22.   ]
  23. });
  25. const Group = this.sequelize.define('Group', {
  26.   id: {
  27.     type: DataTypes.UUID,
  28.     allowNull: false,
  29.     primaryKey: true,
  30.     defaultValue: DataTypes.UUIDV4,
  31.     field: 'group_id'
  32.   },
  33.   groupSecondId: {
  34.     type: DataTypes.UUID,
  35.     allowNull: false,
  36.     defaultValue: DataTypes.UUIDV4,
  37.     field: 'group_second_id'
  38.   }
  39. }, {
  40.   tableName: 'tbl_group',
  41.   indexes: [
  42.     {
  43.       unique: true,
  44.       fields: ['group_second_id']
  45.     }
  46.   ]
  47. });
  49. User.belongsToMany(Group, {
  50.   through: 'usergroups',
  51.   sourceKey: 'userSecondId'
  52. });
  53. Group.belongsToMany(User, {
  54.   through: 'usergroups',
  55.   sourceKey: 'groupSecondId'
  56. });

If you want additional attributes in your join table, you can define a model for the join table in sequelize, before you define the association, and then tell sequelize that it should use that model for joining, instead of creating a new one:

  1. class User extends Model {}
  2. User.init({}, { sequelize, modelName: 'user' })
  3. class Project extends Model {}
  4. Project.init({}, { sequelize, modelName: 'project' })
  5. class UserProjects extends Model {}
  6. UserProjects.init({
  7.   status: DataTypes.STRING
  8. }, { sequelize, modelName: 'userProjects' })
  10. User.belongsToMany(Project, { through: UserProjects })
  11. Project.belongsToMany(User, { through: UserProjects })

To add a new project to a user and set its status, you pass extra options.through to the setter, which contains the attributes for the join table

  1. user.addProject(project, { through: { status: 'started' }})

By default the code above will add projectId and userId to the UserProjects table, and remove any previously defined primary key attribute - the table will be uniquely identified by the combination of the keys of the two tables, and there is no reason to have other PK columns. To enforce a primary key on the UserProjects model you can add it manually.

  1. class UserProjects extends Model {}
  2. UserProjects.init({
  3.   id: {
  4.     type: Sequelize.INTEGER,
  5.     primaryKey: true,
  6.     autoIncrement: true
  7.   },
  8.   status: DataTypes.STRING
  9. }, { sequelize, modelName: 'userProjects' })

With Belongs-To-Many you can query based on through relation and select specific attributes. For example using findAll with through

  1. User.findAll({
  2.   include: [{
  3.     model: Project,
  4.     through: {
  5.       attributes: ['createdAt', 'startedAt', 'finishedAt'],
  6.       where: {completed: true}
  7.     }
  8.   }]
  9. });

Belongs-To-Many creates a unique key when primary key is not present on through model. This unique key name can be overridden using uniqueKey option.

  1. Project.belongsToMany(User, { through: UserProjects, uniqueKey: 'my_custom_unique' })