Connecting Ruby on Rails to multiple databases

It’s not dicussed very often, but it is possible to connect a Ruby on Rails app to multiple backend databases. This, paired with the importation of a pre-existing data as outlined in a previous blog post,  is useful in scenarios where your application is required to read data from a shared databases populated by another application.

We start off by defining a new database connection in the ../config/database.yml file. For example, say we would like to connect to a database called “asterisk” on the local PC. We would simply append the following:

asteriskDB:
adapter: mysql2
encoding: utf8
database: asterisk
pool: 5
username: root
password: password
socket: /var/lib/mysql/mysql.sock

This defines a connection named asteriskDB. Next we need to instruct rails to use this new connection. In the models folder, create a new file (which I called asterisk_base.rb) and insert the following:

class AsteriskBase < ActiveRecord::Base
self.abstract_class = true
establish_connection “asteriskDB”
end

The above code defines a new class (AsteriskBase) which inherits from ActiveRecord::Base as other models do. The second line allows you to re-use this class as a parent to other classes. Any child class making use of this inheritance will automatically get the property defined in the third line (establish_connection “asteriskDB”), which points your model to the asteriskDB connection previously defined in database.yml. So we are now free to define additional model classes which use this second database. For example, to use the table “trunks” for the asterisk database, we would create a file “trunks.rb” in the models folder, and insert the following:

class Trunks < AsteriskBase
end

It’s a very simple model class definition, with the essential difference that it inherits “AsteriskBase” rather than the usual ActiveRecord::Base. You can then use the model in the usual ways the Active Record allows you.

One thing you may run across in such a scenario is that the two (or more) database that you are using, contain tables with the same name, and you need to use both these tables. The conventional advice is to use namespaces, however this method didn’t quite work for me, so I ended up avoiding namespaces completely and instead using the set_table_name property to define a different class name and instructing rails to avoid using it’s normal rules of determining the table name via the class name and explicitly stating the table name:

class Trunks2 < AsteriskBase

set_table_name “trunks”

end

Note, the set_table_name has been deprecated in favor of self.table_name:

class Trunks2 < AsteriskBase

self.table_name = “trunks”

end

Another note… sometime Rails’ naming convention can be frustrating. When dealing with the above scenario, to give a concrete example, I wanted to define the following in a controller:

def listAsteriskTrunks
@asteriskTrunks=AsteriskTrunks.all
end

AsteriskTrunks was a model which is pointing to a second database as outlined in the post. However, Rails would return “uninitialized constant”. Clearly it couldnt find the corresponding model file. The correct model file name turned out to be “asterisk_trunks.rb“. Note how the name of the file and the class called in the controller are similar, but the filename is all lowercase and separated by and underscore.