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?

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

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 )

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.

Answer 4

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