UPSERT in ActiveRecord (Rails 6)

Question

I see there were some questions on the topic in the past, but after Rails 6 release I hope things have change.

Goal

Having Role and Effect ActiveRecord models I would like to temporary set up associations (i.e. there should be role.add_effect(effect) and role.remove_effect(effect) methods) to track which effects are currently active for a given role. Note that effects lifetime is beyond those temporary associations and many roles may have the same effect active at some point.

Assuming intermediate object (is there a term for such models?)

class RoleEffect < ApplicationRecord
  belongs_to :role
  belongs_to :effect
end

class CreateRoleEffects < ActiveRecord::Migration[6.0]
  def change
    create_table :role_effects do |t|
      t.belongs_to :role, null: false, foreign_key: true
      t.references :effect, null: false, foreign_key: true
      t.integer :counter, null: false, default: 0

      t.timestamps
    end
    add_index :role_effects, [:role_id, :effect_id], unique: true
  end
end

In terms of SQL that would look something like

INSERT INTO role_effects (role_id, effect_id, counter, created_at, updated_at)
VALUES (:role_id, :effect_id, :delta, :ts, :ts)
ON CONFLICT (role_id, effect_id)
DO UPDATE SET counter = counter + excluded.counter, updated_at = :ts

So the question

what is the current (as of Rails 6) proper way to implement such add/remove methods so that they are atomic. I see there is upsert method available but from the documentation (nor the source code) I can't understand how to do that counter = counter + excluded.counter bit. I also see the recommendations to use https://github.com/zdennis/activerecord-import gem but having a third party package only to execute a single SQL query seems like an overkill.

If current implementation of the upsert method is not meant for such use case I don't mind having it as a custom SQL query though I also can't understand what would be the proper API to execute it safely with all those values substituted safely (sanitized) and preferably mentioned by name and not by anonymous '?' and ordering.

My initial attempt was

def add_effect(effect)
  change_effect_counter(effect, 1)
end

def remove_effect(effect)
  change_effect_counter(effect, -1)
end

private

def change_effect_counter(effect, delta)
  RoleEffect.connection.exec_insert(<<~SQL.strip, {role_id: self.id, effect_id: effect.id, delta: delta, ts: Time.now})
    INSERT INTO role_effects (role_id, effect_id, counter, created_at, updated_at)
    VALUES (:role_id, :effect_id, :delta, :ts, :ts)
    ON CONFLICT (role_id, effect_id)
    DO UPDATE SET counter = counter + :delta, updated_at = :ts
  SQL
  nil
end

but it failed miserably due to mismatch with an expected bindings format (rather ugly array of pairs).

Since both PostgreSQL and SQLite do support such queries I expect the solution that would work for both of them.

UPDATE (after first comments)

Of course I do have

has_many :role_effects, dependent: :destroy
has_many :effects, through: :role_effects

But this does not provide an atomic operation to modify the association.

I can abandon the counter idea, remove uniqueness index and add effects like that role.effects << effect but removing one association would be tricky as it will require DELETE ... LIMIT 1 which is not available in SQLite by default (I know it can be recompiled with a special flag but that is just too much of a requirement for a development env).

Alternatively I could've used

RoleEffect.find_or_create_by!(role: role, effect: effect).increment!(:counter, delta)

But I am not sure if both of this calls guarantee correct behaviour in a presence of concurrency. And even if it is - it uses multiple queries instead of INSERT ... ON CONFLICT UPDATE.

Is this not a use case for [has_many :through](https://guides.rubyonrails.org/association_basics.html#the-has-many-through-association)? The 'intermediate object' is called a join model. — jvillian, Sep 25 '20 at 18:43
``upsert`` is for doing bulk attribute changes, which bypass all the goodness of Rails validations, etc. This seems like a pretty straightforward thing to do with ``has_many through`` and something like ``first_or_create``. — rmlockerd, Sep 25 '20 at 18:48
@jvillian @rmlockerd what does `has_many :through` gives regarding the question? How does it provides atomicity? As far as I can tell it will use separate queries to fetch initial state and to modify it which would cause inconsistency in presence of concurrency. — Ilya Denisov, Sep 25 '20 at 21:41
@jvillian thanks for mentioning the 'join model' term though :) — Ilya Denisov, Sep 25 '20 at 21:43
I don't know what atomicity means. Or what inconsistency in presence of concurrency means. If you are concerned about race conditions, look into locking. — jvillian, Sep 26 '20 at 04:19
this post may be useful for you: https://stackoverflow.com/questions/58759765/how-does-the-upsert-function-in-rails-work — Siwei, Jan 30 '23 at 01:11

Ilya Denisov · Answer 1 · 2023-01-31T18:47:29.227

Just in case, I'll post my findings though the result is quite imperfect (e.g. insertion does not return the model object which may be unexpected, caches like role.role_effects may be stale etc.).

class CreateRoleEffects < ActiveRecord::Migration[6.0]
  def change
    create_table :role_effects do |t|
      t.belongs_to :role, null: false, foreign_key: true
      t.references :effect, null: false, foreign_key: true
      t.integer :counter, null: false, default: 0

      t.timestamps
    end
    add_index :role_effects, [:role_id, :effect_id], unique: true
  end
end

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  def self.exec_custom_insert(sql, vars)
    sql = ActiveRecord::Base::sanitize_sql_array([sql, vars])
    connection.exec_insert(sql, 'SQL')
  end
end

class RoleEffect < ApplicationRecord
  belongs_to :role
  belongs_to :effect

  scope :active, -> { where('counter > 0') }
  scope :for_role, ->(role) { where(role: role) }

  def self.increment(role, effect)
    change(role, effect, 1)
  end

  def self.decrement(role, effect)
    change(role, effect, -1)
  end

  private_class_method def self.change(role, effect, delta)
    exec_custom_insert(<<~SQL.strip, {role_id: role.id, effect_id: effect.id, delta: delta, ts: Time.now})
      INSERT INTO role_effects (role_id, effect_id, counter, created_at, updated_at)
      VALUES (:role_id, :effect_id, :delta, :ts, :ts)
      ON CONFLICT (role_id, effect_id)
      DO UPDATE SET counter = counter + excluded.counter, updated_at = excluded.updated_at
    SQL
    nil
  end
end

class Role < ApplicationRecord
  ...
  has_many :role_effects, dependent: :destroy
  has_many :active_effects, -> { RoleEffect.active }, through: :role_effects, source: :effect

  def add_effect(effect)
    RoleEffect.increment(self, effect)
  end

  def remove_effect(effect)
    RoleEffect.decrement(self, effect)
  end
end

Usage

irb(main):127:0> reload!; r = Role.first; e = Effect.first
Reloading...
   (0.0ms)  SELECT sqlite_version(*)
  Role Load (0.1ms)  SELECT "roles".* FROM "roles" ORDER BY "roles"."id" ASC LIMIT ?  [["LIMIT", 1]]
  Effect Load (0.1ms)  SELECT "effects".* FROM "effects" ORDER BY "effects"."id" ASC LIMIT ?  [["LIMIT", 1]]
=> #<Effect id: 1, name: "The Effect", created_at: "2020-09-25 08:42:54", updated_at: "2020-09-25 08:42:54">
irb(main):128:0> r.active_effects
  Effect Load (0.1ms)  SELECT "effects".* FROM "effects" INNER JOIN "role_effects" ON "effects"."id" = "role_effects"."effect_id" WHERE "role_effects"."role_id" = ? AND (counter > 0) LIMIT ?  [["role_id", 1], ["LIMIT", 11]]
=> #<ActiveRecord::Associations::CollectionProxy []>
irb(main):129:0> r.add_effect(e)
  SQL (1.2ms)  INSERT INTO role_effects (role_id, effect_id, counter, created_at, updated_at)
VALUES (1, 1, 1, '2020-09-27 12:16:55.639693', '2020-09-27 12:16:55.639693')
ON CONFLICT (role_id, effect_id)
DO UPDATE SET counter = counter + excluded.counter, updated_at = excluded.updated_at
=> nil
irb(main):130:0> r.active_effects
  Effect Load (0.3ms)  SELECT "effects".* FROM "effects" INNER JOIN "role_effects" ON "effects"."id" = "role_effects"."effect_id" WHERE "role_effects"."role_id" = ? AND (counter > 0) LIMIT ?  [["role_id", 1], ["LIMIT", 11]]
=> #<ActiveRecord::Associations::CollectionProxy [#<Effect id: 1, name: "The Effect", created_at: "2020-09-25 08:42:54", updated_at: "2020-09-25 08:42:54">]>
irb(main):131:0> r.remove_effect(e)
  SQL (8.1ms)  INSERT INTO role_effects (role_id, effect_id, counter, created_at, updated_at)
VALUES (1, 1, -1, '2020-09-27 12:17:08.148215', '2020-09-27 12:17:08.148215')
ON CONFLICT (role_id, effect_id)
DO UPDATE SET counter = counter + excluded.counter, updated_at = excluded.updated_at
=> nil
irb(main):132:0> r.active_effects
  Effect Load (0.2ms)  SELECT "effects".* FROM "effects" INNER JOIN "role_effects" ON "effects"."id" = "role_effects"."effect_id" WHERE "role_effects"."role_id" = ? AND (counter > 0) LIMIT ?  [["role_id", 1], ["LIMIT", 11]]
=> #<ActiveRecord::Associations::CollectionProxy []>

UPDATE 31.01.2023

Rails 7 adds an option to specify ON CONFLICT behaviour:

RoleEffect.upsert(
  {role_id: role.id, effect_id: effect.id, counter: delta},
  unique_by: %i[role_id effect_id],
  on_duplicate: Arel.sql(
    "counter = counter + EXCLUDED.counter, updated_at = EXCLUDED.updated_at"
  )
)

To be honest, I expected that explicit :unique_by won't be needed but it is: at least for the SQLite the generated SQL has ON CONFLICT ("id") ignoring the unique index the table has.

Anyway, looks better than raw SQL.

UPSERT in ActiveRecord (Rails 6)

1 Answers1