Sequelize Database Entity Relation for Postgres SQL-Many To Many Relation

http://knowledge-cess.com/sequelize-database-entity-relation-for-postgres-sql-many-to-many-relation/

In case of Many To Many relation we can define relation using Sequelize belongsToMany method. But before moving on to Many-To-Many relation in sequelize, I suggest readers to go through my previous post on One-To-Many relation with sequelize.

Defining one-to-many relation in Sequelize

Now let us consider the below relation for understanding on how to define Many-to-many relation using sequelize. If you are developing a platform for school management system and considering the requirement one user is associated with many schools, this is true in case of teachers, where they will be teaching in multiple schools / colleges as guest lectures.

The database schema for Users and Schools Entities looks as follows.

Defining Many-To-Many Relation using Sequelize

Users Model

<code>
const UserModel = db.define(
  'users',
  {
    userId: {
      field: 'userId',
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    firstName: {
      field: 'firstName',
      type: sqlOperator.STRING(100),
      require: true
    },
    lastName: {
      field: 'lastName',
      type: sqlOperator.STRING(100),
    },
    email: {
      field: 'email',
      type: sqlOperator.STRING(255),
      defaultValue: null,
      allowNull: true,
      // unique: true,
      validate: {
        isEmail: true
      }
    },
    userName: {
      field: 'userName',
      type: sqlOperator.STRING(255),
      defaultValue: null,
      allowNull: true,
      // unique: true
    }
  },
  {
    tableName: 'users',
    timestamps: true,
    freezeTableName: true,
  }
);

UserModel.associate = async () => {
  //defining relation in user schools model
}

module.exports = UserModel;
</code>

Observe here that the relation between Users and Schools is defined through another intermediate table called User Schools hence the ralation is defined using belongsToMany using through keyword as given below.

UserSchools Model

<code>const SchoolModel = require('./SchoolModel');
const UserModel = require('./UserModel');

const UserSchoolModel = db.define(
  'user_schools',
  {
    userSchoolId: {
      field: 'userSchoolId',
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    }
  },
  {
    tableName: 'user_schools',
    timestamps: true
  }
);

UserSchoolModel.associate = async () => {
  const UserModel = require("./UserModel");
  UserModel.belongsToMany(SchoolModel, {
    through: UserSchoolModel,
    foreignKey: "userUserId",
    as: 'userSchools'
  });
  SchoolModel.belongsToMany(UserModel, {
    through: UserSchoolModel,
    foreignKey: "schoolSchoolId",
    as: 'schoolUsers'
  });
};
module.exports = UserSchoolModel;
</code>

If you try to achieve this only using belongsTo and hasMany then you will face many sequelize errors while querying. You must use belongsToMany along with through keyword.