Sequelize: How to make relationship between 3 tables

Question

If you have the following entities: Users, Roles, Organizations. You want to setup the relationships so that each user has an organization-role.

In simple each user can belong to multiple organizations and the user has a specific role in each organization.

How would you model this with Sequelize?

I have tried by creating a junction table called organisation_users and then in that table adding a organisationUsers.belongsTo(role); From I have read Sequelize doesnt support associations on junction tables and so that solution doesn't work.

Regards, Emir

Answer 1

It may vary upon your requirements of fetching the data from these 3 tables Consider this Example : Tables : login, userProfile, farmer Requirements : Farmer has one user Profile , User Profile has one Login. Along with these tables we can register a user as a Farmer.

--------------------------------------------------------------------------
farmer.js

module.exports = (Sequelize, DataTypes) => {
  const Farmer = Sequelize.define("farmer", { /*attributes*/});
  return Farmer;
};

--------------------------------------------------------------------------
login.js

module.exports = (Sequelize, DataTypes) => {
  const Login = Sequelize.define("login", {*attributes*/});

  return Login;
};
--------------------------------------------------------------------------
userProfile.js

module.exports = (Sequelize, DataTypes) => {
  const UserProfile = Sequelize.define("userProfile", {*attributes*/});

  return UserProfile;
};

--------------------------------------------------------------------------
index.js

const dbConfig = require("../config/dbConfig"); // your config file
const { Sequelize, DataTypes } = require("sequelize");

//object initilize. (pass parameter to constructor)
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false, //hide errors
  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle,
  },
});

sequelize
  .authenticate()
  .then(() => {
    console.log("DB connected!");
  })
  .catch((err) => {
    console.log("Error " + err);
  });

const db = {}; // Empty object

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.login = require("./login.js")(sequelize, DataTypes);
db.userProfile = require("./userProfile.js")(sequelize, DataTypes);
db.farmer = require("./farmer.js")(sequelize, DataTypes);

//relations
db.farmer.userProfile = db.farmer.belongsTo(db.userProfile);

db.userProfile.login = db.userProfile.belongsTo(db.login);

db.sequelize
  .sync({ force: false }) //force :true - drop all tables before start
  .then(() => {
    console.log("yes-sync done!");
  });

module.exports = db;

//Declare follwing things in a separate location (may be controllers__.js
-----------INSERT DATA (CREATE)--------------------------

const saved = await Farmer.create(
    {
      supplierCode: "SUP0001",
      userProfile: {
        firstName: "ssss",
        middleName: "ssss",
        lastName: "ssss",
        address: "ssss",
        login: {
          name: "ssss",
          email: "ssss",
          password: "ssss",
          role: "ssss",
          lastLogin: null,
          avatar: "ssss",
          status: "ssss",
        },
      },
    },
    {
      include: [
        {
          association: Farmer.userProfile,
          include: [Login],
        },
      ],
    }
  );

Observe the usage of the include option in the Farmer.create call. That is necessary for Sequelize to understand what you are trying to create along with the association.

Note: here, our user model is called farmer, with a lowercase f - This means that the property in the object should also be farmer. If the name given to sequelize.define was Farmer, the key in the object should also be Farmer.

-----------FETCH DATA (SELECT)-------------------------- 

const users = await Farmer.findAll({
    include: [
      {
        association: Farmer.userProfile,
        include: [Login],
      },
    ],
  });

Output:
[
    {
        "id": 1,
        "supplierCode": "SUP0001",
        "createdAt": "2021-11-17T07:39:13.000Z",
        "updatedAt": "2021-11-17T07:39:13.000Z",
        "userProfileId": 1,
        "userProfile": {
            "id": 1,
            "firstName": "ssss",
            "middleName": "ssss",
            "lastName": "ssss",
            "address": "ssss",
            "createdAt": "2021-11-17T07:39:13.000Z",
            "updatedAt": "2021-11-17T07:39:13.000Z",
            "loginId": 1,
            "login": {
                "id": 1,
                "name": "ssss",
                "email": "ssss",
                "password": "ssss",
                "role": "ssss",
                "lastLogin": null,
                "avatar": "ssss",
                "status": "ssss",
                "createdAt": "2021-11-17T07:39:13.000Z",
                "updatedAt": "2021-11-17T07:39:13.000Z"
            }
        }
    }
] 

Assume you want to add another user Role/Type (we have Farmer already). then you can make coordinater.js (example user role/type) and defind attributes

in above index.js you can add this relation

//Coordinator relation

db.coordinator.userProfile = db.coordinator.belongsTo(db.userProfile, {
  onDelete: "CASCADE",
  onUpdate: "CASCADE",
}); 

Now you can register users with different users Roles/Types : Coordinator.create.... give correct associations

Answer 2

Sequelize does supports associations on join table. You should look in the "through" options here >

Example :

const Asso_Organization_User = sequelize.define('Asso_Organization_User', {
    id: DataTypes.STRING,
    userId: DataTypes.STRING,
    organizationId: DataTypes.STRING
});
User.Organizations = User.belongsToMany(Organization, {
    through: Asso_Organization_User,
    foreignKey: 'userId',
    otherKey: 'organizationId',
    as: 'organizations'
})

But your case is a bit special, I dont see a way using sequelize to get a user, all of its organizations, and his role for each organization in the same query. It looks like there is no solution yet, based on this issue: https://github.com/sequelize/sequelize/issues/6671

Maybe you could do it in two queries : get all organizations and all roles, and then merging them with code.

Actually, I see another solution:

const Asso_Organization_User = sequelize.define('Asso_Organization_User', {
    id: DataTypes.STRING,
    userId: DataTypes.STRING,
    organizationId: DataTypes.STRING
});
User.Organizations = User.belongsToMany(Organization, {
    through: Asso_Organization_User,
    foreignKey: 'userId',
    otherKey: 'organizationId',
    as: 'organizations'
})

The model would look like the following :

User: id
Role: id, userId, organizationId
Organizations: id
Asso_Organization_User: id, userId, organizationId

Then :

User.Organizations = User.belongsToMany(Organization, {
    through: Asso_Organization_User
})
Organization.Roles = Organization.haMany(Role, {
    foreignKey: 'organizationId'
})

And then you should be able to query :

   User.findAll({
        include: [ {
            model: Organization,
            include: {
                model: Role  
            }
        } ]
        where: {
            'role.userId': Sequelize.col("User.id")
        }
    });

I'm not totally sure of the exact syntax, but combining Sequelize.col with the hasMany should work. But be careful, if you dont add this where clause, this would return all roles for each organization (for every user having a role in this organization).