Extra columns in many to many junction table are not added/updated in Sequelize

Question

Here are the models( many to many)

Company.js

module.exports = (sequelize, DataTypes) => {
  const Company = sequelize.define('Company', {
    company_name: DataTypes.STRING,
  }, {
    timestamps: true,
    underscored: true,
    tableName: 'company',
  });
    Company.belongsToMany(models.Goal, {
      through: 'company_goal_relation', foreignKey: 'company_id', as: 'goal'
    });
  };
  return Company;
};

Goal.js

module.exports = (sequelize, DataTypes) => {
  const Goal = sequelize.define('Goal', {
    goal_name: DataTypes.STRING,
  }, {
    timestamps: false,
    underscored: true,
    tableName: 'goal',
  });
  Goal.associate = function(models) {
    Goal.belongsToMany(models.Company, {
      through: 'company_goal_relation',
      foreignKey: 'goal_id',
      as: 'company',
    });
  };
  return Goal;
};

CompanyGoalRelation.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const CompanyGoalRelation = sequelize.define('CompanyGoalRelation', {
    company_id: DataTypes.INTEGER,
    goal_id: DataTypes.INTEGER,
    objective: DataTypes.STRING,
    description: DataTypes.TEXT,
  }, {
    timestamps: false,
    underscored: true,
    tableName: 'company_goal_relation',
  });

  CompanyGoalRelation.associate = function(models) {
    CompanyGoalRelation.belongsTo(models.Company, { foreignKey: 'company_id' });
    CompanyGoalRelation.belongsTo(models.Goal, { foreignKey: 'goal_id' });
  };

  return CompanyGoalRelation;
};

What I want here is to add or update the extra columns of the junction table. What I tried is down below.

const { goal, companyId } = req.body;
const company = await Company.findByPk(companyId);
    const goalInstance = await Goal.findOne({
      where: { id: goal.id },
    });
await company.addGoal(goalInstance, {
      through: {
        objective: goal.objective,
        description: goal.description,
      }
    });

No error occurred and company_id, goal_id were successfully added but both objective and description were still null.

I am currently using Sequelize (5.6.0) and PostgreSQL. Anybody can help? Thank you!

I understand you are using model.sync, right? I don't know the answer to your question, but i would strongly recommend not using model.sync, and switch to using migrations instead. There, you could manually create each table exactly as you want it, and both your development/production db's will always be in sync, after every change you make to a table(and run the migrations, of course). model.sync will NOT change the structure of your existing production db, so it's quite useless to rely on it, in my opinion. — i.brod, Mar 26 '20 at 13:49
@ibrod Thank you for replying. Got it. It makes sense if I rather use migration. But comparing other tables (OneToMany, OneToOne), once I delete the tables in postgreSQL and run the model sync, it would create the right tables containing right columns that I defined in model. But only the problem is in ManyToMany association. Even if I delete the junction table and run model sync, it creates the junction table contains only foreignKeys, doesn't contain the columns. I think the reason is junction table is created implicitly? — apollo, Mar 26 '20 at 16:09
Yes it's created implicitly, and i'm sure it can be configured, but the docs are so poor that it's hard to find. Regarding migrations: once you use them, you don't need(and shouldn't) use model.sync. When u create a new DB, just run all migrations on it(one command), and everything is created. But, this of course requires you to also create the junction tables, within migrations. — i.brod, Mar 26 '20 at 16:18
Thank you @i.brod, helpful. Why I was starting from this issue was I couldn't add/update the extra columns in the junction table even when I added the extra columns manually in postgreSQL and used setter function like here. https://stackoverflow.com/questions/39409088/sequelize-many-to-many-with-extra-columns?rq=1 , The reason is to add the extra columns manually , not using migration? I don't think so. I will update the issue details as I wanted finally. — apollo, Mar 26 '20 at 20:18
How do you insert records into the junction table? Do you perform a raw query into that table(this is what i did...), or are you using some Sequelize functionality for it?. To be honest i think the docs of sequelize are a disaster....very hard to find useful data. — i.brod, Mar 26 '20 at 20:23
@i.brod. Thank you for quick replying. I updated the question. — apollo, Mar 26 '20 at 20:31

score 0 · Answer 1 · answered Mar 26 '20 at 20:44

So, as i said, i don't know how to configure Sequelize properly in this regard, and i think it's a waste of time. What i would do, is just perform a raw query. Notice that in your case, you're performing a totally unnecessary query to the database, to first fetch the Company record by its id, and then you perform another one to create the relation(2 queries instead of 1...)

So you can just pass the desired relation fields, let's say from an ajax request, to your express application(assuming this is what you're using):

const {companyId,goalId,objective,description} = req.body;

And then perform the query:

db.query(`insert into company_goal_relation (company_id,goal_id , objective, description,createdAt,updatedAt) values (?,?,?,?,NOW(),NOW()) `,{
        replacements:[companyId,goalId,objective,description]
    })

Notice that "db" is the sequelize object you've setup in the beginning, you will need to import it from your config/setup file.

Of course this isn't the "normal" sequelize way to do this, but i see no reason why it shouldn't be done like this, and then it's end of story :D

Extra columns in many to many junction table are not added/updated in Sequelize

1 Answers1