bulkUpdate in sequelize orm
Minimal bulkCreate + updateOnDuplicate example
Just to clarify what was mentioned at https://stackoverflow.com/a/54900639/895245
const assert = require('assert');
const path = require('path');
const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize({
dialect: 'sqlite',
storage: 'tmp.' + path.basename(__filename) + '.sqlite',
});
(async () => {
const Integer = sequelize.define('Integer',
{
value: {
type: DataTypes.INTEGER,
unique: true, // mandatory
primaryKey: true,
},
name: {
type: DataTypes.STRING,
},
inverse: {
type: DataTypes.INTEGER,
},
},
{
timestamps: false,
}
);
await Integer.sync({force: true})
await Integer.create({value: 2, inverse: -2, name: 'two'});
await Integer.create({value: 3, inverse: -3, name: 'three'});
await Integer.create({value: 5, inverse: -5, name: 'five'});
// Initial state.
assert.strictEqual((await Integer.findOne({ where: { value: 2 } })).name, 'two');
assert.strictEqual((await Integer.findOne({ where: { value: 3 } })).name, 'three');
assert.strictEqual((await Integer.findOne({ where: { value: 5 } })).name, 'five');
assert.strictEqual((await Integer.findOne({ where: { value: 2 } })).inverse, -2);
assert.strictEqual((await Integer.findOne({ where: { value: 3 } })).inverse, -3);
assert.strictEqual((await Integer.findOne({ where: { value: 5 } })).inverse, -5);
assert.strictEqual(await Integer.count(), 3);
// Update.
await Integer.bulkCreate(
[
{value: 2, name: 'TWO'},
{value: 3, name: 'THREE'},
{value: 7, name: 'SEVEN'},
],
{ updateOnDuplicate: ["name"] }
);
// Final state.
assert.strictEqual((await Integer.findOne({ where: { value: 2 } })).name, 'TWO');
assert.strictEqual((await Integer.findOne({ where: { value: 3 } })).name, 'THREE');
assert.strictEqual((await Integer.findOne({ where: { value: 5 } })).name, 'five');
assert.strictEqual((await Integer.findOne({ where: { value: 7 } })).name, 'SEVEN');
assert.strictEqual((await Integer.findOne({ where: { value: 2 } })).inverse, -2);
assert.strictEqual((await Integer.findOne({ where: { value: 3 } })).inverse, -3);
assert.strictEqual((await Integer.findOne({ where: { value: 5 } })).inverse, -5);
assert.strictEqual(await Integer.count(), 4);
await sequelize.close();
})();
Generated SQLite update query:
INSERT INTO `IntegerNames` (`value`,`name`) VALUES (2,'TWO'),(3,'THREE'),(7,'SEVEN')
ON CONFLICT (`value`) DO UPDATE SET `name`=EXCLUDED.`name`;
Minimal update example
To complement what was mentioned at https://stackoverflow.com/a/63727529/895245 you don't need to use fixed values for update, you can also use other columns and SQL functions, e.g.:
const assert = require('assert');
const path = require('path');
const { Sequelize, DataTypes, Op } = require('sequelize');
const sequelize = new Sequelize({
dialect: 'sqlite',
storage: 'tmp.' + path.basename(__filename) + '.sqlite',
});
(async () => {
const Inverses = sequelize.define('Inverses',
{
value: {
type: DataTypes.INTEGER,
primaryKey: true,
},
inverse: {
type: DataTypes.INTEGER,
},
name: {
type: DataTypes.STRING,
},
},
{ timestamps: false }
);
await Inverses.sync({force: true})
await Inverses.create({value: 2, inverse: -2, name: 'two'});
await Inverses.create({value: 3, inverse: -3, name: 'three'});
await Inverses.create({value: 5, inverse: -5, name: 'five'});
// Initial state.
assert.strictEqual((await Inverses.findOne({ where: { value: 2 } })).inverse, -2);
assert.strictEqual((await Inverses.findOne({ where: { value: 3 } })).inverse, -3);
assert.strictEqual((await Inverses.findOne({ where: { value: 5 } })).inverse, -5);
assert.strictEqual(await Inverses.count(), 3);
// Update to fixed value.
await Inverses.update(
{ inverse: 0, },
{ where: { value: { [Op.gt]: 2 } } },
);
assert.strictEqual((await Inverses.findOne({ where: { value: 2 } })).inverse, -2);
assert.strictEqual((await Inverses.findOne({ where: { value: 3 } })).inverse, 0);
assert.strictEqual((await Inverses.findOne({ where: { value: 5 } })).inverse, 0);
assert.strictEqual(await Inverses.count(), 3);
// Update to match another column.
await Inverses.update(
{ inverse: sequelize.col('value'), },
{ where: { value: { [Op.gt]: 2 } } },
);
assert.strictEqual((await Inverses.findOne({ where: { value: 2 } })).inverse, -2);
assert.strictEqual((await Inverses.findOne({ where: { value: 3 } })).inverse, 3);
assert.strictEqual((await Inverses.findOne({ where: { value: 5 } })).inverse, 5);
assert.strictEqual(await Inverses.count(), 3);
// Update to match another column with modification.
await Inverses.update(
{ inverse: sequelize.fn('1 + ', sequelize.col('value')), },
{ where: { value: { [Op.gt]: 2 } } },
);
assert.strictEqual((await Inverses.findOne({ where: { value: 2 } })).inverse, -2);
assert.strictEqual((await Inverses.findOne({ where: { value: 3 } })).inverse, 4);
assert.strictEqual((await Inverses.findOne({ where: { value: 5 } })).inverse, 6);
assert.strictEqual(await Inverses.count(), 3);
// A string function test.
await Inverses.update(
{ name: sequelize.fn('upper', sequelize.col('name')), },
{ where: { value: { [Op.gt]: 2 } } },
);
assert.strictEqual((await Inverses.findOne({ where: { value: 2 } })).name, 'two');
assert.strictEqual((await Inverses.findOne({ where: { value: 3 } })).name, 'THREE');
assert.strictEqual((await Inverses.findOne({ where: { value: 5 } })).name, 'FIVE');
assert.strictEqual(await Inverses.count(), 3);
await sequelize.close();
})();
Generated SQLite update queries:
UPDATE `Inverses` SET `inverse`=$1 WHERE `value` > 2
UPDATE `Inverses` SET `inverse`=`value` WHERE `value` > 2
UPDATE `Inverses` SET `inverse`=1 + (`value`)
UPDATE `Inverses` SET `name`=upper(`name`) WHERE `value` > 2
QueryInterface.bulkUpdate minimal example for a migration
What was confusing me is that there is a bulkUpdate for QueryInterface, but there isn't one for Model.
Part of the reason seems to be that there is no way to make non-raw queries in migrations as mentioned at: https://github.com/sequelize/cli/issues/862 With the non-raw queries, we can just use .update as shown above. It appears that this bulkUpdate is a more raw version that is available to migrations.
A full example is shown at: https://github.com/cirosantilli/cirodown/blob/bulk-update-col/web/migrations/20210903000000-user-add-display-name-column.js In this example, I was adding a new displayName column to the database, and I wanted it to be based on the existing username column.
Here's an example of how to do it to make displayName be the same as username. Note how it is not actually using an Array input like Model.bulkCreate, it just runs an SQL UPDATE query instead, so the naming is a bit confusing:
module.exports = {
up: async (queryInterface, Sequelize) => queryInterface.sequelize.transaction(async transaction => {
await queryInterface.addColumn('User', 'displayName',
{
type: Sequelize.STRING(256),
allowNull: false,
defaultValue: '',
},
{transaction},
)
await queryInterface.bulkUpdate('User',
{displayName: queryInterface.sequelize.col('username')},
{}, // optional where clause to select which rows to update
// If empty like this it updates every single row.
{transaction},
)
}),
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('User', 'displayName')
}
};
Related example for creation: Add data in Sequelize migration script?
TODO QueryInterface.bulkInsert + updateOnDuplicate minimal example for a migration
OK, so now I want to actually insert an array during migration, since my modification is just too complex, and cannot be done inside SQL with bulkUpdate: I have to bring data into Node and then push it back.
I would expect the following would work, but it didn't, and given the complete lack of backtraces... I'm not sure why:
module.exports = {
up: async (queryInterface, Sequelize) => queryInterface.sequelize.transaction(async transaction => {
await queryInterface.addColumn('User', 'displayName',
{
type: Sequelize.STRING(256),
allowNull: false,
defaultValue: '',
},
{transaction},
)
const [users] = await queryInterface.sequelize.query('SELECT * FROM "User";', { transaction });
const newUsers = users.map(user =>
{ return { id: user.id, displayName: user.username } }
)
await queryInterface.bulkInsert('User',
newUsers,
{
updateOnDuplicate: ['displayName'],
transaction,
}
)
}),
down: async (queryInterface, Sequelize) => {
await queryInterface.removeColumn('User', 'displayName')
}
};
fails with:
ERROR: Cannot read property 'map' of undefined
Tested on sequelize 6.5.1, sqlite3 5.0.2, node v14.17.0, Ubuntu 21.04.
Use the bulkCreate to bulkUpdate method.
bulkCreate([...], { updateOnDuplicate: ["name"] })
updateOnDuplicate is an array of fields that will be updated when the primary key (or may be unique key) match the row. Make sure you have at least one unique field (let say id) in your model and in the dataArray both for upsert.
For reference refer here
You can, if you want to update a lot of records with the same values! example: I want to update field "activationStatus" for 10 users at 1 time, 1 user = 1 record in DB and I have Array of user IDs then:
User.update({ activationStatus: 'active'}, {
where: {
id: [1,2,3,4,5,6,7,8,9,10]
}
});
it will be analogue of SQL query:
UPDATE User SET activationStatus = 'active' WHERE id IN(1,2,3,4,5,6,7,8,9,10);
you can find more info about Sequelize Operator Aliases HERE