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?

http://stackoverflow.com/questions/3647454/increment-counter-or-insert-row-in-one-statement-in-sqlite — Reactormonk, Mar 19 '12 at 11:09

score 20 · Accepted Answer · edited Apr 06 '12 at 22:26

20

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

This is an extra db call.
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

edited Apr 06 '12 at 22:26

the Tin Man

150,910
39
198
279

answered Mar 28 '12 at 14:00

radiospiel

2,370
18
27

This is a nice solution. Thanks – Kumar Akarsh Feb 19 '13 at 11:00
2

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. – Flexoid Aug 19 '15 at 11:37
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. – radiospiel Aug 20 '15 at 18:01

score 16 · Answer 2 · answered Jan 19 '16 at 22:28

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 )

score 3 · Answer 3 · edited May 19 '15 at 16:45

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.

edited May 19 '15 at 16:45

the Tin Man

150,910
39
198
279

answered Mar 19 '12 at 11:40

Mladen Jablanović

41,202
10
87
110

score 1 · Answer 4 · answered Jul 05 '12 at 16:14

1

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

answered Jul 05 '12 at 16:14

Seamus Abshere

7,730
2
40
59

How to update or insert on Sequel dataset?

4 Answers4