How to use upsert on the many to many relationship using sequelize?

Question

I want to update my record if it exists or create a new record if the record is present. I am using upsert for that. I have two tables USERS and GROUPS with a many to many relationship(auto created users_groups). This is the code I have written

const [instance, created] = await models.USERS.upsert({
  userName: userName,
  userId: userId,
  lastName: lastName,
  GROUPS: [
   {
      groupId: 123,
      groupName: "abc"
   },
   {
      groupId: 345,
      groupName: "def"
   }
 ],
 }, {
   include: {
   model: models.GROUPS
  },
});

Here userId is unique for USERS table and groupId is unique for GROUPS. Primary key for USERS is userPK and for GROUPS it is groupsPK. upsert is working only for USERS table not for GROUPS. Where am I going wrong?    

Answer 1

Just came across this problem. I don't think sequelize handles upserting nested data. Just like it does not handle updating nested data. So for example user.update({ GROUPS: [{ groupId: 1, }] }); does not work as well, although I can't find the location in the documentation where I read that.

You want to make two calls, upsert the user data to update the userName and lastName. Then a second call upserting on the many to many linking table to update the groups. The linking table is probably called userGroups or groupUsers in your case. The problem is when you let sequelize create the linking table automatically you cannot call upsert on it because you do not have a model object like you have for you user model.

The solution is to explicitly create the linking table as shown by the code below that is on this page.

const User_Profile = sequelize.define('User_Profile', {}, { timestamps: false });
User.belongsToMany(Profile, { through: User_Profile });
Profile.belongsToMany(User, { through: User_Profile });

Relevant documentation: https://sequelize.org/docs/v6/advanced-association-concepts/advanced-many-to-many/