title: UPSERT in ActiveRecord (Rails 6)
body: <p>I see there were some questions on the topic in the past, but after Rails 6 release I hope things have change.</p>
  <p><strong>Goal</strong></p>
  <p>Having <code>Role</code> and <code>Effect</code> ActiveRecord models I would like to temporary set up associations (i.e. there should be <code>role.add_effect(effect)</code> and <code>role.remove_effect(effect)</code> 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.</p>
  <p>Assuming intermediate object (is there a term for such models?)</p>
  <pre class="lang-rb prettyprint-override"><code>class RoleEffect &lt; ApplicationRecord
    belongs_to :role
    belongs_to :effect
  end
  
  class CreateRoleEffects &lt; 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
  </code></pre>
  <p>In terms of SQL that would look something like</p>
  <pre class="lang-sql prettyprint-override"><code>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
  </code></pre>
  <p><strong>So the question</strong></p>
  <p>what is the current (as of Rails 6) proper way to implement such add/remove methods so that they are <strong>atomic</strong>. I see there is <a href="https://apidock.com/rails/v6.0.0/ActiveRecord/Persistence/ClassMethods/upsert" rel="nofollow noreferrer">upsert</a> method available but from the documentation (nor the source code) I can't understand how to do that <code>counter = counter + excluded.counter</code> bit. I also see the recommendations to use <a href="https://github.com/zdennis/activerecord-import" rel="nofollow noreferrer">https://github.com/zdennis/activerecord-import</a> gem but having a third party package only to execute a single SQL query seems like an overkill.</p>
  <p>If current implementation of the <code>upsert</code> 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.</p>
  <p>My initial attempt was</p>
  <pre class="lang-rb prettyprint-override"><code>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(&lt;&lt;~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
  </code></pre>
  <p>but it failed miserably due to mismatch with an expected bindings format (rather ugly array of pairs).</p>
  <p>Since both PostgreSQL and SQLite do support such queries I expect the solution that would work for both of them.</p>
  <p><strong>UPDATE</strong> (after first comments)</p>
  <p>Of course I do have</p>
  <pre class="lang-rb prettyprint-override"><code>has_many :role_effects, dependent: :destroy
  has_many :effects, through: :role_effects
  </code></pre>
  <p>But this does not provide an atomic operation to modify the association.</p>
  <p>I can abandon the counter idea, remove uniqueness index and add effects like that <code>role.effects &lt;&lt; effect</code> but removing one association would be tricky as it will require <code>DELETE ... LIMIT 1</code> 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).</p>
  <p>Alternatively I could've used</p>
  <pre class="lang-rb prettyprint-override"><code>RoleEffect.find_or_create_by!(role: role, effect: effect).increment!(:counter, delta)
  </code></pre>
  <p>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 <code>INSERT ... ON CONFLICT UPDATE</code>.</p>