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.

Accusative

Answer 1

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.

Issiah

see comment on Pasta's answer – Pinnule

Has anyone seen this generate an upsert? The rails guide indicates the new object won't yet be stored in the DB so I can't see how this is a true DB upsert. I.e., won't work reliably in a multi-threaded environment. – Centuple

This solution has concurrency issues. It will fail if another thread updates the table between find_or_initialize and save. – Krol

This isn't an upsert. Outcome wise, it is equivalent (so long as concurrency issues like @BarryFruitman mentions do not occur) but performance wise it is not. – Tenorrhaphy

Answer 2

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

Laris

This should be used with caution as upsert & upsert_all both bypass all validations and callbacks. – Encyclical

add an example of how to use maybe? – Dreadfully

Answer 3

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

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

Missal

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]

Franciscafranciscan

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;

Dameron

Answer 6

There is also Model.find_or_create

Galliwasp

This does not do an upsert. It does a select and then (optionally) an insert. While you get the same effect in a single threaded world, in a multi-threaded world, you'll need it to do an actual upsert. – Pinnule

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.

Toothless

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)

Dramamine

Not a fan of using errors to drive expected logical flow, especially when you can predict when this error would occur. – Pence

To those who downvoted, this is a standard approach in Rails 6 although wrapped in a transaction. – Vindication

Rails now has create_or_find_by since Rails 6 to do this without resorting to using errors for logical flow, except you have to have the uniqueness contraint at the datbase level, not just in ActiveRecord – Warmedover