Sqlite: bulkCreate with updateOnDuplicate fails when the model contains a composite key.

[jpvanoosten]

Related issue: #11432

Issue Description

The bulkCreate fails if the model contains a composite key and doesn't explicitly define a primary key.

The upsertKeys array is emptied when the model contains a composite key since the length of the fields array of the compositeKey object will be of size > 1 (which causes this filter to skip it):

sequelize/lib/model.js

Line 2705 in 3a60069

options.upsertKeys = _.chain(model.uniqueKeys).values().filter(c => c.fields.length === 1).map('column').value();

My model looks like this:

"use strict";
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    "User",
    {
      username: { type: DataTypes.STRING, unique: "compositeKey" },
      discordId: { type: DataTypes.STRING, unique: "compositeKey" }
    },
    {}
  );
  User.associate = function(models) {
    // associations can be defined here
  };
  return User;
};

In this case, the model.uniqueKeys object only contains a single compositeKey object.
Shouldn't the model.uniqueKeys object also contain the automatically generated id primary key for this model?

Changing the model to this works:

"use strict";
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    "User",
    {
      username: { type: DataTypes.STRING, unique: true },
      discordId: { type: DataTypes.STRING, unique: true }
    },
    {}
  );
  User.associate = function(models) {
    // associations can be defined here
  };
  return User;
};

What are you doing?

Upserting users for testing:

models.sequelize
  .sync()
  .then(async () => {
    if (process.env.UPSERT_TEST_USERS === "true") {
      const numTestUsers = parseInt(process.env.NUM_TEST_USERS || 50);
      const testUsers = new Array(numTestUsers).fill(null).map((user, i) => ({
        id: i,
        username: `Test${i}`,
        discordId: `discordId${i}`
      }));

      await models.User.bulkCreate(testUsers, {
        updateOnDuplicate: ["username", "discordId"]
      });
    }
  })
  .then(() => {
    debug("Database synchronization succeeded.");

    /**
     * Listen on provided port, on all network interfaces.
     */
    server.listen(port);
    server.on("error", onError);
    server.on("listening", onListening);
  })
  .catch(err => {
    debug(`Database synchronization failed: ${err}`);
  });

What do you expect to happen?

I expect the bulkCreate method to succeed and upsert the test users to the database.

What is actually happening?

Doing this with this model results in the following error:

SequelizeDatabaseError: SQLITE_ERROR: near ")": syntax error

The error is referring to the ON CONFLICT() since it is an error to have an empty ON CONFLICT clause. This should be:

ON CONFLICT('username', 'discordId')

Additional context

Add any other context or screenshots about the feature request here.

Environment

• Sequelize version: 5.19.3
• Node.js version: v10.16.3
• Operating System: Windows 10
• If TypeScript related: TypeScript version: N/A

Issue Template Checklist

How does this problem relate to dialects?

• I think this problem happens regardless of the dialect.
• I think this problem happens only for the following dialect(s): Postgres, Sqlite
• I don't know, I was using PUT-YOUR-DIALECT-HERE, with connector library version XXX and database version XXX

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[papb]

Hello, thanks for the report!

Yes, I have the time but I don't know how to start, I would need guidance.

Great! What kind of guidance do you want?

[sushantdhiman]

Fixed with #11984