ROM.rb is an alternative open-source data mapper and persistence library for Ruby, its an alternative to using something like ActiveRecord and comes with its own suite of opinions. UPSERT is also a particularly handy Postgres feature for effectively merging via either insertion or update a bunch of data to a table. Let's take a look at how we might get both to play nice with each other.
Commands are a powerful part of the ROM API to enable changes to your data. Recently I found myself working with ROM on a project and needing to leverage UPSERT, whilst ROM comes with support for Postgres UPSERT out of the box - it needs to be opted-in to and it was not particularly clear from reading through the docs and the source code how we might approach supplying our own conflict strategies and still applying the schema we had defined including its strict types.

Fortunately, this is actually pretty straightforward and can be accomplished in a few steps:

• Create a custom command
• Register the custom command
• Making the custom command available to our repository

Creating a custom command

Although we can call upsert directly with relation.upsert (where relation is your .. relation). By default this has a ON CONFLICT DO NOTHING clause, which in my case, wasn't really what I wanted.

Looking at the docs for both the existing upsert command and custom command types we can simply choose to extend the default command class and in doing so, provide some relevant options depending on our outcome

    class CustomUpsert < ROM::SQL::Postgres::Commands::Upsert
      relation :users
      register_as :upsert
      conflict_target :name
      update_statement  email: Sequel[:excluded][:email], title: Sequel[:excluded][:title]
    end

The API gives us 4 options via a macro to configure our upsert for our own purposes:

• conflict_target
• update_statement
• update_where
• constraint

We also need to use the relation and register_as options to help make our command available when we want to call it on our relation or via our repository and how we want to call it.

Note in our case with the update_statement because we want to merge the incoming values for email and title we need to tell our upsert how to retrieve them via our update_statement and because ROM uses Sequel under the hood we're simply passing in the Sequel code to pull in those values.

Register the custom command

With our hard work out of the way and with a new shiny, custom command we can register our command via the ROM configuration block enabling us to access it within our application - if you have autoloading configured, this step may not be necessary.

    config.register_command(CustomUpsert)

Making the custom command available to our repository

Because in our case, we have previously used the register_as macro on our custom command and given it the value of upsert we can now leverage this command on our repo with the commands macro like so:

class UserRepository < Repository[:users]
  commands :upsert
end

With this set of changes we can now reliably use our upsert with the query structure we need via powerful custom commands.

Does this work for bulk upsert? Or how I learned to stop worrying and leverage Sequel instead.

Unfortunately, the built-in upsert command will do multiple individual inserts or updates instead of a bulk upsert. This is less than ideal, but is also something we can overcome by working directly with Sequel and the internals of the command class we're extending

module PerformCore
  module Persistence
    module Commands
      class UserUpsert < ROM::SQL::Postgres::Commands::Upsert
        relation :users
        register_as :upsert
        constraint :users_pk
        update_statement email: Sequel[:excluded][:email]

        def execute(tuples)
          relation.dataset.returning(*relation.schema.qualified_projection).insert_conflict(upsert_options).multi_insert(tuples)
        end
      end
    end
  end
end

Let's break this one down -

relation.dataset.returning(*relation.schema.qualified_projection)

Means that we can return a collection of entities from our bulk upsert and then

insert_conflict(upsert_options).multi_insert(tuples)

calls out to Sequel's insert_conflict API which ROM's upsert uses under the hood, passes our options constraint and update_statement (along with any other of our 4 options specified earlier in the post) by leveraging the upsert_options method defined on ROM::SQL::Postgres::Commands::Upsert.

Finally we use multi_insert to handle bulk insertion or update

Happy days

By leveraging ROM's command API we can easily support our bulk upsert needs and have well-defined commands to work with our repositories and/or relations that will also properly conform to your specific schema.