Sequelize – is it possible to limit the number of record in junction table

There are 3 tables student_teacher, student, teacher with below relationship.

Each teacher will be responsible for 5 students, so the relationship should be 1 to Many, but I decide to create a junction table for storing extra information for this relationship.

When I create a student_teacher record, the payload will be like this:

{
  "studentId": "xxx",
  "teacherId": "yyy",
  "groupName": "Group A"
}

Let’s say I have record below now in table student_teacher:

[
{
    "studentId": "studentA",
    "teacherId": "teacherA",
    "groupName": "Group X"
},
{
    "studentId": "studentB",
    "teacherId": "teacherA",
    "groupName": "Group X"
},
{
    "studentId": "studentC",
    "teacherId": "teacherA",
    "groupName": "Group X"
},
{
    "studentId": "studentD",
    "teacherId": "teacherA",
    "groupName": "Group X"
},
{
    "studentId": "studentE",
    "teacherId": "teacherA",
    "groupName": "Group X"
}
]

There are already 5 record for teacherA in table student_teacher, I will to forbid to create 1 more record for teacherA.

Is it possible to do it in Sequelize? Or handle I need to handle it in node.js function?

student-teacher.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const studentTeacher = sequelizeClient.define('student_teacher', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    },
    studentId: {
      allowNull: false,
      type: DataTypes.UUID,
      references: { model: 'student', key: 'id' },
      defaultValue: Sequelize.UUIDV4,
      unique: 'studentId_foreign_idx'
    },
    teacherId: {
      allowNull: false,
      type: DataTypes.UUID,
      references: { model: 'teacher', key: 'id' },
      defaultValue: Sequelize.UUIDV4,
      unique: 'teacherId_foreign_idx'
    },
    groupName: {
      type: DataTypes.STRING,
      allowNull: false,
      defaultValue: ''
    },
    ...
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  studentTeacher.associate = function (models) {};

  return studentTeacher;
};

student.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const student = sequelizeClient.define('student', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      isEmail: true,
      unique: 'email'
    },
    firstName:{
      type: DataTypes.STRING,
      allowNull: false,
      defaultValue: '',
    },
    lastName:{
      type: DataTypes.STRING,
      allowNull: false,
      defaultValue: '',
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  student.associate = function (models) {
 student.belongsToMany(models.teacher, { as: 'teachers', through: 'student_teacher', foreignKey: 'studentId', onDelete: 'cascade' })
  };

  return student;
};

teacher.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const teacher = sequelizeClient.define('teacher', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  teacher.associate = function (models) {
 teacher.belongsToMany(models.student, { as: 'students', through: 'student_teacher', foreignKey: 'teacherId', onDelete: 'cascade' })
  };

  return teacher;
};

Answer

Sequelize’s hooks are very compatible with your requirement:

// in your student-teacher.model.js model (before you return the model)

studentTeacher.beforeCreate(async (instance, options) => {
  try {
      const result = await studentTeacher.findAll({
          where: {
              teacherId: instance.teacherId
          }
      });
      if(result.length === MAX_RECORDS_FOR_TEACHER) {
          throw new Error(`Cannot create more instnaces for ${instance.teacherId}`);
      }
  }
  catch(e) {
      throw e; // You must throw an error inside the hook in order to cancel 
               // the real statement execution.
  }
});

Read more: https://sequelize.org/master/manual/hooks.html