bulkCreate with updateOnDuplicate not returning primary key for new record

[VidhiyaElango]

What are you doing?

let data = [{id:1, name:'name1', value:'value1'},{id:2, name:'name2', value:'value2'},{id:'', name:'name3',value:'value3'}];
Model.bulkCreate(data, {updateOnDuplicate:['name', 'value']}).then((response)=>{console.log(response)})

To Reproduce
Steps to reproduce the behavior:
model:

{
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        unique: true
    },
    name: Sequelize.TEXT,
    value: Sequelize.TEXT
}

What do you expect to happen?

To return the primary key for all objects

What is actually happening?

I have inserted the first 2 data earlier and while updating have added the 3rd object to the array.
while getting response i am getting primary key's for first 2 data and null for newly added object.
response :

[
    {id: 1, name: 'name1', value: 'value1'},
    {id: 2, name: 'name2', value: 'value2'},
    {id: null, name: 'name3', value: 'value3'}
]

Environment

Dialect: - mysql
Dialect library version: ^2.16.0
Sequelize version: ^5.2.0
Node Version: 10.15.3
OS: linux

[papb]

Can you check with another dialect, such as SQLite for instance?

[VidhiyaElango]

Hi papb,
I have tried with SQLite too and the same problem exits.
But i have found a solution by settting the value of the id in the model

Model:

{
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        unique: true
         set() {
                    let value = this.getDataValue('id')
                    this.setDataValue('id', value);
          },
    },
    name: Sequelize.TEXT,
    value: Sequelize.TEXT
}

Thank you for your response.

[VidhiyaElango]

But if i set the value in the model it is duplicating all the records and not updating

[papb]

I see...

You posted only a code snippet, can you provide a SSCCE (also known as MCVE/reprex) to easily reproduce it all by copy-pasting?

[sushantdhiman]

MySQL only returns ids for records which got inserted with updateOnDuplicate. I don't think we can reliably support this unless MySQL adds support for RETURNING * equivalent

const Sequelize = require('./index');
const sequelize = require('./test/support').createSequelizeInstance();
const Op = Sequelize.Op;

const Model = sequelize.define('Model', {
  name: Sequelize.STRING,
  value: Sequelize.STRING
});

(async () => {
  await Model.sync({ force: true });

  await Model.bulkCreate([
    {id: 1, name: 'name1', value: 'value1'},
    {id: 2, name: 'name2', value: 'value2'}
  ]);

  const result = await Model.bulkCreate([
    {id: 1, name: 'name1', value: 'value1'},
    {id: 2, name: 'name2', value: 'value2'},
    { name: 'name3', value: 'value3'}
  ], {
    updateOnDuplicate: ['name', 'value']
  });

  console.log(JSON.stringify(result, null, 2));
})();

Executing (default): DROP TABLE IF EXISTS `Models`;
Executing (default): CREATE TABLE IF NOT EXISTS `Models` (`id` INTEGER NOT NULL auto_increment , `name` VARCHAR(255), `value` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `Models`
Executing (default): INSERT INTO `Models` (`id`,`name`,`value`,`createdAt`,`updatedAt`) VALUES (1,'name1','value1','2019-07-27 09:02:18','2019-07-27 09:02:18'),(2,'name2','value2','2019-07-27 09:02:18','2019-07-27 09:02:18');
[ { id: 2 }, { id: 3 } ]
Executing (default): INSERT INTO `Models` (`id`,`name`,`value`,`createdAt`,`updatedAt`) VALUES (1,'name1','value1','2019-07-27 09:02:18','2019-07-27 09:02:18'),(2,'name2','value2','2019-07-27 09:02:18','2019-07-27 09:02:18'),(NULL,'name3','value3','2019-07-27 09:02:18','2019-07-27 09:02:18') ON DUPLICATE KEY UPDATE `name`=VALUES(`name`),`value`=VALUES(`value`);
[ { id: 3 } ]
[
  {
    "id": 1,
    "name": "name1",
    "value": "value1",
    "createdAt": "2019-07-27T09:02:18.974Z",
    "updatedAt": "2019-07-27T09:02:18.974Z"
  },
  {
    "id": 2,
    "name": "name2",
    "value": "value2",
    "createdAt": "2019-07-27T09:02:18.974Z",
    "updatedAt": "2019-07-27T09:02:18.974Z"
  },
  {
    "id": null,
    "name": "name3",
    "value": "value3",
    "createdAt": "2019-07-27T09:02:18.974Z",
    "updatedAt": "2019-07-27T09:02:18.974Z"
  }
]

[cirosantilli]

Possibly the same problem on bulkInsert ignoreDuplicates: true, sequelize@6.5.1 sqlite3@5.0.2

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'})
const tags = await Tag.bulkCreate(
  [
    {name: 't0'},
    {name: 't1'},
    //{name: 't1'},
    {name: 't2'},
  ],
  {
    ignoreDuplicates: true,
  }
)
console.error(tags);
await sequelize.close();
})();

output:

Executing (default): DROP TABLE IF EXISTS `Tags`;
Executing (default): DROP TABLE IF EXISTS `Tags`;
Executing (default): CREATE TABLE IF NOT EXISTS `Tags` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` VARCHAR(255) UNIQUE);
Executing (default): PRAGMA INDEX_LIST(`Tags`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_Tags_1`)
Executing (default): INSERT INTO `Tags` (`id`,`name`) VALUES (NULL,$1);
Executing (default): INSERT OR IGNORE INTO `Tags` (`id`,`name`) VALUES (NULL,'t0'),(NULL,'t1'),(NULL,'t2');
[
  Tag {
    dataValues: { id: 3, name: 't0' },
    _previousDataValues: { name: 't0', id: 3 },
    _changed: Set(0) {},
    _options: {
      isNewRecord: true,
      _schema: null,
      _schemaDelimiter: '',
      include: undefined
    },
    isNewRecord: false
  },
  Tag {
    dataValues: { id: 4, name: 't1' },
    _previousDataValues: { name: 't1', id: 4 },
    _changed: Set(0) {},
    _options: {
      isNewRecord: true,
      _schema: null,
      _schemaDelimiter: '',
      include: undefined
    },
    isNewRecord: false
  },
  Tag {
    dataValues: { id: null, name: 't2' },
    _previousDataValues: { name: 't2', id: null },
    _changed: Set(0) {},
    _options: {
      isNewRecord: true,
      _schema: null,
      _schemaDelimiter: '',
      include: undefined
    },
    isNewRecord: false
  }
]

so note how t2 has null id.

However, if I inspect the table on the shell after running the script:

sqlite3 tmp.ignore_duplicates.js.sqlite .dump

we see that t2 does have it:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE `Tags` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` VARCHAR(255) UNIQUE);
INSERT INTO Tags VALUES(1,'t0');
INSERT INTO Tags VALUES(3,'t1');
INSERT INTO Tags VALUES(4,'t2');
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('Tags',4);
COMMIT;

SQLite just added RETURNING on 3.35 it seems: https://sqlite.org/forum/info/a4dde39b614ec0b2 but unfortunately it does not return anything if the INSERT fails due to the conflict, related: https://stackoverflow.com/questions/13244393/sqlite-insert-or-ignore-and-return-original-rowid

More context: https://stackoverflow.com/questions/54866952/how-to-ignore-sequelizeuniqueconstrainterror-in-sequelize/68040264#68040264

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[aneeq-ur-rehman4]

Setting individualHooks: true works perfectly.
Here is an example:

 const result = await Model.bulkCreate([
    {id: 1, name: 'name1', value: 'value1'},
    {id: 2, name: 'name2', value: 'value2'},
    { name: 'name3', value: 'value3'}
  ], {
    updateOnDuplicate: ['name', 'value'],
    individualHooks: true
  });