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