Nodejs sequelize bulk upsert

Solution 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.

Solution 2:

Update

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

Original

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:

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:

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:

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(',')})`;