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

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;

Chapters Model


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;

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


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;

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

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;

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.

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

For one-to-one relation in database schema, it is easy to define the relation in Model Class using Sequelize BelongsTo method. Let us consider a schema given below which has Users and UserProfile models. One user can have one UserProfile, Which is 1-to-1 relation.

Sequelize one-to-one relation
Sequelize one-to-one relation

One-to-One Relation using belongsTo in Sequelize

Users Model


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
    },
    phone: {
      field: 'phone',
      type: sqlOperator.STRING(13),
      allowNull: true,
      // unique: true
    },
    photo: {
      field: 'photo',
      type: sqlOperator.STRING(1024),
      defaultValue: null,
      validate: {
        isUrl: true
      }
    },
  },
  {
    tableName: 'users',
    timestamps: true,
    freezeTableName: true,
  }
);

UserModel.associate = async () => {
  console.log('--- Establishing relations between user and user profile ---');

  const UserProfileModel = require('./UserProfileModel');

  UserModel.belongsTo(UserProfileModel, {
    foreignKey: 'profile',
    targetKey: 'userProfileId'
  }); /** foriengKey and targetKey are both from table */
}

module.exports = UserModel;

UserProfile Model


const UserProfileModel = db.define(
  'user_profiles',
  {
    userProfileId: {
      field: 'userProfileId',
      type: sqlOperator.UUID,
      primaryKey: true,
      defaultValue: sqlOperator.UUIDV4
    },
    address: {
      field: 'address',
      type: sqlOperator.STRING(100),
      require: true
    }
  },
  {
    tableName: 'user_profiles',
    timestamps: true,
  }
);

UserProfileModel.associate = async () => {
  console.log('--- Establishing relations between user profile ---');
}

module.exports = UserProfileModel;

While defining one to one relation, relation can be defined in any of the model, either User Model or UserProfile model. It will work fine with any way.

Let us understand foreign key and target key in belongsTo as given above.

foreignKey: ‘profile’, since we are defining relation in Users Model, the user profile id will be added to users table and that is why it become one to one relation. Hence foreignKey here means the column name to be added in Source Table that is Users Model.


targetKey: ‘userProfileId’, here the Source is Users model and since we are defining relation in Users Model, then the target model is UserProfiles model, hence the target key here is the primary key of UserProfile table.

As per sequelize documentation, it says “BelongsTo inserts the association key in the source model”, source model here is Users Model.

Read more about Defining One-To-Many relation using sequelize