Rom-sql upsert multiple entities by default

#1

Hi,

I was just working on upserting records and was surprised to see that upsert in rom-sql does not support multiple records at once. My assumption was that the command would support multiple entities at once like the other commands. (ROM::SQL::Relation::Writing#upsert seems to imply that multiple values are supported, but I think the PostgreSQL command module changes the behavior.)

My solution was multi_insert with the insert_conflict options (for PostgreSQL). Is this the proper approach, or did I miss the way to do this more simply with a changeset or relation command?

  module Repositories
    class TickerValues < ::ROM::Repository[:ticker_values]
      def upsert(values)
        ticker_values.dataset.insert_conflict(
          constraint: :idx_ticker_values_uniq,
          update: {
            value:   :excluded__value,
            updated_at: :excluded__updated_at
          }
        ).multi_insert(values)
      end
    end
  end

P.S. I discovered that rom-sql is not compatible with Sequel 5+ because in this instance the update hash syntax specified in the example above does not work beyond Sequel 4. Will Sequel 5 support be released before ROM 5.0?

Thanks!

#2

I discovered that rom-sql is not compatible with Sequel 5+ because in this instance the update hash syntax specified in the example above does not work beyond Sequel 4

AFAIK it’s not an issue with ROM, symbol splitting is disabled by default in Sequel 5. You can enable it manually, or use the preferred method like this:

ticker_values.dataset.insert_conflict(
  constraint: :idx_ticker_values_uniq,
  update: {
    value: Sequel[:excluded][:value],
    updated_at: Sequel[:excluded][:updated_at]
  }
).multi_insert(values)
#3

@smaximov seems tests pass in Sequel 4 and 5 with that change. Thanks!

Any experience with the upsert behavior? Does anyone use upsert for multiple records?

#4

I use bulk upsert via the command interface. It works almost fine except that bulk upserts generate multiple inserts statements (I use ROM via hanami-model which is locked on rom v3.3, I haven’t tried it with more recent rom versions yet).

#5

Thanks for the info @smaximov. I’m going to work on my first Hanami project shortly and was going to use ROM directly instead of hanami-model. Any benefits to using the model?

#6

I didn’t use bare ROM much but I think it’s more mature at the moment. hanami-model is just a thin wrapper around ROM anyway. And if you want to use new ROM features, you are out of luck.

#7

Another argument in favor of using ROM directly: hanami-model's associations story is far from complete and it doesn’t allow to leverage ROM for features it doesn’t yet support. This a problem I’ve stumbled upon today (copy-pasted from the Hanami Gitter):

Consider the problem: given the following table definition:

create table users (
  id bigserial primary key,
  email text not null unique,
  ref_id bigint references users(id), -- referral user ID
);

suppose I want to fetch the email of the user with ID = 42 along with the email of their referral:

select users.id as id,
       users.email as email,
       referrals.id as ref_id,
       referrals.email as ref_email
  from users
  join users as referrals on users.ref_id = referrals.id
 where users.id = 42;

As far as I understand, hanami-model does not support defining self-referencing associations yet (see: https://github.com/hanami/model/blob/3258b68191c20bcff12e99f0f86e39e3e22879a2/lib/hanami/model/associations/dsl.rb), so I cannot use hanami-model/ROM relation when joining the users relation with itself.

But I can use Sequel expressions for that and fetch columns of the base relation:

users
  .join(Sequel[:users].as(:referrals), id: :ref_id)
  .where(Sequel[:users][:id] => 42)
  .select(
    users[:id].qualified.as(:id), 
    users[:email].qualified.as(:email)
  )
  .one

But I can’t make it work to return columns of the aliased relation (referrals), because hanami-model's select doesn’t accept Sequal expressions:

...
  .select(Sequel[:referrals][:id].as(:ref_id))

causes the following error: “NoMethodError: undefined method ‘primary_key?’ for #Sequel::SQL::AliasedExpression:0x00007fdb03b8cff0”. It seems like select accepts either Symbols or ROM attributes. But I can’t build ROM attributes for the referrals relation, users.as(:referrals)[:id].as(:ref_id) causes “ROM::MapperMissingError: referrals”, because the relation is not registered and it cannot be registered because hanami-model doesn’t yet support it.

I don’t know, maybe it can be solved using hanami-model at the moment, but it’s far from obvious in that way.

#8

Thanks for posting the example!

From what I can tell, Hanami tends to build its own opinionated abstraction on top of things like ROM rather than add a thin layer on top and then let you get at the ROM internals directly for more sophisticated work. I much prefer a thin layer that makes ROM fit more naturally into whatever a framework like Hanami wants to facilitate, but not try to recreate their own classes for all concepts. I was looking at the internals of Hanami and it looks like all Hanami and it’s not immediately obvious that ROM is under the hood. That approach makes it harder to maintain Hanami (to mirror ROM things) and harder to extend (accessing internals of Hanami such as ROM).

I’m going to use ROM since I don’t see a benefit of being bound to Hanami’s models. I can imagine that I might end up with some sort of abstraction later to get my ROM implementation to work better with Hanami, but will see – still getting my head around ROM.

#9

After playing with hanami-model for some time, I have chosen the same path, i.e. working with ROM directly. I have created a mini project that shows how to integrate Hanami with ROM. There are some issues you have to be aware of (e.g. hanami withouth hanami-model and ROM both define the same rake db:migrate task).