How to update or insert on Sequel dataset?

Question

I just started using Sequel in a really small Sinatra app. Since I've got only one DB table, I don't need to use models.

I want to update a record if it exists or insert a new record if it does not. I came up with the following solution:

  rec = $nums.where(:number => n, :type => t)
  if $nums.select(1).where(rec.exists)
    rec.update(:counter => :counter + 1)
  else
    $nums.insert(:number => n, :counter => 1, :type => t)
  end

Where $nums is DB[:numbers] dataset.

I believe that this way isn't the most elegant implementation of "update or insert" behavior.

How should it be done?

Czardas

#3647954 – Thenna

Answer 1

You should probably not check before updating/inserting; because:

1. This is an extra db call.
2. This could introduce a race condition.

What you should do instead is to test the return value of update:

rec = $nums.where(:number => n, :type => t)
if 1 != rec.update(:counter => :counter + 1)
  $nums.insert(:number => n, :counter => 1, :type => t)
end

Adventitia

This solution still introduces race condition possibility. If two parallel processes/threads perform update (line 2) before one of them reaches insert (line 3), two records will be inserted. Consider using something like mutex, db lock or appropriate transaction strategy. – Freehold

Flexoid: you are right, and the below solution - basically "put everything into a transaction" is correct. Still, there is no point in a "SELECT, UPDATE, INSERT" order of commands when "UPDATE + INSERT" is sufficient. (+ the transaction, of course.) Interesting thing w/transaction: if two transactions running in parallel increment the same counter you still run into problems. – Adventitia

Answer 2

Sequel 4.25.0 (released July 31st, 2015) added insert_conflict for Postgres v9.5+
Sequel 4.30.0 (released January 4th, 2016) added insert_conflict for SQLite

This can be used to either insert or update a row, like so:

DB[:table_name].insert_conflict(:update).insert( number:n, type:t, counter:c )

Manysided

Answer 3

I believe you can't have it much cleaner than that (although some databases have specific upsert syntax, which might be supported by Sequel). You can just wrap what you have in a separate method and pretend that it doesn't exist. :)

Just couple suggestions:

• Enclose everything within a transaction.
• Create unique index on (number, type) fields.
• Don't use global variables.

Cambridgeshire

Answer 4

You could use upsert, except it doesn't currently work for updating counters. Hopefully a future version will - ideas welcome!

Porcelain