Nodejs sequelize bulk upsert
node.jssequelize.jsnode.js Problem Overview
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.
node.js Solutions
Solution 1 - node.js
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.
Solution 2 - node.js
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.
Solution 3 - node.js
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
})
Solution 4 - node.js
2020 October 1st Update
Sequelize Version: ^6.3.5
The issue still persists. We can't still bulkUpsert with unique composite indexes. bulkCreate with updateOnDuplicates doesn't yet work with unique composite indexes. There are PR's still awaiting to be merged, which may fix this issue:-
https://github.com/sequelize/sequelize/pull/12516
https://github.com/sequelize/sequelize/pull/12547
Workaround
For the time being, if anyone wants a quick workaround, then the following raw query based wrapper can be used by modifying with your own tables attributes, names and data:-
const bulkUpsertIntoTable = async ({ bulkUpsertableData }) => {
try {
/* eslint-disable */
// id column will automatically be incremented if you have set it to auto-increment
const query = `INSERT INTO "Table" ("non_id_attr1", "non_id_attr2", "non_id_attr3","createdAt", "updatedAt") VALUES ${bulkUpsertableData
.map((_) => "(?)")
.join(
","
)} ON CONFLICT ("non_id_attr1","non_id_attr2") DO UPDATE SET "non_id_attr1"=excluded."non_id_attr1", "non_id_attr2"=excluded."non_id_attr2", "non_id_attr3"=excluded."non_id_attr3", "updatedAt"=excluded."updatedAt" RETURNING "id","non_id_attr1","non_id_attr2","non_id_attr3","createdAt","updatedAt";`;
/* eslint-enable */
return await models.sequelize.query(query, {
replacements: bulkUpsertableData,//------> dont forget to pass your data here
type: models.Sequelize.QueryTypes.INSERT,
// transaction:t -----> if required to be done in transaction
});
} catch (error) {
console.error("Bulk Upserting into Table:", error);
throw error;
}
};
Important point is creating the bulkUpsertableData, where it should be Array<Array> ie:- [[]]. Example creation:-
// with reference to above wrapper function
const bulkUpsertableData = Object.keys(myObjectData).map(type => [
myObjectData[type],// -----> non_id_attr1
type, // -----> non_id_attr2
someOtherRandomValue, // -----> non_id_attr3
new Date(), // -----> created_at
new Date(), // -----> updated_at
]);
// response will have all the raw attributes mentioned in RETURNING clause
const upsertedTableResponse = await bulkUpsertIntoTable({ bulkUpsertableData });
Solution 5 - node.js
2020 November 2nd update
Based on @Yedhin answer, here is a more generic solution (typescript):
export const bulkUpsert = async <T extends Model<T>, K extends keyof T>(
items: Partial<T>[],
model: ModelCtor<T>,
conflictKeys: K[],
excludeFromUpdate: K[] = [],
): Promise<[number, number]> => {
if (!items.length) {
return [0, 0];
}
const { tableName, sequelize, name } = model;
if (!sequelize) {
throw new Error(`Sequelize not initialized on ${name}?`);
}
const sample = items[0];
const fields = Object.keys(sample) as K[];
const createFields = `("${fields.join(`","`)}")`;
const updateFields = fields
.filter((field) => ![...excludeFromUpdate, ...conflictKeys].includes(field))
.map((field) => `"${field}"=EXCLUDED."${field}"`)
.join(', ');
const values = items.map(dataToSql(sequelize)).join(',');
const onConflict = `ON CONFLICT ("${conflictKeys.join(`","`)}")`;
const returning = `"${fields.join('","')}"`;
const query = `INSERT INTO "${tableName}" ${createFields} VALUES ${values} ${onConflict} DO UPDATE SET ${updateFields} RETURNING ${returning};`;
return sequelize.query(query, {
replacements: items,
type: QueryTypes.INSERT,
});
};
const valueToSql = (sequelize: Sequelize) => (
value: string | number | boolean | null | Date | string[] | Record<string, unknown>,
): string => {
if (value === null) {
return 'null';
}
if (typeof value === 'boolean') {
return value ? 'true' : 'false';
}
if (typeof value !== 'object' || value instanceof Date) {
return sequelize.escape(value);
}
return sequelize.escape(JSON.stringify(value));
};
const dataToSql = <T extends Node<T>>(sequelize: Sequelize) => (data: Partial<T>): string =>
`(${Object.values(data).map(valueToSql(sequelize)).join(',')})`;
Solution 6 - node.js
2021 September update
Bulk upserting with unique compound indexes now just works in Sequelize v6.4.4.
Solution 7 - node.js
A modified version. Which would do the job.
/**
*
* @param {*} data Raw JSON data
* @param {*} model Sequalize model
* @param {*} fields Columns thare need to be inserted/update.If none passed, it will extract fields from the data.
* @returns response consists of data with type of action(upsert/create) performed for each record.
*/
export const bulkUpert = (data, model, fields = undefined) => {
console.log("****Bulk insertion started****");
if (!data.length) {
return [0, 0];
}
const { name, primaryKeyAttributes } = model;
console.log(name, primaryKeyAttributes, fields);
if (!sequelize) {
throw new Error(`Sequalize not initialized on ${name}`);
}
const extractFields = fields ? fields : Object.keys(data[0]);
const createFields = extractFields.join(", ");
const values = data.map(dataToSql()).join(", ");
const query = `MERGE INTO
[${name}]
WITH(HOLDLOCK)
AS [targetTable]
USING (
VALUES ${values}
)
AS [sourceTable]
(
${createFields}
) ON
${getPrimaryQueryString(primaryKeyAttributes)}
WHEN MATCHED THEN
UPDATE SET
${getUpdateFieldsString(extractFields)}
WHEN NOT MATCHED THEN
INSERT (
${createFields}
)
VALUES
(
${getInsertValuesString(extractFields)}
)
OUTPUT $action, INSERTED.*;`;
return sequelize.query(query);
};
const valueToSQL = () => (value) => {
if (value === null) {
return "null";
}
if (typeof value === "boolean") {
return value ? "true" : "false";
}
if (typeof value !== "object" || value instanceof Date) {
return sequelize.escape(value);
}
return sequelize.escape(JSON.stringify(value));
};
const getPrimaryQueryString = (primaryKeyAttributes) => {
let string = "";
for (let i = 0; i < primaryKeyAttributes.length; i++) {
string += `[targetTable].[${primaryKeyAttributes[i]}] = [sourceTable].[${primaryKeyAttributes[i]}]`;
if (i != primaryKeyAttributes.length - 1) {
string += " AND";
}
}
return string;
};
const getUpdateFieldsString = (fields) => {
let string = "";
for (let i = 0; i < fields.length; i++) {
string += `[targetTable].[${fields[i]}] = [sourceTable].[${fields[i]}]`;
if (i != fields.length - 1) {
string += ", ";
}
}
return string;
};
const getInsertValuesString = (fields) => {
let string = "";
for (let i = 0; i < fields.length; i++) {
string += `[sourceTable].[${fields[i]}]`;
if (i != fields.length - 1) {
string += ", ";
}
}
return string;
};
const dataToSql = () => (data) =>
`(${Object.values(data).map(valueToSQL()).join(",")})`;