# Rom-sql upsert multiple entities by default
**URL:** https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240
**Category:** Usage Stories
**Created:** [April 13, 2018, 7:26am UTC](https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240 "2018-04-13T07:26:58Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@joelvh](https://discourse.rom-rb.org/u/joelvh)
#### Post date: [April 13, 2018, 7:26am UTC](https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240/1 "2018-04-13T07:26:58Z")
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?
```ruby
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!
---
### Author:  [@smaximov](https://discourse.rom-rb.org/u/smaximov)
#### Post date: [April 17, 2018, 7:14am UTC](https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240/2 "2018-04-17T07:14:34Z")
> 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](http://sequel.jeremyevans.net/rdoc/classes/Sequel.html#method-c-split_symbols-3D), or use the preferred method like this:
```ruby
ticker_values.dataset.insert_conflict(
constraint: :idx_ticker_values_uniq,
update: {
value: Sequel[:excluded][:value],
updated_at: Sequel[:excluded][:updated_at]
}
).multi_insert(values)
```
---
### Author:  [@joelvh](https://discourse.rom-rb.org/u/joelvh)
#### Post date: [April 17, 2018, 7:06pm UTC](https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240/3 "2018-04-17T19:06:35Z")
@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?
---
### Author:  [@smaximov](https://discourse.rom-rb.org/u/smaximov)
#### Post date: [April 17, 2018, 8:11pm UTC](https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240/4 "2018-04-17T20:11:28Z")
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).
---
### Author:  [@joelvh](https://discourse.rom-rb.org/u/joelvh)
#### Post date: [April 25, 2018, 7:51pm UTC](https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240/5 "2018-04-25T19:51:41Z")
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?
---
### Author:  [@smaximov](https://discourse.rom-rb.org/u/smaximov)
#### Post date: [April 26, 2018, 7:11am UTC](https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240/6 "2018-04-26T07:11:57Z")
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.
---
### Author:  [@smaximov](https://discourse.rom-rb.org/u/smaximov)
#### Post date: [April 27, 2018, 4:23pm UTC](https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240/7 "2018-04-27T16:23:31Z")
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:
>
> ```sql
> 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:
>
> ```sql
> 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](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:
>
> ```ruby
> 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:
>
> ```ruby
> ...
> .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.
---
### Author:  [@joelvh](https://discourse.rom-rb.org/u/joelvh)
#### Post date: [April 27, 2018, 5:17pm UTC](https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240/8 "2018-04-27T17:17:21Z")
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.
---
### Author:  [@apohllo](https://discourse.rom-rb.org/u/apohllo)
#### Post date: [June 28, 2018, 7:47pm UTC](https://discourse.rom-rb.org/t/rom-sql-upsert-multiple-entities-by-default/240/9 "2018-06-28T19:47:41Z")
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](https://github.com/apohllo/hanami-testbed) 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).