Associations between multiple databases

Issues
1

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.

1 Like
2

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

3

Did you solve your problems?

4

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.

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`">>>]}>]>