upsert using wrong ON CONFLICT attribute when using composite unique index

[oriuken]

Bug Description

When using upsert (with Postgres) with a model that has a composite unique index, a Validation Error is being thrown since the query is using the model primary key on "ON CONFLICT" instead of the composite unique index.

Here are some code examples

Model Definition:

Entry.init({
    id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
    subjectId: {type: DataTypes.INTEGER, allowNull: false},
    objectId: {type: DataTypes.INTEGER, allowNull: false},
    mask: {type: DataTypes.STRING, allowNull: false},
}, {
    indexes: [
        { fields: ["subjectId", "objectId"], unique: true},
    ],
    sequelize,
    modelName: .....,
    tableName: .....,
});

Migration file:

await queryInterface.createTable("entry", {
    id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
    subject_id: {
      type: DataTypes.INTEGER,
      references: {
        model: "subject",
        key: "id",
      },
      onUpdate: "CASCADE",
      onDelete: "CASCADE",
      allowNull: false,
    },
    object_id: {
      type: DataTypes.INTEGER,
      references: {
        model: "object",
        key: "id",
      },
      onUpdate: "CASCADE",
      onDelete: "CASCADE",
      allowNull: false,
    },
    mask: {type: DataTypes.STRING, allowNull: false},
    created_at: {type: DataTypes.DATE, allowNull: false},
    updated_at: {type: DataTypes.DATE, allowNull: false},
  });

  await queryInterface.addIndex("entry", { fields: ["subject_id", "object_id"], unique: true });

Service code (where we actually make the call):

  async setEntry(params): Promise<AclEntry> {
    const [entry] = await Entry.upsert({
        subjectId: params.subjectId,
        objectId: params.objectId,
        mask: params.mask
    }, {
        returning: true,
        logging: console.log,

    });

    return entry;
}

I can also confirm that in Postgres database, an index was created for the composite unique index by the following query:

CREATE UNIQUE INDEX entry_subject_id_object_id ON public.entry USING btree (subject_id, object_id)

The Issue

The previous upsert call is throwing the next error when there is already a record with subjectId and objectId:

Validation error {"message":"Validation error","name":"SequelizeUniqueConstraintError"...

And the SQL query that is being run is as follows:

INSERT INTO "public"."entry" ("subject_id","object_id","mask") VALUES (1,2,"RW") ON CONFLICT ("id") DO UPDATE SET "subject_id"=EXCLUDED."subject_id","object_id"=EXCLUDED."object_id","mask"=EXCLUDED."mask" RETURNING "id","subject_id","object_id","mask";

What do you expect to happen?

1. Row with subject_id: 1 and object_id:2 to be updated instead of getting a Validation Error.
2. Generatede SQL query to reference "subject_id" and "object_id" at "ON CONFLICT" instead of "id" like shown below:

   INSERT INTO "public"."entry" ("subject_id","object_id","mask") VALUES (1,2,"RW") ON CONFLICT ("subject_id","object_id") DO UPDATE SET "subject_id"=EXCLUDED."subject_id","object_id"=EXCLUDED."object_id","mask"=EXCLUDED."mask" RETURNING "id","subject_id","object_id","mask";

   This last query I tried it on Postgres directly using "subject_id" and "object_id" at "ON CONFLICT" instead of "id" and the record was successfully updated !

What is actually happening?

The previous upsert call is throwing the next error: Validation error {"message":"Validation error","name":"SequelizeUniqueConstraintError"...

And the SQL query that is being run is as follows:

INSERT INTO "public"."entry" ("subject_id","object_id","mask") VALUES (1,2,"RW") ON CONFLICT ("id") DO UPDATE SET "subject_id"=EXCLUDED."subject_id","object_id"=EXCLUDED."object_id","mask"=EXCLUDED."mask" RETURNING "id","subject_id","object_id","mask";

Environment

• Sequelize version: 6.9.0
• Node.js version: 14.17.0
• TypeScript version: 4.4.4

[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. 🙂

[acsaadk]

Still not working