How can I bulk update records using sequelize.js and ignore certain columns

Question

How can I bulk update records using sequelize.js. All of the time, I will be having only 150 users to update. In this case how do I find unique records to udpate in MySql ? At the moment update of record is not happening

1.email field is unique

2.existing records if changed need to be updated

3.new records need to be inserted

server.js

const manageNomineesSchema = require('./server/models/managenominees');
const ManageNomineesModel  = manageNomineesSchema(sequelize, DataTypes);
app.post('/service/managenominees', upload.single('file'), async (req, res, next) => {
    try {
        if(req.file){
          let filePath = req.file.path;
          fs.createReadStream(filePath)
              .pipe(csv())
              .on('data', (data) => results.push(data))
              .on('end', async () => {
                console.log(results);
                const allNominees = results.map(
                    nominees => {
                      return {
                        name: nominees.name,
                        email: nominees.email
                      }
                    });
        const emailCount = await ManageNomineesModel.count({ col: 'email' });
        if(emailCount == 0){
          await ManageNomineesModel.bulkCreate(allNominees);
        } else {
          await ManageNomineesModel.bulkCreate(allNominees,
              { updateOnDuplicate: ["email"] },
              {attributes: { exclude: ['createdAt'] }
              })
        }
                res.status(200).json({ message: "Nominees inserted successfully !"});
              });
          }
    
      } catch (e) {
        res.status(500).json({ fail: e.message });
      }

});

managenominees.js

module.exports = (sequelize, DataTypes) => {
    const managenominees = sequelize.define('managenominees', {
        id: {
            type: DataTypes.INTEGER(10),
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        name: {
            type: DataTypes.STRING(200),
            allowNull: false
        },
        email: {
            type: DataTypes.STRING(100),
            allowNull: false
        },
        access: {
            type: DataTypes.STRING(10),
            allowNull: true
        },
        createdAt: {
            type: DataTypes.DATE,
            allowNull: false,
            defaultValue: DataTypes.NOW
        },
        updatedAt: {
            type: DataTypes.DATE,
            allowNull: false,
            defaultValue: DataTypes.NOW
        }
    }, {
        timestamps: true,
        tableName: 'managenominees'
    });

    return managenominees;
};

soccerway · Accepted Answer · 2021-08-10 09:44:41Z

1

After adding a PK inside the where clause where: { id: ['id']}, it started updating the records against the column updateOnDuplicate: ["name"]

const emailCount = await ManageNomineesModel.count({ col: 'email' });
    if(emailCount == 0){
      await ManageNomineesModel.bulkCreate(allNominees);
      res.status(200).json({ message: "Nominees inserted successfully !"});
    } else {
      await ManageNomineesModel.bulkCreate(allNominees,
          { updateOnDuplicate: ["name"],
          where: { id: ['id']}
          });
      res.status(200).json({ message: "Nominee records updated successfully !"});
    }

answered Aug 10, 2021 at 9:44

soccerway

12.1k23 gold badges86 silver badges166 bronze badges

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

Collectives™ on Stack Overflow

How can I bulk update records using sequelize.js and ignore certain columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related