create/update with Many to Many association in Node.js sequelize and PostgreSQL

Question

I am just creating or updating a table having a Many-to-Many association with another table like here: Company <-> Industry <-> CompanyIndustryRelation.

Industry.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Industry = sequelize.define('Industry', {
    industry_name: DataTypes.STRING,
  }, {
    timestamps: false,
    underscored: true,
    tableName: 'industry',
  });
  Industry.associate = function(models) {
    Industry.belongsToMany(models.Company, {
      through: 'company_industry_relation', foreignkey: 'industry_id'
    });
  };
  return Industry;
};

Company.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Company = sequelize.define('Company', {
    company_name: DataTypes.STRING,
  }, {
    timestamps: false,
    underscored: true,
    tableName: 'company',
  });
  Company.associate = function(models) {
    Company.belongsToMany(models.Industry, {
      through: 'company_industry_relation', foreignKey: 'company_id'
    });
  };
  return Company;
};

CompanyIndustryRelation.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  const CompanyIndustryRelation = sequelize.define('CompanyIndustryRelation', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
    },
  }, {
    timestamps: false,
    underscored: true,
    tableName: 'company_industry_relation',
  });
  return CompanyIndustryRelation;
};

Currently I have the industry table already built like down below.

Industry array industry = [ { label: 'Accounting' }, { label: 'Computer Science' } ]

CompanyName: 'ApolloIT'

I want to create a new company record with the given industry array and companyName.

Thanks in advance!

Your question is not clear. Please be more specific. It is better if you show an example of your data and what you want to achieve. With your relation I can only see Company -> CompanyIndustryRelation <- Industry. It's not clear what you want to create or update. — Dan
– Dan, Commented Mar 2, 2020 at 16:18
Dan, I have tried like this for (const i of industry) { const industryInstance = await Industry.findOne({ industry_name: i.label }); await CompanyIndustryRelation.create({ company_id: company.id, industry_id: industryInstance.id }); } But it's saying error "id" of relation "company_industry_relation" does not exist — apollo
– apollo, Commented Mar 2, 2020 at 16:43

apollo · Accepted Answer · 2020-03-02 22:38:42Z

1

I have found a simple way to create/update records with associations.

industries: [ 
 { value: 'Gaming', label: 'Gaming' },
 { value: 'Computer Science', label: 'Computer Science' } 
]

const company = await Company.create({
   company_name: companyName,
});

const industry = await Industry.findAll({
   where: { industry_name: { [Op.in]: _.map(industries, o => o.label) } }
});
await company.addIndustry(industry);

Please refer to here. https://sequelize.org/master/manual/advanced-many-to-many.html

answered Mar 2, 2020 at 22:38

apollo

1952 gold badges6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan · Accepted Answer · 2020-03-02 17:40:20Z

0

Ok. For what I can see, you are not doing your model definition right. In sequelize you create a model to relate to a database table, generally, this model has to be the same as the database table.

For Industry.js, your just defining the industry_name column, forgetting the id. The same goes for Company.js. If sequeliaze doesn't know the primary key... it wont work well.

In the case of CompanyIndustryRelation.js, generally, you don't need an id since the primary key (generally) is the related tables id. In this case it should be industry_id and company_id. Again, in the CompanyIndustryRelation.js there are no such fields.

When you are using a n to n relation, first you need to create the master tables. If you have:

company ---> CompanyIndustryRelation <--- Industry

Then first you have to create the company and the industry then the CompanyIndustryRelation.

In sequelize you can do (for create):

sequelize.transaction((t) => {// to make a rollback in case something goes wrong

    return Company.create(data_company, { transaction: t }).then((new_company) => {

        return Industry.create(data_industry, { transaction: t }).then((new_industry) => {

            //here goes the created ids, asuming your using autoincrement to create ids
            var data_relation = {industry_id: new_industry.id,company_id: new_company.id};
            return CompanyIndustryRelation.create(data_relation, { transaction: t }).then(()=>{

                return;// Everything OK you could return something here

// when there is an error, you have to throw it for the rollback to work
            }).catch(err => {throw err;});

        }).catch(err => {throw err;});

    }).catch(err => {throw err;});

}).catch(err => {
    // process error, then rollback
});

For the update, it is the same if you want to update in cascade, it is a good practice to use transactions in case something goes wrong in the middle.

There are other methods to use cascade updates or creates but everything is better if you use transactions

answered Mar 2, 2020 at 17:40

Dan

1,9211 gold badge16 silver badges20 bronze badges

6 Comments

apollo Over a year ago

Thank you for the answer, btw. is there any way to use async-await ? And usage of the transaction is a must and mandatory?

Dan Over a year ago

In the link transactions is the sequelize manual and how to use it with await.

apollo Over a year ago

TypeError: _sequelize.default.transaction is not a function, where can I reference sequelize?

apollo Over a year ago

Unfortunately, it didn't work, still saying column "id" of relation "company_industry_relation" does not exist

apollo Over a year ago

I tried

const t = await db.sequelize.transaction(); const company = await Company.create({         company_name: companyName       }, { transaction: t });        for (const industry of industries) {         const industryInstance = await Industry.findOne({ industry_name: industry.label });         await CompanyIndustryRelation.create({ company_id: company.id, industry_id: industryInstance.id }, { transaction: t });         await t.commit();       }

But it doesn't work

|

Collectives™ on Stack Overflow

create/update with Many to Many association in Node.js sequelize and PostgreSQL

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related