Is it possible to inner join across multiple databases in Rails?

Question

I'm having difficult accessing data with a has_many :through association where some of the tables live in a separate database.

# database_one
class Input < ApplicationRecord
  belongs_to :user      # Works great
end

# database_two
class User < AbstractClass
  belongs_to :group     # Works great
  has_many :inputs      # Works great
end

# database_two
class Group < AbstractClass
  has_many :users                     # Works great
  has_many :inputs, through: :users   # Does not work at all
end

class AbstractClass < ApplicationRecord
  self.abstract_class = true
  establish_connection "database_two_#{Rails.env}".to_sym
end

So with the code as it is above, I can do the following:

Group.first
=> #<Group id: 1...

User.first
=> #<User id: 1...

User.first.inputs
=> #<ActiveRecord::Associations::CollectionProxy []>

Group.first.users
=> #<ActiveRecord::Associations::CollectionProxy []>

But it won't let me do the following:

Group.first.inputs
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "users" does not exist
LINE 1: SELECT  "inputs".* FROM "inputs" INNER JOIN "users" ON "inpu...
                                                ^
: SELECT  "inputs".* FROM "inputs" INNER JOIN "users" ON "inputs"."user_id" = "users"."id" WHERE "users"."group_id" = $1 LIMIT $2

It looks like it's not possible to do an INNER JOIN across two databases? Is there anything I can do to alleviate this? I've tried adding this method to the AbstractClass but it didn't solve anything unfortunately:

def self.table_name_prefix
  "database_two_#{Rails.env}."
end

As a workaround, I have added the following to the Group model, but this isn't the solution I'm looking for.

def inputs
  Input.where(id: users.ids)
end

Answer 1

I don't think it is possible to join two different tables in one query. What you can probably do instead is use Ruby to get your final collection. Get the collection from one DB with one query and then another collection from the other query. Then use Ruby to select/filter from these two collections. I hope this helps you.

Answer 2

I don't know about PostgreSQL, but it works on MySQL 5.7 and Rails v7 if you manually build the join:

class Legacy::Site < Legacy::Record
  scope :joins_new_site, -> {
    # Workaround to join different databases.
    new_db = ::Site.connection.current_database
    new_table = ::Site.table_name
    joins("INNER JOIN #{new_db}.#{new_table} ON #{new_table}.legacy_site_id = #{table_name}.site_id")
  }

  scope :enabled, -> { joins_new_site.merge(::Site.enabled) }
end

I haven't tried a 'has many through' join, but the same principles apply, just explicitly name the database.

Answer 3

This is how I do this (supposing that both databases runs on the same host):

1/ Have a database.yml file for your second DB This is important as it will allow you to connect to your second database the Rails way. I'm sure you already have it setup but for future developers stumbling upon this question, it can be done as follow:

config/database.yml

development:
  adapter: postgresql
  encoding: unicode
  database: database_one_development
  pool: 5
  username: USERNAME
  password: PASSWORD

test:
  adapter: postgresql
  encoding: unicode
  database: database_one_test
  pool: 5
  username: USERNAME
  password: PASSWORD

production:
  adapter: postgresql
  encoding: unicode
  database: database_one_production
  pool: 5
  username: USERNAME
  password: PASSWORD

config/database_two.yml

development:
  adapter: postgresql
  encoding: unicode
  database: database_two_development
  pool: 5
  username: USERNAME
  password: PASSWORD

test:
  adapter: postgresql
  encoding: unicode
  database: database_two_test
  pool: 5
  username: USERNAME
  password: PASSWORD

production:
  adapter: postgresql
  encoding: unicode
  database: database_two_production
  pool: 5
  username: USERNAME
  password: PASSWORD

config/initializers/database_connector.rb

DATABASE_ONE_DB = YAML.load_file(File.join(Rails.root, "config", "database.yml"))[Rails.env.to_s]
DATABASE_TWO_DB = YAML.load_file(File.join(Rails.root, "config", "database_two.yml"))[Rails.env.to_s]

2/ Set your AbstractClass as follow:

class DatabaseTwoModel < ApplicationRecord
  self.abstract_class = true
  establish_connection DATABASE_TWO

  def self.table_name_prefix
    "database_two_#{Rails.env}."
  end
end

And your models as follow:

class User < DatabaseTwoModel
  belongs_to :group
  has_many :inputs
end

class Group < DatabaseTwoModel
  has_many :users
  has_many :inputs, through: :users
end

3/ To avoid any confusion, I create another class for the models belonging to the database_one

class DatabaseOneModel < ApplicationRecord
  self.abstract_class = true

  def self.table_name_prefix
    "database_one_#{Rails.env}."
  end
end

Your Input model should then inherit from this class like this:

class Input < DatabaseOneModel
  belongs_to :user      # Works great
end

It does work great by itself, but when doing inner join, it might mess up the SQL query.

4/ Rspec

If you are using Rspec, you need to add this to your rails_helper.rb file:

database_one = Rails.configuration.database_configuration[Rails.env]
database_two = YAML.load_file(File.join(Rails.root, "config", "database_two.yml"))[Rails.env]

# start by truncating all the tables but then use the faster
# transaction strategy the rest of the time.
config.before(:suite) do
  ActiveRecord::Base.establish_connection database_two
  DatabaseCleaner.clean_with(:truncation)
  DatabaseCleaner.strategy = :transaction
  ActiveRecord::Base.establish_connection database_one
  DatabaseCleaner.clean_with(:truncation)
  DatabaseCleaner.strategy = :transaction
end

This cleans both the databases and your tests will all run smoothly.

You should be able to run your query as well as your has_many through.

Different Hosts

If your databases are on different hosts, you can look into the St-Elsewhere gem. It is old, but gives a good understanding on how to deal with this problem.

I hope this helps!