CODEWITHSUNDEEP
rubyupsertsequel
nooga
nooga

Reputation: 570

How to update or insert on Sequel dataset?

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?

Upvotes: 16

Views: 15767

Answers (4)

Phrogz
Phrogz

Reputation: 303271

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 )

Upvotes: 18

Mladen Jablanović
Mladen Jablanović

Reputation: 44080

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.

Upvotes: 3

Seamus Abshere
Seamus Abshere

Reputation: 8516

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

Upvotes: 1

radiospiel
radiospiel

Reputation: 2479

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

Upvotes: 23

Related Questions