Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does Sequelize support Insert on duplicate key update?

Is there a Model or Instance method that will perform an insert or update, depending on the whether or not the record exists? Preferably making use of MySQL's "INSERT ON DUPLICATE KEY UPDATE" syntax?

like image 407
ThePizzle Avatar asked Jan 31 '14 16:01

ThePizzle


People also ask

What is insert on duplicate key update?

INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.

How do I insert data into a table using Sequelize?

The create() method is used to insert a single row into your SQL table. When you need to insert multiple rows at once, you need to use the bulkCreate() method instead. Finally, you can also write and execute a raw SQL statement using the sequelize. raw() method.

How do I update bulk in Sequelize?

While Sequelize doesn't provide a bulkUpdate() method, both update() and bulkCreate() methods allow you to update multiple rows with a single method. When you need to update multiple rows with different values, you can use the bulkCreate() method.

What does upsert do in Sequelize?

The upsert() method accepts an object of data with the property keys serving as the column names and the property values as the column values. The method would then return an array of two elements: The instance the Model where you call the method, returning the new/updated row.


2 Answers

they have recently added this feature upsert or insertOrUpdate

  /** 
  * Insert or update a single row. An update will be executed if a row
  * which matches the supplied values on either the primary key or a unique
  * key is found. Note that the unique index must be defined in your sequelize
  * model and not just in the table. Otherwise you may experience a unique 
  * constraint violation, because sequelize fails to identify the row that
  * should be updated.
  */

  Model.upsert({uniqueKey: 1234, name: 'joe'}).then(function () {
        // cheer for joy
  });
like image 87
Chad Scira Avatar answered Oct 12 '22 11:10

Chad Scira


Sequelize does not currently support upsert - I believe it was hard to introduce a good cross dialect solution.

You can however do a findOrCreate and a updateAttributes.

Edit: Sequelize does now support UPSERT with a pretty decent cross dialect implementation, see: https://github.com/sequelize/sequelize/pull/2518

like image 34
Mick Hansen Avatar answered Oct 12 '22 11:10

Mick Hansen