Logo

TODAY I LEARNED

Using Upsert with Rails

Some DBMS support natively something that behaves like update or insert, Rails recently added the method Upsert that can take advantage of this method.

It is useful to update information that do not need to run validations, meaning, in a super performant way, here's an example:

# We usually do this:
activity = current_user.activity
if activity 
  activity.update(last_seen_at: Time.current)
else
  current_user.activity.create(last_seen_at: Time.current)
end

But if you see, it does not need to run any validation, it just needs to update last_seen_at if exists or create a new one, it performs two queries: one to instanciate the activity object and a second one that performs the real update/insert statement.

That can be replaced with the following code and it will perform just a single query and it will take care to either create the record or update an existing one

Activity.upsert({ last_seen_at: Time.current, user_id: current_user.id}, unique_by: :user_id)

To make this really work, considering you use Postgresql, you have to add a unique index on user_id and modify default value on created_at and updated_at in a migration like this:

query = <<-SQL
  ALTER TABLE #{Activity.table_name}
  ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP,
  ALTER COLUMN updated_at SET DEFAULT CURRENT_TIMESTAMP
SQL
execute(query)

Learned by Edwin Cruz on Mar 19, 2021

973983 adforcodereview v3 0211021 c02 021121