On Monday, June 9, 2014 6:11:33 PM UTC-7, Andrew Hacking wrote: > > A fairly common pattern in a JSON API based server is to support > idempotent insert/update (aka upsert). It is especially useful when using > client assigned v4 UUIDs as PUT can be used for both create and update. > > I noticed a ruby gem https://github.com/seamusabshere/upsert that > provides an efficient way to do upsert, however I would rather stick to > using Sequel than introduce another dependency. > > Sequel for me has been a performance and functionality win over the other > ruby alternatives, adding upsert is something that would yield performance > additional wins. > > Is there any interest in implementing an upset operation in sequel? >
I have no interest in trying to emulate a upsert/merge operation generically across all of the databases that Sequel supports. Sequel's philosophy is to expose the database specific API for it. So on MySQL, Sequel supports Dataset#on_duplicate_key_update, and on both SQLite and Mysql, Dataset#replace does something similar to upsert/merge. Looking at the gem you mentioned, they use INSERT OR IGNORE on SQLite, which is not like an upsert/merge operation, since it does not update the row if it already exists. That's similar to Dataset#insert_ignore in Sequel's MySQL support. I don't think Sequel supports that syntax on SQLite, but I'd consider adding support for it. The fact that the gem you mentioned uses INSERT OR IGNORE on SQLite but uses a UDF emulation on PostgreSQL and MySQL that does will update the row if it already exists would discourage me from using it if I wanted portable code (or a true upsert/merge on SQLite). For PostgreSQL, from my prior reading I know that there are multiple approaches to emulating upsert/merge, each with their own tradeoffs, and which approach is best depends on the specific database/query load. There is no one best approach. So any gem that tries to provide an "optimized" upsert/merge emulation for PostgreSQL must ship multiple implementations. I don't think it makes sense to such a thing to be included in Sequel. Similar to sequel_postgresql_triggers, I think if you want to write such a layer, it's best shipped as an external extension. When PostgreSQL ships support for MERGE (or something similar), Sequel will add support for it. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
