How to ignore SequelizeUniqueConstraintError in Sequelize?

Question

Using the example from the documentation, everything works well only at the first insertion. After the second and subsequent, errors appear.

const Product = this.sequelize.define('Product', {
  title: Sequelize.STRING
});
const Tag = this.sequelize.define('Tag', {
  name: Sequelize.STRING,
  unique: true
});
const ProductTag = this.sequelize.define('ProductTag', {
  product_id: Sequelize.INTEGER,
  tag_id: Sequelize.INTEGER
});

Product.belongsToMany(Tag, {through: 'ProductTag', as: 'tag'});
Tag.belongsToMany(Product, {through: 'ProductTag'});

The first insert works fine.

Product.create({
  title: 'Chair',
  tag: [
    { name: 'Alpha'},
    { name: 'Beta'}
  ]
}, {
  include: [{
    model: Tag,
    as: 'tag'
  }]
})

SQL logs

INSERT INTO "Product" ("id","title","created_at","updated_at") VALUES (DEFAULT,'Chair','2019-02-25 12:51:50.802 +00:00','2019-02-25 12:51:50.802 +00:00') RETURNING *;
INSERT INTO "Tag" ("id","name","created_at","updated_at") VALUES (DEFAULT,'Alpha','2019-02-25 12:51:51.061 +00:00','2019-02-25 12:51:51.061 +00:00') RETURNING *;
INSERT INTO "Tag" ("id","name","created_at","updated_at") VALUES (DEFAULT,'Beta','2019-02-25 12:51:51.061 +00:00','2019-02-25 12:51:51.061 +00:00') RETURNING *;
INSERT INTO "ProductTag" ("product_id","tag_id","created_at","updated_at") VALUES (1,1,'2019-02-25 12:51:51.068 +00:00','2019-02-25 12:51:51.068 +00:00') RETURNING *;
INSERT INTO "ProductTag" ("product_id","tag_id","created_at","updated_at") VALUES (1,2,'2019-02-25 12:51:51.072 +00:00','2019-02-25 12:51:51.072 +00:00') RETURNING *;

Subsequent insertions Product.create(...) produce errors SequelizeUniqueConstraintError

Key "(name)=(Alpha)" already exists.

How to make it so that if the tag already exists, it took the ID of the existing one and ignore the error?

You've tagged this question as both [mysql] and [postgresql] - which RDBMS are you actually using? — Scoots, Feb 25 '19 at 13:13
You may want to look at [upsert](http://docs.sequelizejs.com/class/lib/model.js~Model.html#static-method-upsert). — TGrif, Feb 25 '19 at 13:13
Thanks for the answer, @TGrif, but using upsert, only the product is added. Tags are not added. — Hello World, Feb 25 '19 at 13:19
What's the behaviour, because on your model definition you have tags as `unique: true`. That means that on your all records you can only have one of each value. — Ellebkey, Feb 25 '19 at 17:27
Thanks for the answer, @Ellebkey. Yes, each tag is unique. But why sequelizejs does not take into account this condition? Instead of creating a new record, why doesn't it make upsert instead of create? — Hello World, Feb 25 '19 at 17:58
Because your including the tag model on the Product creation, there for you are creating also a new Tag. And now I see that you defined a M:N relation so you should chekc [this](http://docs.sequelizejs.com/class/lib/associations/belongs-to-many.js~BelongsToMany.html) documentation. — Ellebkey, Feb 25 '19 at 18:17
I edit post. Yes, I have Product belongsToMany Tag, and Tag belongsToMany Product. — Hello World, Feb 25 '19 at 19:43
You can use findOrCreate function of Sequlize. But for this, you will be needing to separately do this operation after product is created — Rohit Dalal, Feb 26 '19 at 08:06

Ciro Santilli OurBigBook.com · Answer 1 · 2021-11-29T09:07:53.637

bulkCreate with ignoreDuplicates: true

This might be of interest. Tested on sequelize@6.5.1 sqlite3@5.0.2 this produces a single INSERT OR IGNORE INTO, and PostgreSQL produces ON CONFLIC DO NOTHING.

Note the problem of missing IDs further discussed below.

const assert = require('assert');
const path = require('path');
const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'tmp.' + path.basename(__filename) + '.sqlite',
  define: {
    timestamps: false
  },
});
(async () => {
const Tag = sequelize.define('Tag', {
  name: {
    type: DataTypes.STRING,
    unique: true,
  },
});
await sequelize.sync({force: true})
await Tag.create({name: 't0'})

// Individual create does not have the option for some reason.
// Apparently you're just supposed to catch.
// https://github.com/sequelize/sequelize/issues/4513
//await Tag.create({name: 't0', ignoreDuplicates: true})

// SQLite: INSERT OR IGNORE INTO as desired.
// IDs may be missing here.
const tags = await Tag.bulkCreate(
  [
    {name: 't0'},
    {name: 't1'},
    {name: 't1'},
    {name: 't2'},
  ],
  {
    ignoreDuplicates: true,
  }
)
const tagsFound = await Tag.findAll({order: [['name', 'ASC']]})
assert.strictEqual(tagsFound[0].name, 't0')
assert.strictEqual(tagsFound[1].name, 't1')
assert.strictEqual(tagsFound[2].name, 't2')
assert.strictEqual(tagsFound.length, 3)

await sequelize.close();
})();

Missing IDs problem

The return value of Tag.bulkCreate does not contain the IDs generated by SQLite during INSERT INTO unfortunately however as mentioned at: https://github.com/sequelize/sequelize/issues/11223#issuecomment-864185973

This is likely because neither SQLite nor PostgreSQL return the row in the underlying queries:

This breaks the use case mentioned by OP of assigning tags to a product immediately from the return, because we would need the IDs for the through table.

I'm not sure how it compares to OPs findOrCreate option, but I think it will be faster to just do a second find/SELECT afterwards, on my Article has Tags model "update model" controller I went for:

  await Promise.all([
    req.app.get('sequelize').models.Tag.bulkCreate(
      tagList.map((tag) => {return {name: tag}}),
      {ignoreDuplicates: true}
    ).then(tags => {
      // IDs may be missing from the above, so we have to do a find.
      req.app.get('sequelize').models.Tag.findAll({
        where: {name: tagList}
      }).then(tags => {
        return article.setTags(tags)
      })
    }),
    article.save()
  ])

This is a GREAT workaround for most cases. – adamsfamily Nov 29 '21 at 08:03 — adamsfamily, Nov 29 '21 at 08:03

score 1 · Accepted Answer · answered Feb 28 '19 at 19:41

My solution.

Add method updateOrCreate

Product.updateOrCreate = function (options) {
    return this.findOrCreate(options).then(res => {
        let [row, created] = res;
        if (created) return [row, created];
        return row.update(options.defaults, options.transaction)
            .then(updated => [updated, created]);
    })
};
Tag.updateOrCreate = function (options) {
    return this.findOrCreate(options).then(res => {
        let [row, created] = res;
        if (created) return [row, created];
        return row.update(options.defaults, options.transaction)
            .then(updated => [updated, created]);
    })
};

use

let data = {
    title: 'Chair',
    tag: [
        {name: 'Alpha'},
        {name: 'Beta'}
    ]
};
return sequelize.transaction().then(t => {
    return Product.updateOrCreate({
        where: {title: data.title},
        defaults: data,
        transaction: t
    }).then(res => {
        let [product] = res;
        return Promise.all(data.tag.map(tag => {
            return Tag.updateOrCreate({
                where: {name: tag.name},
                defaults: tag,
                transaction: t
            }).then(res => {
                let [tag] = res;
                return Promise.resolve(tag);
            }).catch(err => Promise.reject(err.message));
        })).then(() => {
            t.commit();
            sequelize.close();
        }).catch(err => Promise.reject(err));
    }).catch(err => {
        t.rollback();
        sequelize.close();
    });
});

How to ignore SequelizeUniqueConstraintError in Sequelize?

2 Answers2

Linked