Possible to upsert (do INSERT ON DUPLICATE KEY UPDATE)?

[customautosys]

Dear mevdschee, is it possible to do this? Otherwise I guess I will have to select and check first which will use more data.

[mevdschee]

This is easy to build, but not easy to design. What verb should we use: "post", "put", "patch" or even "merge"? And what value should we return: rows affected vs. insert id?

[customautosys]

Dear mevdschee,

If you're going to add this, maybe I can suggest POST (since it is primarily an insert)?

Maybe it can return something in JSON?

Many thanks for considering this idea anyway!

[mycaravam]

I build a PoC upsert function today with LINK-method. If anyone wants to take a look at it:

https://gist.github.com/mycaravam/7b298db541e042e85573921fe832afb2

As @mevdschee said: easy to build, harder to design. Its not fully tested yet, so use at own risk...

[roipeker]

I have no clue how to overcome the INSERT IGNORE query... i make bulk inserts (400-500 records), and lots of records will be duplicated in the table, exception gets thrown, and no insert is made... can u somehow merge the LINK implementation, or parametrize somehow the url query to insert the SQL strings? No PHP experience here, just using ur lib as a quick api on my server :)

[mevdschee]

@roipeker this is by no means a trivial topic, please read this article:

https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

My take away from the article is that there is no easy cross-database performant and correct solution and that it is better to make your application aware of the existence/absence of records.

[roipeker]

What about the ability to extend api.php implantation with custom endpoints to created custom queries/implementations?

[Richie765]

Here's how I think it can be done with MySQL.

INSERT INTO user_logins(username, password)
  VALUES ('Naomi', 'secret1'), ('James', 'secret2')
  ON DUPLICATE KEY UPDATE
    username = VALUES(username),
    password = VALUES(password);

As you can see it is almost identical to a regular INSERT. The ON DUPLICATE KEY UPDATE section is added, with an assignment for each field.

Regarding the verb/endpoint, I think it's best to have it exactly identical as the regular insert. To differentiate between an INSERT and UPSERT, a header could be used, perhaps:

crud-allow-upsert: true

Regarding the return-value, I'd just return the PK's just like with the regular insert. There may be cases when you'd like to know weather it was inserted or updated, but I think they are rare enough to ignore.

In short, the INSERT and UPSERT are identical except for the header, in which case the ON DUPLICATE KEY UPDATE section is appended.