Upsert in Rails ActiveRecord

Question

Does ActiveRecord have a built-in upsert functionality? I know I could write it myself but I obviously don't want to if such a thing already exists.

Answer 1

There is an awesome new feature in rails 6: they added upsert and upsert_all to ActiveRecord

More info can be found here https://edgeapi.rubyonrails.org/classes/ActiveRecord/Persistence/ClassMethods.html#method-i-upsert_all

Answer 2

Model.find_or_initialize likely does what you want. You can chain it with save or update_attributes if that makes sense.

More info in the Rails Guides.

Answer 3

I just ran across this library: https://github.com/seamusabshere/upsert

I haven't tested it yet, but it looks promising

Answer 4

Rails 6 introduces create_or_find_by for this case https://github.com/rails/rails/pull/31989

Also for bulk of records it is possible to use https://github.com/zdennis/activerecord-import

Example:

Book.import [book], on_duplicate_key_update: [:title]

Answer 5

IMO Upsert mechanism requires custom configuration for each model.

So the best solution would be to implement a custom SQL query for a model, e.g.

insert into <table> (<field_1>, ..., <field_n>) 
  values (<field_1_value>, ..., <field_n_value>)
on duplicate key update
  field_x = field_x_value,
  ...
  field_z = field_z_value;

Answer 6

There is also Model.find_or_create

Answer 7

from Rails 6 , it has upsert method, doc:

1. https://apidock.com/rails/v6.0.0/ActiveRecord/Persistence/ClassMethods/upsert
2. How does the upsert function in Rails work?

usage:

Book table
---
id: integer
author_name: string
title: string

Usage : ( into rails c )

> Book.all   #=> []
> Book.upsert({ id: 1, author_name: 'Liu', title: 'Tripple body 1'})
> Book.upsert({ id: 1, author_name: 'Liu', title: 'Tripple body 1'})
> Book.upsert({ id: 1, author_name: 'Liu', title: 'Tripple body 1'})
> Book.all  # => only 1 book, with tile: 'Tripple body 1'

and you may see the raw SQL looks like: ( in Postgres 14.2 )

INSERT INTO "books" ("author_name","title","created_at","updated_at") 
  VALUES ('Liu', 'Tripple body 1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) 
  ON CONFLICT ("id") 
  DO UPDATE SET updated_at=(
      CASE WHEN (
        "books"."author_name" IS NOT DISTINCT 
          FROM excluded."author_name" 
        AND "books"."title" IS NOT DISTINCT 
          FROM excluded."title"
      ) 
      THEN "books".updated_at ELSE CURRENT_TIMESTAMP END
    ),
    "author_name"=excluded."author_name",
    "title"=excluded."title" 
  RETURNING "id"

so be aware that:

1. upsert will determine uniqueness from the hash parameter, so make sure there is the key column in it which included in the table's unique columns (such as id column )
2. if you don't pass an id or similar unique column in the parameter, it always insert new record into table
3. upsert will skip the model callbacks and validations.

btw, I hate this method, I prefer find_or_create_by or just:

unless Book.exists?("...")
  Book.create ...
end

which is clearer.

Answer 8

I had written a blog post on how we can achieve this. Check it out here.

You'll have to write an active record extension. It will look something like this.

module ActiveRecordExtension
  extend ActiveSupport::Concern

  def self.upsert(attributes)
    begin
        create(attributes)
    rescue ActiveRecord::RecordNotUnique, PG::UniqueViolation => e
        find_by_primary_key(attributes['primary_key']).
        update(attributes)
    end
  end
end

ActiveRecord::Base.send(:include, ActiveRecordExtension)