Rails - same model with multiple databases (read only)

Question

I have multiple databases, with the same tables and columns names inside (but different unique ids and rows..). Instead of having one huge database with all the rows, it is splitted into different databases. This is something I cannot change (you can think of this as collecting the same data from different countries, but each country has its own database). These databases are "read only" - meaning, when I use them via Rails, it is only to display the data (or save it on a local database) - I do not change the data on any of the remote databases.

My problem is, that I need to have 1 model in rails, that collects the data from all these databases. We want to be able to do something like:

OneModelAllDB.select_where(...)

and not to split each search to:

data1 = FirstDBModel.select_where(same_condition)
data2 = SecondDBModel.select_where(same_condition)
...
data = data1 + data2 + ...

Also, if I want to make 1 model with threads (parallel searches), there is a problem:

[:db1, :db2].each do |db|
  threads << Thread.new do
    self.establish_connection(db)
    results[db] = self.where(bla_bla_condition)
  end
end

because the modification of the connection is not threadsafe...

Is there any way to do so? As we do not change any of these databases, and each row has a unique id, there shouldn't be any problem to get the data from different databases and join it together...

Thanks!

Answer 1

Rails active records are abstracted from the database connection. The connection is managed separately. You won't have something like, FirstDB.select_where(...).

However, what you can try is define your different databases in your config/database.yml, for example:

db1:
  adapter: postgresql
  encoding: unicode
  database: database1
  pool: 5
  username: xxx
  password: xxx

db2:
  adapter: postgresql
  encoding: unicode
  database: database2
  pool: 5
  username: xxx
  password: xxx

Then in your code, you would use ActiveRecord::Base.establish_connection to re-establish the connection to the database you want before running the query. If your active record model is called Foo, then:

Foo.establish_connection(:db1)
data1 = Foo.where(...)

Foo.establish_connection(:db2)
data2 = Foo.where(...)

I have not tried this in detail myself, but it should work something like that.