alfateam/orange-orm

Support for on conflict clause

Closed this issue · 6 comments

lorefnon commented

Hello, thanks for creating this library. The API looks really great for TS.

Curious if you would be open to adding support for on conflict clause for insert. It is quite convenient for handling upsert. Related docs in postgres manual.

lroal commented

Hello, thanks for your feedback! It is highly appreciated.
Yes, upserts are probably something that needs to be there. I need to do some research first, because I want the feature to be available for the other databases as well (MySQL, MSSQL, SAP, SQLite). I guess not all of them have this built-in, so I need to figure out an alternative implementation for those.
Also, I need to decide whether to have a separate upsert method or use the existing insert method with an extra input option.

lorefnon commented

Glad to see that this is something you are open to considering. I think on conflict has wider application than just upsert so it does belong in the insert api.

An example is something like insert into user_role (user_id, role_id, assigned_at) values (1, 1, now()) on conflict do nothing to assign a role unless already assigned while also tracking assignment time.

However having an upsert function in addition (which internally uses this) will certainly help with discoverability.

Is the goal of rdb to provide a uniform api across all databases by emulating features not available ? I know libraries like jooq strive for this but that does complicate the process of exposing new features. A lot of folks would also be happy to have most of the features available in their database of choice exposed through a type-safe api. Just my 2 cents.

lroal commented

Maybe we can use the same concurrency interface as on saveChanges().
Like this:

await db({ order: { concurrency: 'skipOnConflict' } }).order.insert({ id: 1, name: 'foo'});
await db({ order: { concurrency: 'overwrite' } }).order.insert({ id: 1, name: 'foo'});

In the saveChanges method, it is also possible to have concurrency at the column level. But this is something that can be postponed for the insert.

Yes, having an additional upsert method equivalent to the last statement above, would be practical.
It is important to provide a uniform api, but not at all costs. There are several postgres specific methods in the core that are not exposed to the typescript api - yet. (E.g. schema, transaction lock, session lock).

lroal commented

Mostly done with the postgres part.
This also works with custom strategy on columns.

Usage and tests

https://github.com/alfateam/rdb/blob/conflict/tests/conflicts.test.js#L72-L263

Todo

  • sap, mysql, mssql, sqlite
  • insertAndForget
lroal commented

Done - in master now