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.
Top comments (0)