feat(upsert): return upserted record with options.returning=true

[sushantdhiman]

Pull Request check-list

Please make sure to review and check all of these items:

• Does npm run test or npm run test-DIALECT pass with this change (including linting)?
• Does the description below contain a link to an existing issue (Closes #[issue]) or a description of the issue you are solving?
• Have you added new tests to prevent regressions?
• Is a documentation update included (if this change modifies existing APIs, or introduces new ones)?
• Did you follow the commit message conventions explained in CONTRIBUTING.md?

Description of change

Close #3354

Sequelize still doesnot support ON CONFLICT, but with using RETURNING its now possible for return upserted record.

// current behaviour
const created = await Model.upsert(....values....);

// with options.returning = false
const created = await Model.upsert(....values...., { returning: false });

// with options.returning = true , new feature
const [record, created] = await Model.upsert(....values...., { returning: true });

[codecov]

Codecov Report

Merging #8924 into master will increase coverage by 0.01%.
The diff coverage is 100%.

[sushantdhiman]

In v5, we can set this Promise<record, created> by setting returning=true by default. One caveat is null is now first argument for other dialects. May be should set this as Promise<created, record>, but that doesn't match with other method's API. Any ideas?

[sushantdhiman]

I have added support for MSSQL as well, so now may be current order make sense.

[sushantdhiman]

In issue #3354 there is discussion about MySQL support, but I find that LAST_INSERTED_ID is still limited. It wont work for any non incrementing primary keys like STRING, UUID etc. So I haven't added any support for MySQL.

Currently we support MSSQL and Postgres for this feature

[abdabsi]

In issue #3354 there is discussion about MySQL support, but I find that LAST_INSERTED_ID is still limited. It wont work for any non incrementing primary keys like STRING, UUID etc. So I haven't added any support for MySQL.

Currently we support MSSQL and Postgres for this feature

If they're using non-incrementing primary keys, they most probably won't need LAST_INSERTED_ID. Those who use auto-incremented primary keys (absolute majority) on MySql (#1 rdbms) have no use of the current upsert method. Please fix it! :)