fix: upsert should find unique index created by one-to-one relation

[joeflateau]

Description of change

Fixes #8587

One-to-one relations create a unique index, that index should be suitable for an upsert conflict target.

Pull-Request Checklist

• Code is up-to-date with the master branch
• npm run lint passes with this change
• npm run test passes with this change
• This pull request links relevant issues as Fixes #0000
• There are new or updated unit tests validating the change
• Documentation has been updated to reflect this change
• The new commits follow conventions explained in CONTRIBUTING.md

[pleerock]

Thanks for the fix! Quick question: do we really need a runtime check for column uniqueness? Usually we delegate everything the db, and if there is something that db cannot handle - it throws an error.

[joeflateau]

@pleerock I think it's important. It's really there to protect MySQL/MariaDB users since those DB engines don't let you specify the conflict columns. If you "upsert" and there's no unique constraint for the columns you specify as your conflict columns then Postgres will throw an error but MySQL will just insert it and move on.

[pleerock]

if that's natural MySQL behaviour, and users use MySQL's UPSERT with it's natural behaviour (orm is just a wrapper around what rdbms provides) then why do we need to prevent users from doing what they wanna do?

[joeflateau]

my philosophy is that I like to catch footguns like this where the abstraction of "upsert" could cause some unexpected behavior due to db engine differences. your call though. I can remove the check altogether if you prefer that

[pleerock]

The problem is that we definitely cannot catch all the errors - and when we handle some errors one way and other errors another way - it brings a confusion.

Also, there are users who use ORM with exist database without entities synchronization. Such users don't define entity indices and uniques. They simply define entities and execute queries against exist database. Current approach fail for them.

I can remove the check altogether if you prefer that

Yes, I would prefer these checks to be handles by database if it's possible. If you don't have a time or wish to do that - no worries, I'll merge this PR for now and someday we might refactor this code.

[pleerock]

thank you a lot

[Robert-Rendell-THE]

For anyone that wants typing, I did this:

const conflictPaths: (keyof MyEntity)[] = ['columnA', 'columnB']
const opts: UpsertOptions<MyEntity> = { conflictPaths };
myEntityRepo.upsert({ ... }, opts)