ON CONFLICT WHERE for upserts.

[wbourne0]

Issue Creation Checklist

[x] I have read the contribution guidelines

Feature Description

Currently sequelize doesn't support upserts with unique partial indexes.

There's many cases for unique partial indexes - such as a unique constraint on rows that haven't been soft deleted.

For example, a generic membership table with permissions:

const Memberships = sequelize.define(
  'memberships',
  {
    user_id: { type: DataTypes.INTEGER, allowNull: false },
    foreign_key: { type: DataTypes.INTEGER, allowNull: false },
    permissions: { type: DataTypes.ENUM('admin', 'member', 'guest'), allowNull: false }
  },
  {
    deletedAt: 'time_deleted',
    indexes: [
      {
        fields: ['user_id', 'foreign_key'],
        unique: true,
        where: { time_deleted: null },
      },
    ],
  }
);

// Adds a membership for a user as an admin (updates previous membership instead if there is one)
await Memberships.upsert(
  {
    user_id: userId,
    foreign_id: foreignId,
    permissions: 'admin',
  },
  {
    conflictWhere: { time_deleted: null },
  }
);

Is your feature request related to a problem? Please describe.

There's several cases where this comes up:

• Unique usernames, except for deleted users.
• Membership models - something can't belong to the same item multiple times, however it can be fine for the association to be deleted then created later.

Describe the solution you'd like

See the code sample above.

Additional context

Part of #13240.

I also have a PR up at #13411, which is a piece of #13270 (closed)

Feature Request Checklist

Is this feature dialect-specific?

• No. This feature is relevant to Sequelize as a whole.
• Yes. This feature only applies to the following dialect(s): Postgres, Sqlite

Would you be willing to implement this feature by submitting a Pull Request?

#13411

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

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

[mschoeffmann]

+1 for this one

[pavhcoinstats]

Hello

I have to similar problems

It will be good set array of conflicted fields on upsert