Rails - same model with multiple databases (read-only)
I have multiple databases with the same table and column names inside (but different unique ids and strings ..). Instead of having one huge database with all the rows, it is split into different databases. This is something I cannot change (you can think of it 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 through Rails, it should only display the data (or store it in a local database). I am not changing data on any of the remote databases.
My problem is that I need to have 1 model in rails that collects data from all these databases. We want to be able to do something like:
OneModelAllDB.select_where(...)
and don't split each search into:
data1 = FirstDBModel.select_where(same_condition) data2 = SecondDBModel.select_where(same_condition) ... data = data1 + data2 + ...
Also, if I want to create 1 model with streams (parallel search) the problem arises:
[:db1, :db2].each do |db|
threads << Thread.new do
self.establish_connection(db)
results[db] = self.where(bla_bla_condition)
end
end
because changing the connection is not thread safe ...
Is there a way to do this? Since we are not changing any of these databases, and each row has a unique ID, there should be no problem to get data from different databases and merge them together ...
Thank!
source to share
Rails active records abstract away from the database connection. The connection is managed separately. You won't have something like FirstDB.select_where(...)
.
However, you can try to define different databases in 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 should use ActiveRecord :: Base.establish_connection to reestablish the connection to the database you want before running the request. If your active record model is named Foo
then:
Foo.establish_connection(:db1)
data1 = Foo.where(...)
Foo.establish_connection(:db2)
data2 = Foo.where(...)
I haven't tried it in detail myself, but it should work something like this.
source to share