updateOnDuplicate in bulkCreate always uses id in On Conflict(), when used with another unique column.

[RahmatAliMalik5]

Issue

I have solved this issue for me but want to point out to the devs so they can make it work for all scenarios.

updateOnDuplicate not works as expected when used in bulkCreate with unique column.

Versions

• sequelize: 6.21.0
• sequelize-typescript: 2.1.3
• typescript: 4.7.4

Issue type

• [x] bug report
• [ ] feature request

Actual behavior

When we use updateOnDuplicate in bulkCreate on a table which have unique column, sequelize always use id in On Conflict(id). eg:

Model:

@Table({ modelName: 'transaction' })
export class TransactionModel extends Model {
  @Column({
    type: DataType.INTEGER,
    allowNull: false,
    autoIncrement: true,
    unique: true,
    primaryKey: true,
  })
  override id: number;

  @Column(DataType.BOOLEAN)
  status: boolean;

  @Unique
  @Column(DataType.STRING)
  hash: string;
}

Sequelize Code:

const txns = [{
    hash: '0x1cc469dc90a542d0cb8ca1046874d6f7dacda2780641984dc9f4289d2913e4d6',
    status: true,
}];

await this._transactionModel.bulkCreate(txns, {
    updateOnDuplicate: ['status'],
});

Generated Query:

INSERT INTO "transactions" ("id", "hash", "status")
VALUES (DEFAULT, '0x1cc469dc90a542d0cb8ca1046874d6f7dacda2780641984dc9f4289d2913e4d6', 'true')
ON CONFLICT ("id") DO UPDATE SET "status"=EXCLUDED."status" RETURNING "id","hash", "status";

This gives, SeqeulizeUniqueConstraintError if a row with that hash is existed.

Expected behavior

It should be run without any problem and should update the status of existing row with the latest one. The generated query should be as follows (notice On Conflict() part):

INSERT INTO "transactions" ("id", "hash", "status")
VALUES (DEFAULT, '0x1cc469dc90a542d0cb8ca1046874d6f7dacda2780641984dc9f4289d2913e4d6', 'true')
ON CONFLICT ("hash") DO UPDATE SET "status"=EXCLUDED."status" RETURNING "id","hash", "status";

Related code

I have solved this by taking unique column with @Unique decorator in first place before id column. It solved the problem and now generated query is using On Conflict("hash")

@Table({ modelName: 'transaction' })
export class TransactionModel extends Model {

  @Unique
  @Column(DataType.STRING)
  hash: string;                       // <------------------- After

  @Column({
    type: DataType.INTEGER,
    allowNull: false,
    autoIncrement: true,
    unique: true,
    primaryKey: true,
  })
  override id: number;

  @Column(DataType.BOOLEAN)
  status: boolean;


// @Unique
// @Column(DataType.STRING)
//  hash: string;                     <------------------ Before
}