Upsert does not always find/handle unique index

[JacobLey]

Issue Description

What are you doing?

class User extends Model {
}
User.init({
    name: DataTypes.STRING,
    someVal: DataTypes.INTEGER,
}, {
   indexes: [
       fields: [fn('LOWER', 'name')],
       unique: true,
       where: { someVal: 123 },
       name: '<some-name>',
   ],
})

const [{ id }] = await User.upsert({
    name: 'abc',
    someVal: 123,
}, {
    fields: ['name'],
    returning: ['id'],
});
const [{ id: id2 }] = await User.upsert({
    name: 'abc',
    someVal: 123,
}, {
    fields: ['name'],
    returning: ['id'],
});
console.log(id === id2) // true

What do you expect to happen?

In the above code, I would expect the User model to be upserted based on the partial unique index.

What is actually happening?

Sequelize fails to detect the unique index and upserts based on primary key. So Postgres rejects the insert due to unique constraint without explicit handling.

Additional context

To my understanding, here are the general causes to this failure:

The first is that the index detection only looks for string literals for the field name https://github.com/sequelize/sequelize/blob/main/lib/dialects/abstract/query-interface.js#L789

So when an index uses functions, it cannot find the values (because fn() !== fn())

The second is that even if the unique-index selection logic found util methods, it would still fail to stringify the values (currently it only expects string literals).
https://github.com/sequelize/sequelize/blob/main/lib/dialects/abstract/query-generator.js#L186
(Will open PR for this fix)

And third is that only the fields are used in declaring the conflict. An index with a where clause should also have that included.
See index_predicate here https://www.postgresql.org/docs/13/sql-insert.html

Environment

• Sequelize version: 6.6.2
• Node.js version: 14.16.1
• Operating System: MacOS

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 (which supports partial index with functions). May impact other languages with similar functionality
• 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.

I will open a PR for the second part, but the first and third are a bit trickier.

For the first, I think the best solution is to provide the name of the index in the options, and map that to the found index. Note the generated SQL should still use unique index inference for the reason stated in Postgres docs, but those issues don't apply to Sequelize.

For the third, I think the insert query options needs to take a where option as well. And when present, stringify it and attach to the ON CONFLICT statement.

[wbourne0]

Can you break this up into multiple issues? I'm creating an issue for the partial index part, but ideally each step would be its own issue.

Edit: Here's the issue for the ON CONFLICT WHERE part: #13412

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