many_to_many upsert

[jonjonw]

My database has several many-to-many relations with a unique key to prevent multiple connections between the same entities. ie:

create table a(id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
create table b(id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
create table ab(
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  a_id int references a,
  b_id int references b,
  note text default null,
  constraint uniq_ab unique(a_id, b_id)
);

I'd like to have the relation use an upsert to add associations so I don't have to check if the association already exists. I implemented something like the following:

class A < Sequel.Model(:a)
  many_to_many :bs, join_table: :ab, adder: ->(*args) { upsert_adder(:a, :a_id, *args) }

  def upsert_adder(table_name, right_key, model, note = nil)
    db[table_name].insert_conflict(
      constraint: :uniq_ab,
      update: %i[note].to_h { |c| [c, Sequel[:excluded][c]] }
    ).insert(right_key => model.id, a_id: id, note:)
  end
end

This seems to be working but I have several other similar tables I and I am looking for a way to add the functionality to them all without repeating the above. Is there an easy way to do this?

[jeremyevans]

You probably want to write a class method that returns a proc to use for adder. Something like this (untested):

def (Sequel::Model).upsert_adder(table, right_key, left_key, constraint)
  ds = db[table].insert_conflict(
      constraint:,
      update: %i[note].to_h { |c| [c, Sequel[:excluded][c]] }
    )

  ->(model, note=nil) { ds.insert(right_key => id, left_key => model.id, note:) }
end

class A < Sequel::Model(:a)
  many_to_many :bs, join_table: :ab, adder: upsert_adder(:ab, :a_id, :b_id, :uniq_ab)
end

[jonjonw]

Thanks @jeremyevans! That will clean things-up quite a bit. I might try writing it as a plugin.

module Sequel::Plugins::ManyToManyUpsert
  module ClassMethods
    def upsert_adder(table, right_key, left_key, constraint)
      ds = db[table].insert_conflict(
        constraint:,
        update: %i[note].to_h { |c| [c, Sequel[:excluded][c]] }
      )

      ->(model, **params) { ds.insert(right_key => id, left_key => model.id, **params) }
    end
  end
end

I wonder if there's a way to introspect some of the values passed to upsert_adder from the context of the many_to_many declaration.