Upsert in Rails ActiveRecord
Does ActiveRecord have a built-in upsert functionality? I know I could write it myself but I开发者_C百科 obviously don't want to if such a thing already exists.
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
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.
I just ran across this library: https://github.com/seamusabshere/upsert
I haven't tested it yet, but it looks promising
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]
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;
There is also Model.find_or_create
from Rails 6 , it has upsert
method, doc:
- https://apidock.com/rails/v6.0.0/ActiveRecord/Persistence/ClassMethods/upsert
- 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:
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 asid
column )- if you don't pass an
id
or similar unique column in the parameter, it always insert new record into table 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.
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)
精彩评论