Atomic upsert? - Githubissues

glebm commented 6 years ago

A basic upsert has three inputs:

Values to insert.
Values to update if insertion failed because of a unique index / constraint conflict.
Values to return.

All databases supported by Arel support a form upsert:

On Postgres v9.5+, this is done with INSERT ... ON CONFLICT ... DO UPDATE.
On MySQL, this is done with INSERT ... ON DUPLICATE KEY UPDATE. There should only be one unique index on the table that matches the query (https://bugs.mysql.com/bug.php?id=58637).
SQLite only provides an atomic INSERT OR REPLACE that can be used to implement upsert if the table definition is known, e.g. https://stackoverflow.com/a/7511635/181228. Alternatively, it can be implemented with an INSERT OR IGNORE, followed by an UPDATE, wrapped in a transaction.
On Oracle, Microsoft SQL Server, DB2, and Informix, this is done with the MERGE ... WHEN [NOT] MATCHED syntax (SQL standard).

The lowest common denominator between all these databases is that the conflicting values must belong to a single unique index (there is no way to select them explicitly).

it'd be nice to have a cross-database way to do it (useful for library authors).

Is Arel at the right abstraction level to provide it?

matthewd commented 6 years ago

Per #523, Arel development is moving to rails/rails.

If this issue is still relevant, please consider reopening it over there. (Note that the Rails repository does not accept feature request issues, and requires reproduction steps that rely on Active Record's documented API.)

mhuggins commented 6 years ago

Where can I find the issue for this in rails/rails?

rails / arel

Atomic upsert? #520