`bulkCreate`'s `updateOnDuplicate` option default does not work

[ozhi]

trafficstars

What are you doing?

I am creating entities in bulk with bulkCreate. In case of a conflict on the primary key, I want that entity to be updated with the new values I have given.

await Asset.destroy({ where: {} });

await Asset.create({
    id: 1,
    uuid: 'uuid-1',
    name: 'asset-1',
});

await Asset.bulkCreate([
    {
        id: 2,
        uuid: 'uuid-2',
        name: 'asset-2',
    },
    {
        id: 1,
        uuid: 'uuid-updated',
        name: 'asset-updated',
    },
],{
    // updateOnDuplicate: ['name'],
});

To Reproduce Steps to reproduce the behavior:

1. Define model Asset:

Asset.init(
	{
		id: {
			type: Sequelize.INTEGER,
			primaryKey: true,
		},
		uuid: {
			type: Sequelize.UUID,
		},
		name: {
			type: Sequelize.STRING,
			allowNull: false,
		},
	},
	{
		sequelize,
	}
);

2. Run the code above
3. See a SequelizeUniqueConstraintError

What do you expect to happen?

I wanted

const assets = await Asset.findAll({ where: {} });
console.log(assets.map(a => a.toJSON()));

to produce:

[ { id: 1,
    uuid: 'uuid-updated',
    name: 'asset-updated',
    createdAt: 2019-06-20T13:04:53.000Z,
    updatedAt: 2019-06-20T13:04:53.000Z },
  { id: 2,
    uuid: 'uuid-2',
    name: 'asset-2',
    createdAt: 2019-06-20T13:04:53.000Z,
    updatedAt: 2019-06-20T13:04:53.000Z } ]

What is actually happening?

The mentioned code produces

SequelizeUniqueConstraintError: Validation error
      at Query.formatError (node_modules/sequelize/lib/dialects/mysql/query.js:218:16)
      at Query.handler [as onResult] (node_modules/sequelize/lib/dialects/mysql/query.js:46:23)
      at Query.execute (node_modules/mysql2/lib/commands/command.js:30:14)
      at Connection.handlePacket (node_modules/mysql2/lib/connection.js:449:32)
      at PacketParser.Connection.packetParser.p [as onPacket] (node_modules/mysql2/lib/connection.js:72:12)
      at PacketParser.executeStart (node_modules/mysql2/lib/packet_parser.js:75:16)
      at Socket.Connection.stream.on.data (node_modules/mysql2/lib/connection.js:79:25)
      at addChunk (_stream_readable.js:263:12)
      at readableAddChunk (_stream_readable.js:250:11)
      at Socket.Readable.push (_stream_readable.js:208:10)
      at TCP.onread (net.js:594:20)

The output is as expected if I pass updateOnDuplicate: ['name', 'uuid'] as option to Asset.bulkCreate, but I want the option to default to that by itself.

Environment

Dialect:

• [x] mysql
• [ ] postgres
• [ ] sqlite
• [ ] mssql
• [ ] any Dialect library version: "mysql2": "1.6.5" Database version: 5.7.23 Sequelize version: "5.8.7" Node Version: "8.9.3" OS: Linux Tested with latest release:
• [x] No
• [ ] Yes, specify that version:

[papb]

Thanks for the report.

You said that the default option does not work. Does setting it manually makes it work? (Uncommenting the line in your example, or perhaps using ['uuid', 'name'], does that work?)

[ozhi]

Yes, it does work when I manually provide the property names. However, I want to use the default in order not to have to change it when I add/remove properties to my model.

[sushantdhiman]

There are no defaults with updateOnDuplicate, you have to specify what fields should be updated if there is a conflict

[ozhi]

According to docs: Fields to update if row key already exists (on duplicate key update)? (only supported by MySQL). *By default, all fields are updated*.

[sushantdhiman]

Docs need update

[sushantdhiman]

You can workaround this by setting

updateOnDuplicate: Object.keys(Model.attributes)

[papb]

@sushantdhiman What if we had a new feature updateOnDuplicate: true which internally converted itself to Object.keys(Model.attributes) except that it removes virtual attributes automatically?

[sushantdhiman]

@papb sounds good

[mdcoon]

What exactly should be in the list for updateOnDuplicate property? I tried above Object.keys method but there is no static "attributes" on my model class. There is, however, MyModel.prototype.rawAttributes. I tried using that but it looks like it adds all columns as "EXCLUDE" in the update set. Is that supposed to happen? I don't want them excluded, I want them updated. Should I only add the primary key fields or something to the updateOnDuplicate array??

[Restuta]

I am having similar issues with "Object.keys(Model.attributes)"

[ustun]

We use Object.keys(Model.rawAttributes)

[github-actions[bot]]

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

[yehyaumar]

Is there any update on this issue without using Object.keys(Model.rawAttributes)? Since it is depreciated now.