Sequelize - query with join table

Question

I'm building role based authentication with permissions. I have following tables: Users, Groups, GroupPermissions and Permissions.

Users model:

module.exports = (sequelize, DataTypes) => {
    var User = sequelize.define('User', {
        id: {
            primaryKey: true,
            type: DataTypes.UUID,
            defaultValue: DataTypes.UUIDV4,
        },
        email: DataTypes.STRING,
        password: DataTypes.STRING,
    }, {});

    User.associate = function(models) {
        User.belongsTo(models.Group, { as: 'group', foreignKey: 'groupId' });
    };

    return User;
};

Group model:

module.exports = (sequelize, DataTypes) => {
    var Group = sequelize.define('Group', {
        id: {
            primaryKey: true,
            type: DataTypes.UUID,
            defaultValue: DataTypes.UUIDV4,
        },
        name: DataTypes.STRING,
    }, {
        timestamps: false,
    });

        Group.associate = function(models) {
        Group.hasMany(models.User, { foreignKey: 'groupId' });
    };

    return Group;
};

GroupPermission model:

    module.exports = (sequelize, DataTypes) => {
        var GroupPermission = sequelize.define('GroupPermission', {
            id: {
                primaryKey: true,
                type: DataTypes.UUID,
                defaultValue: DataTypes.UUIDV4,
            },
            groupId: {
                type: DataTypes.UUID,
                allowNull: false,
            },
            permissionId: {
                type: DataTypes.UUID,
                allowNull: false,
            }
        }, {});

        GroupPermission.associate = function(models) {
        };

        return GroupPermission;
    };

Permission model:

module.exports = (sequelize, DataTypes) => {
    var Permission = sequelize.define('Permission', {
        id: {
            primaryKey: true,
            type: DataTypes.UUID,
            defaultValue: DataTypes.UUIDV4,
        },
        name: {
            type: DataTypes.STRING,
            unique: true,
            allowNull: false,
        },
        level: {
            type: DataTypes.TINYINT,
            allowNull: false,
        }
    }, {});

    Permission.associate = function(models) {
    };

    return Permission;
};

And this is my method to get all users:

getAll(req, res) {
    return User
        .findAll({
            include: [
                {
                    model: Group,
                    as: 'group',
                },
            ]
        })
        .then(users => res.status(201).send({ users }))
        .catch(error => res.status(400).send(error));
},

Which gives me:

{
    "users": [
        {
            "id": "03c50380-3f05-4d8d-893f-408a179fb993",
            "email": "johndoe@gmail.com",
            "password": "$2b$10$fP3BR14iSZRHs/sYi0QAVuh8Zno60GO1dOKbN4K7GkOdVod2EENfC",
            "groupId": "c89ef57e-da09-42c1-bb09-0d988a35dd97",
            "group": {
                "id": "c89ef57e-da09-42c1-bb09-0d988a35dd97",
                "name": "Admin"
            }
        }
    ]
}

I also want to have permissions either inside of group or next to the group property, but I can't figure out what I'm doing wrong - with query or with associacions. I ommited the code in which I was struggling to figure out associations to not confuse anyone interested in helping me with that.

Answer 1

First you have to associate Permission and Group model. I think you want many-to-many correspondence through GroupPermission

module.exports = (sequelize, DataTypes) => {
  var Group = sequelize.define('Group', {
    id: {
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
    },
    name: DataTypes.STRING,
  }, {
    timestamps: false,
  });

  Group.associate = function(models) {
    Group.hasMany(models.User, { foreignKey: 'groupId' });
    Group.belongsToMany(models.Permission, { as: 'permissions', through: models.GroupPermission, foreignKey: 'groupId', otherKey: 'permissionId' })
  };

  return Group;
};

module.exports = (sequelize, DataTypes) => {
  var GroupPermission = sequelize.define('GroupPermission', {
    id: {
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
    },
    groupId: {
      type: DataTypes.UUID,
      allowNull: false,
    },
    permissionId: {
      type: DataTypes.UUID,
      allowNull: false,
    }
  }, {});

  GroupPermission.associate = function(models) {
  };

  return GroupPermission;
};

module.exports = (sequelize, DataTypes) => {
  var Permission = sequelize.define('Permission', {
    id: {
      primaryKey: true,
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
    },
    name: {
      type: DataTypes.STRING,
      unique: true,
      allowNull: false,
    },
    level: {
      type: DataTypes.TINYINT,
      allowNull: false,
    }
  }, {});

  Permission.associate = function(models) {
    Permission.belongsToMany(models.Group, { through: models.GroupPermission, foreignKey: 'permissionId', otherKey: 'groupId' });
  };

  return Permission;
};

then you can:

getAll(req, res) {
  return User
    .findAll({
      include: [
        {
          model: Group,
          as: 'group',
          include: [{
            model: Permission,
            as: 'permissions',
          }]
        },
      ]
    })
    .then(users => res.status(201).send({ users }))
    .catch(error => res.status(400).send(error));
}

I haven't tested the code, but it should give you the idea.