Nodejs sequelize bulk upsert

Question

Is there a way of doing bulk upsert in sequelize. Also, can I specify which keys to use for checking for duplicates?

I tried following but it didn't work:

Employee.bulkCreate(data, {
    updateOnDuplicate: true
});

Bulk creation works fine though. Above statement always creates new entries in the DB.

Answer 1

From the official sequelizejs reference.

It can be done using bulkCreate with the updateOnDuplicate option.

Like this for example :

Employee.bulkCreate(dataArray, 
    {
        fields:["id", "name", "address"] ,
        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.

Answer 2

Update (Sequelize >= 6)

Sequelize 6.x added support for all UPSERTs on all dialects, so @followtest52's answer is valid for PostgreSQL too.

Original (Sequelize < 6)

Since PostgreSQL is not supported by the answer, the """"best"""" alternative using Sequelize is doing a manual query with the ON CONFLICT statement. Example (Typescript):

const values: Array<Array<number | string>> = [
    [1, 'Apple', 'Red', 'Yummy'],
    [2, 'Kiwi', 'Green', 'Yuck'],
]

const query = 'INSERT INTO fruits (id, name, color, flavor) VALUES ' +
     values.map(_ => { return '(?)' }).join(',') +
     ' ON CONFLICT (id) DO UPDATE SET flavor = excluded.flavor;'

sequelize.query({ query, values }, { type: sequelize.QueryTypes.INSERT })

This would build a query like:

INSERT INTO 
    fruits (id, name, color, flavor)
VALUES 
    (1, 'Apple', 'Red', 'Yummy'),
    (2, 'Kiwi', 'Green', 'Yuck')
ON CONFLICT (id) DO UPDATE SET 
    flavor = excluded.flavor;

Suffice to say, this is not an ideal solution to have to manually build queries, since it defeats the purpose of using sequelize, but if it's one-off query that you don't desperately need, you could use this method.

Answer 3

2019 Update

Works for all dialects provided a certain minimum version is matched

HERE is the reference to the source code for the same

• Note that individual options may or may not work across all dialects For example, updateOnDuplicate will work only on MySQL, MariaDB, SQLite and Postgres

• ignoreDuplicates option will NOT work on MSSQL

Also check this BLOCK of code in the source

if (Array.isArray(options.updateOnDuplicate) && options.updateOnDuplicate.length) {
    options.updateOnDuplicate = _.intersection(
        _.without(Object.keys(model.tableAttributes), createdAtAttr),
        options.updateOnDuplicate
    );
} else {
    return Promise.reject(new Error('updateOnDuplicate option only supports non-empty array.'));
}

updateOnDuplicate has to be an Array, cannot be true or false

So going with the above points, your code should be something like this

Employee.bulkCreate(data, {
    updateOnDuplicate: ['employeeName', 'employeeAge'],
});

UPDATE:

Since someone mentioned it is not working, try this

models.Employee.bulkCreate(items, {
    returning: ['employeeId'],
    ignoreDuplicates: true
  })