/active_record_upsert

Upsert for Rails 5 / Active Record 5

Primary LanguageRubyMIT LicenseMIT

Gem Version Build Status Code Climate

ActiveRecordUpsert

Real upsert for PostgreSQL 9.5+ and Rails 5+ / ActiveRecord 5+. Uses ON CONFLICT DO UPDATE.

Main points

  • 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

Prerequisites

  • PostgreSQL 9.5+ (that's when UPSERT support was added; see Wikipedia's PostgreSQL Release History)

  • ActiveRecord >= 5

  • For MRI: pg

  • For JRuby: No support

NB: Releases to avoid

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.

Installation

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

Usage

Create

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>

Update

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

Create with specific Attributes

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')

Validations

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!

Gotcha with database defaults

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.

Conflict Clauses

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 NULLL } })

Tests

Make sure to have an upsert_test database:

bin/run_rails.sh db:create db:migrate DATABASE_URL=postgresql://localhost/upsert_test

Then run rspec.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/jesjos/active_record_upsert.

Contributors