Sequelize Postgres - How to use ON CONFLICT for unique?

Question

I am implementing sequelize into my NodeJS application. Before this, I was using a written INSERT query that used ON CONFLICT (field) DO NOTHING to handle not inserting records where a value needed to be unique.

const sql = 'INSERT INTO communications (firstname, lastname, age, department, campus, state, message_uuid) VALUES ($1, $2, $3, $4, $5, $6, $7) ON CONFLICT (message_uuid) DO NOTHING';

const values = [val.firstName, val.lastName, val.age, val.department, val.campus, val.state, message_uuid];

Is there support for this in sequelize where I can define the same thing within a model? Or perhaps a better way to handle it?

Essentially, if a record already exists in the table in the column with message_uuid = 123 and another record try's to insert that has that same value, it ignores it and does nothing.

Did you ever figure this out? I'm wondering how to do the same. — fullStackChris, Aug 03 '20 at 07:03
There's also `ON CONFLICT DO UPDATE` support with `updateOnDuplicate`: https://stackoverflow.com/questions/55531860/sequelize-bulkcreate-updateonduplicate-for-postgresql — Ciro Santilli OurBigBook.com, Feb 21 '22 at 23:10

Lin Du · Accepted Answer · 2020-12-02T06:32:23.927

You can use public static bulkCreate(records: Array, options: Object): Promise<Array> method with options.ignoreDuplicates.

Ignore duplicate values for primary keys? (not supported by MSSQL or Postgres < 9.5)

Besides, it's important to add a unique constraint for the message_uuid field on the model. So that the query will use ON CONFLICT DO NOTHING clause of Postgres.

For example, "sequelize": "^5.21.3" and postgres:9.6:

import { sequelize } from '../../db';
import { Model, DataTypes } from 'sequelize';

class Communication extends Model {}
Communication.init(
  {
    firstname: DataTypes.STRING,
    lastname: DataTypes.STRING,
    age: DataTypes.INTEGER,
    message_uuid: {
      type: DataTypes.INTEGER,
      unique: true,
    },
  },
  { sequelize, tableName: 'communications' },
);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // seed
    await Communication.create({ firstname: 'teresa', lastname: 'teng', age: 32, message_uuid: 123 });
    // test
    await Communication.bulkCreate([{ firstname: 'teresa', lastname: 'teng', age: 32, message_uuid: 123 }], {
      ignoreDuplicates: true,
    });
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

Execution result:

Executing (default): DROP TABLE IF EXISTS "communications" CASCADE;
Executing (default): DROP TABLE IF EXISTS "communications" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "communications" ("id"   SERIAL , "firstname" VARCHAR(255), "lastname" VARCHAR(255), "age" INTEGER, "message_uuid" INTEGER UNIQUE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'communications' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "communications" ("id","firstname","lastname","age","message_uuid") VALUES (DEFAULT,$1,$2,$3,$4) RETURNING *;
Executing (default): INSERT INTO "communications" ("id","firstname","lastname","age","message_uuid") VALUES (DEFAULT,'teresa','teng',32,123) ON CONFLICT DO NOTHING RETURNING *;

Check the database, there is only one row as expected.

node-sequelize-examples=# select * from communications;
 id | firstname | lastname | age | message_uuid 
----+-----------+----------+-----+--------------
  1 | teresa    | teng     |  32 |          123
(1 row)

score 0 · Answer 2 · answered Jul 06 '22 at 05:56

see the new UPSERT feature in Sequelize v6:

https://sequelize.org/api/v6/class/src/model.js~model#static-method-upsert

Implementation details:

MySQL - Implemented with ON DUPLICATE KEY UPDATE`

PostgreSQL - Implemented with ON CONFLICT DO UPDATE. If update data contains PK field, then PK is selected as the default conflict key. Otherwise first unique constraint/index will be selected, which can satisfy conflict key requirements.

SQLite - Implemented with ON CONFLICT DO UPDATE

MSSQL - Implemented as a single query using MERGE and WHEN (NOT) MATCHED THEN

score -2 · Answer 3 · answered Jul 12 '19 at 16:45

-2

as second argument of Model.create you can provide onConflict prop, please read the documentation

answered Jul 12 '19 at 16:45

VitoMakarevich

439
2
5

Here's github search results for "onConflict" within sequelize source code: https://github.com/search?l=&q=onConflict++repo%3Asequelize%2Fsequelize&type=Code Here's proof something like it exists, though: https://github.com/sequelize/sequelize/blob/master/lib/dialects/postgres/index.js#L45 – Tom Nov 07 '19 at 20:33

Sequelize Postgres - How to use ON CONFLICT for unique?

3 Answers3