Real upsert for PostgreSQL 9.5+ and Rails 5.2+ / ActiveRecord 5.2+. Uses ON CONFLICT DO UPDATE.
- Does upsert on a single record using
ON CONFLICT DO UPDATE
- Updates timestamps as you would expect in ActiveRecord
- For partial upserts, loads any existing data from the database
- PostgreSQL 9.5+ (that's when UPSERT support was added; see Wikipedia's PostgreSQL Release History)
- ActiveRecord >= 5.2
- Ruby MRI, with the
pg
gem - JRuby is currently not supported
This library was written at a time in history when Rails did not support any #upsert
method.
Instead of using this library, if you are using a current version of Rails, you may want to use its #upsert
. You may want to investigate how newer PostgreSQL versions support MERGE
statement.
Due to a broken build matrix, v0.9.2 and v0.9.3 are incompatible with Rails < 5.2.1. v0.9.4 fixed this issue.
This library is compatible with all major Rails versions covered by the Rails "Severe Security Issues" maintenance policy.
This library may be compatible with older versions of Ruby, however we only run automated tests using the officially supported Ruby versions.
Add this line to your application's Gemfile:
gem 'active_record_upsert'
And then execute:
bundle
Or install it yourself as:
gem install active_record_upsert
Use ActiveRecord.upsert
or ActiveRecord#upsert
. ActiveRecordUpsert respects timestamps.
class MyRecord < ActiveRecord::Base
end
MyRecord.create(name: 'foo', wisdom: 1)
# => #<MyRecord id: 1, name: "foo", created_at: "2016-02-20 14:15:55", updated_at: "2016-02-20 14:15:55", wisdom: 1>
MyRecord.upsert(id: 1, wisdom: 3)
# => #<MyRecord id: 1, name: "foo", created_at: "2016-02-20 14:15:55", updated_at: "2016-02-20 14:18:15", wisdom: 3>
r = MyRecord.new(id: 1)
r.name = 'bar'
r.upsert
# => #<MyRecord id: 1, name: "bar", created_at: "2016-02-20 14:15:55", updated_at: "2016-02-20 14:18:49", wisdom: 3>
If you need to specify a condition for the update, pass it as an Arel query:
MyRecord.upsert({id: 1, wisdom: 3}, arel_condition: MyRecord.arel_table[:updated_at].lt(1.day.ago))
The instance method #upsert
can also take keyword arguments to specify a condition, or to limit which attributes to upsert (by default, all changed
attributes will be passed to the upsert):
r = MyRecord.new(id: 1)
r.name = 'bar'
r.color = 'blue'
r.upsert(attributes: [:name], arel_condition: MyRecord.arel_table[:updated_at].lt(1.day.ago))
# will only update :name, and only if the record is older than 1 day;
# but if the record does not exist, will insert with both :name and :colors
If you want to create a record with the specific attributes, but update only a limited set of attributes, similar to how ActiveRecord::Base.create_with
works, you can do the following:
existing_record = MyRecord.create(id: 1, name: 'lemon', color: 'green')
r = MyRecord.new(id: 1, name: 'banana', color: 'yellow')
r.upsert(attributes: [:color])
# => #<MyRecord id: 1, name: "lemon", color: "yellow", ...>
r = MyRecord.new(id: 2, name: 'banana', color: 'yellow')
r.upsert(attributes: [:color])
# => #<MyRecord id: 2, name: "banana", color: "yellow", ...>
# This is similar to:
MyRecord.create_with(name: 'banana').find_or_initialize_by(id: 2).update(color: 'yellow')
Upsert will perform validation on the object, and return false if it is not valid. To skip validation, pass validate: false
:
MyRecord.upsert({id: 1, wisdom: 3}, validate: false)
If you want validations to raise ActiveRecord::RecordInvalid
, use upsert!
:
MyRecord.upsert!(id: 1, wisdom: 3)
Or using the instance method:
r = MyRecord.new(id: 1, name: 'bar')
r.upsert!
When a table is defined with a database default for a field, this gotcha can occur when trying to explicitly upsert a record to the default value (from a non-default value).
Example: a table called hardwares
has a prio
column with a default value.
┌─────────┬─────────┬─────────┬
│ Column │ Type │ Default │
├─────────┼─────────┼─────────┼
│ id │ integer │ ... |
│ prio │ integer │ 999 |
And hardwares
has a record with a non-default value for prio
. Say, the record with id
1 has a prio
of 998
.
In this situation, upserting like:
hw = { id: 1, prio: 999 }
Hardware.new(prio: hw[:prio]).upsert
will not mention the prio
column in the ON CONFLICT
clause, resulting in no update.
However, upserting like so:
Hardware.upsert(prio: hw[:prio]).id
will indeed update the record in the database back to its default value, 999
.
It's possible to specify which columns should be used for the conflict clause. These must comprise a unique index in Postgres.
class Vehicle < ActiveRecord::Base
upsert_keys [:make, :name]
end
Vehicle.upsert(make: 'Ford', name: 'F-150', doors: 4)
# => #<Vehicle id: 1, make: 'Ford', name: 'Focus', doors: 2>
Vehicle.create(make: 'Ford', name: 'Focus', doors: 4)
# => #<Vehicle id: 2, make: 'Ford', name: 'Focus', doors: 4>
r = Vehicle.new(make: 'Ford', name: 'F-150')
r.doors = 2
r.upsert
# => #<Vehicle id: 1, make: 'Ford', name: 'Focus', doors: 2>
Partial indexes can be supported with the addition of a where
clause.
class Account < ApplicationRecord
upsert_keys :name, where: 'active is TRUE'
end
Custom index can be handled with a Hash containing a literal key :
class Account < ApplicationRecord
upsert_keys literal: 'md5(my_long_field)'
end
Overriding the models' upsert_keys
when calling #upsert
or .upsert
:
Account.upsert(attrs, opts: { upsert_keys: [:foo, :bar] })
# Or, on an instance:
account = Account.new(attrs)
account.upsert(opts: { upsert_keys: [:foo, :bar] })
Overriding the models' upsert_options
(partial index) when calling #upsert
or .upsert
:
Account.upsert(attrs, opts: { upsert_options: { where: 'foo IS NOT NULL' } })
# Or, on an instance:
account = Account.new(attrs)
account.upsert(opts: { upsert_options: { where: 'foo IS NOT NULL' } })
Rails 6 (via the "Add insert_many to ActiveRecord models" PR #35077) added the ability to create or update individual records through #insert
and #upsert
and similarly the ability to create or update multiple records through #insert_all
and #upsert_all
.
Here is a quick comparison of how the Rails native ActiveRecord::Persistence#upsert
feature compares to what's offered in this gem:
Feature | active_record_upsert |
Rails native ActiveRecord::Persistence#upsert |
---|---|---|
Set model level conflict clause | Yes, through #upsert_keys |
No, but can be passed in through the :unique_by option |
Ability to invoke validations and callbacks | Yes | No |
Automatically sets created_at /updated_at timestamps |
Yes | Yes (Rails 7.0+) |
Checks for unique index on the database | No1 | Yes |
Use associations in upsert calls | Yes | No |
Return object type | Instantiated ActiveRecord model | ActiveRecord::Result |
Make sure to have an upsert_test database:
bin/run_rails.sh db:create db:migrate DATABASE_URL=postgresql:https://localhost/upsert_test
Then run rspec
.
Bug reports and pull requests are welcome on GitHub at https://github.com/jesjos/active_record_upsert.
- Jesper Josefsson
- Aurora Nockert
- Olle Jonsson
- Simon Dahlbacka
- Paul Hoffer
- Ivan (@me)
- Leon Miller-Out (@sbleon)
- Andrii Dmytrenko (@Antti)
- Alexia McDonald (@alexiamcdonald)
- Timo Schilling (@timoschilling)
- Benedikt Deicke (@benedikt)
- Daniel Cooper (@danielcooper)
- Laurent Vallar (@val)
- Emmanuel Quentin (@manuquentin)
- Jeff Wallace (@tjwallace)
- Kirill Zaitsev (@Bugagazavr)
- Nick Campbell (@nickcampbell18)
- Mikhail Doronin (@misdoro)
- Jan Graichen (@jgraichen)
Footnotes
-
Though the gem does not check for the index first, the upsert will still fail due to the database constraint. ↩