# Associations between multiple databases

**URL:** <https://discourse.rom-rb.org/t/associations-between-multiple-databases/577>\
**Category:** Issues\
**Created:** [December 30, 2022, 9:33pm UTC](https://discourse.rom-rb.org/t/associations-between-multiple-databases/577 "2022-12-30T21:33:19Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![wbr](https://avatars.discourse-cdn.com/v4/letter/w/b3f665/32.png) [@wbr](https://discourse.rom-rb.org/u/wbr)\
**Post date:** [December 30, 2022, 9:33pm UTC](https://discourse.rom-rb.org/t/associations-between-multiple-databases/577/1 "2022-12-30T21:33:19Z")

</div>

Hi, I’ve been using ROM for several years but never between multiple databases at once, until now. I’m trying to create an association between a postgres relation and a sqlite relation. My container has two gateways, one for the postgres db and one for the sqlite db. I set the gateway for each relation with `gateway :name_of_gateway`. Then I create basic `has_many` and `belongs_to` associations between the two relations.

However, when I compose a query using `combine(:other_relation)`, I get an exception that shows the sql adapter looking for the tables in the wrong database. I can’t find any documentation on how this should work, so I could be going about it all wrong. Happy to post more explicit examples of my code, but thought I’d ask first if there’s a proper way to make this work. Thanks.

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [January 2, 2023, 8:43am UTC](https://discourse.rom-rb.org/t/associations-between-multiple-databases/577/2 "2023-01-02T08:43:27Z")

</div>

Double check that the gateways are correctly configured by inspecting relation’s dataset objects. This should just work.

---

<div class="post-metadata">

**Author:** ![wuarmin](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/wuarmin/32/233_2.png) [@wuarmin](https://discourse.rom-rb.org/u/wuarmin)\
**Post date:** [January 11, 2023, 7:29am UTC](https://discourse.rom-rb.org/t/associations-between-multiple-databases/577/3 "2023-01-11T07:29:07Z")

</div>

Did you solve your problems?

---

<div class="post-metadata">

**Author:** ![wbr](https://avatars.discourse-cdn.com/v4/letter/w/b3f665/32.png) [@wbr](https://discourse.rom-rb.org/u/wbr)\
**Post date:** [January 11, 2023, 10:58pm UTC](https://discourse.rom-rb.org/t/associations-between-multiple-databases/577/4 "2023-01-11T22:58:45Z")

</div>

Thanks @solnic and @wuarmin , I was not able to solve this issue. I worked around it by creating a method on the entity that pulls in the associated records when needed. Here’s what works and what doesn’t.

Works:

- Basic CRUD on any one table from either of two gateways (postgres, sqlite)
- Using `combine` method with associations between two tables on the same gateway.

Doesn’t work:

- Using `combine` method with associations between two tables on different gateways. The sql of the `ROM::Relation::Combined` object shows it is attempting to perform `INNER JOIN` between the two tables on different gateways.

I can post my setup and the errors I’m getting, but meanwhile, here’s a raw copy-paste of a `ROM::Relation::Combined` object that shows attempted joins between two gateways. Sorry it’s not in the most useful/readable format here.

```auto
irb(main):073:0> Rom::Users.by_pk("123456789").combine(:servers)
=> #<ROM::Relation::Combined root=#<Rom::UserRelation name=ROM::Relation::Name(users) dataset=#<Sequel::Postgres::Dataset: "SELECT \"users\".\"id\", \"users\".\"active\", \"users\".\"birth_date\", \"users\".\"clean_speak_id\", \"users\".\"data\", \"users\".\"expiry\", \"users\".\"first_name\", \"users\".\"full_name\", \"users\".\"image_url\", \"users\".\"insert_instant\", \"users\".\"last_name\", \"users\".\"last_update_instant\", \"users\".\"middle_name\", \"users\".\"mobile_phone\", \"users\".\"parent_email\", \"users\".\"tenants_id\", \"users\".\"timezone\" FROM \"users\" WHERE (\"users\".\"id\" = '4eda2de3-ea0a-4ff3-a7dd-4d2e7d224ce7') ORDER BY \"users\".\"id\"">> nodes=[#<ROM::Relation::Curried relation=#<Rom::ServerRelation name=ROM::Relation::Name(servers on jam_servers) dataset=#<Sequel::SQLite::Dataset: "SELECT `jam_servers`.`created_at`, `jam_servers`.`updated_at`, `jam_servers`.`id`, `jam_servers`.`user_id`, `jam_servers`.`server_name`, `jam_servers`.`avail_zone` FROM `jam_servers` INNER JOIN `users` ON (`users`.`id` = `jam_servers`.`user_id`) ORDER BY `jam_servers`.`id`">> options={:relation=>#<Rom::ServerRelation name=ROM::Relation::Name(servers on jam_servers) dataset=#<Sequel::SQLite::Dataset: "SELECT `jam_servers`.`created_at`, `jam_servers`.`updated_at`, `jam_servers`.`id`, `jam_servers`.`user_id`, `jam_servers`.`server_name`, `jam_servers`.`avail_zone` FROM `jam_servers` INNER JOIN `users` ON (`users`.`id` = `jam_servers`.`user_id`) ORDER BY `jam_servers`.`id`">>, :view=>:preload_assoc, :arity=>2, :curry_args=>[#<ROM::SQL::Associations::OneToMany definition=#<ROM::Associations::Definitions::OneToMany source=ROM::Relation::Name(users) target=ROM::Relation::Name(servers) result=:many> source=#<Rom::UserRelation name=ROM::Relation::Name(users) dataset=#<Sequel::Postgres::Dataset: "SELECT \"users\".\"id\", \"users\".\"active\", \"users\".\"birth_date\", \"users\".\"clean_speak_id\", \"users\".\"data\", \"users\".\"expiry\", \"users\".\"first_name\", \"users\".\"full_name\", \"users\".\"image_url\", \"users\".\"insert_instant\", \"users\".\"last_name\", \"users\".\"last_update_instant\", \"users\".\"middle_name\", \"users\".\"mobile_phone\", \"users\".\"parent_email\", \"users\".\"tenants_id\", \"users\".\"timezone\" FROM \"users\" ORDER BY \"users\".\"id\"">> target=#<Rom::ServerRelation name=ROM::Relation::Name(servers on jam_servers) dataset=#<Sequel::SQLite::Dataset: "SELECT `jam_servers`.`created_at`, `jam_servers`.`updated_at`, `jam_servers`.`id`, `jam_servers`.`user_id`, `jam_servers`.`server_name`, `jam_servers`.`avail_zone` FROM `jam_servers` ORDER BY `jam_servers`.`id`">>>]}>]>

```