sequelize-relations

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

Sequelize one-to-many relation can be defined using belongsTo and hasMany methods. but before moving on to one-to-many I suggest readers to go through my previous post on one-to-one relation.

Defining One-To-One relation in Sequelize

Now let us consider a school management system when we define relation between Subjects and Chapters. One subject has many chapters. The database schema looks as follows.

Master Subjects Entity

<code>const MasterAcademicsModel = require('../Models/MasterAcademicsModel');
const MasterBoardsModel = require('../Models/MasterBoardsModel');
const MasterClassModel = require('../Models/MasterClassModel');
const MasterMediumsModel = require('../Models/MasterMediumsModel');


const MasterSubjectModel = db.define(
  "master_subjects",
  {
    subjectId: {
      field: "subjectId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4,
    },
    name: {
      field: "name",
      type: sqlOperator.STRING(100),
      allowNull: false,
    },
  },
  {
    tableName: "master_subjects",
    timestamps: true,
  }
);

MasterSubjectModel.associate = async () =>{
  await MasterSubjectModel.belongsTo(MasterAcademicsModel,{
    foreignKey: 'masterSubjectAcademicId',
    targetKey: 'academicId'
  });
  await MasterSubjectModel.belongsTo(MasterBoardsModel,{
    foreignKey: 'masterSubjectBoardId',
    targetKey: 'boardId'
  });
  await MasterSubjectModel.belongsTo(MasterMediumsModel,{
    foreignKey: 'masterSubjectMediumId',
    targetKey: 'mediumId'
  });
  await MasterSubjectModel.belongsTo(MasterClassModel,{
    foreignKey: 'masterSubjectClassId',
    targetKey: 'classId'
  });
}

module.exports = MasterSubjectModel;
</code>

Chapters Model

<code>
const ChapterModel = db.define(
  "chapters",
  {
    chapterId: {
      field: "chapterId",
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4,
    },
    title: {
      field: "title",
      type: sqlOperator.STRING(100),
    },
    tags: {
      field: "tags",
      type: sqlOperator.STRING(100),
    },
  },
  {
    tableName: "chapters",
    timestamps: true,
  }
);

ChapterModel.associate = async () => {
  console.log(
    "--- Establishing relations between chapter and chapter content ---"
  );
  const MasterSubjectModel = require("./MasterSubjectModel");
  const ChapterContentModel = require("./ChapterContentModel");
  const MasterAcademicsModel = require("./MasterAcademicsModel");

  await ChapterModel.belongsTo(MasterSubjectModel, {
    foreignKey: "masterSubjectId",
    targetKey: "subjectId",
    as: "subject",
  });
  await MasterSubjectModel.hasMany(ChapterModel, {
    foreignKey: 'masterSubjectId',
    sourceKey: 'subjectId',
  });

  await ChapterModel.belongsTo(MasterAcademicsModel, {
    foreignKey: "masterAcademicId",
    targetKey: "academicId",
    as: "academic",
  });
  await MasterAcademicsModel.hasMany(ChapterModel, {
    foreignKey: 'masterAcademicId',
    sourceKey: 'academicId',
  });

  await ChapterModel.hasMany(ChapterContentModel, {
    foreignKey: 'chapterId',
    sourceKey: 'chapterId',
    as: "chapterContents",
  });
  await ChapterContentModel.belongsTo(ChapterModel, {
    foreignKey: "chapterId",
    targetKey: "chapterId",
  });
};

module.exports = ChapterModel;
</code>

Notice here that to define One-To-Many both belogsTo and hasMany method are used. you can define the relation using only belongsTo method, however you will endup with multiple sequelize errors. To avoid sequelize errors while fetching data using find method on one-to-many relation, kindly update your models by defining relation in both ways.

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.