Multiple databases in Rails

Question

Can this be done? In a single application, that manages many projects with SQLite. What I want is to have a different database for each project my app is managing.. so multiple copies of an identically structured database, but with different data in them. I'll be choosing which copy to use base on params on the URI.

This is done for 1. security.. I'm a newbe in this kind of programming and I don't want it to happen that for some reason while working on a Project another one gets corrupted.. 2. easy backup and archive of old projects

Answer 1

Rails by default is not designed for a multi-database architecture and, in most cases, it doesn't make sense at all. But yes, you can use different databases and connections.

Here's some references:

• ActiveRecord: Connection to multiple databases in different models
• Multiple Database Connections in Ruby on Rails
• Magic Multi-Connections

Answer 2

If you are able to control and configure each Rails instance, and you can afford wasting resources because of them being on standby, save yourself some trouble and just change the database.yml to modify the database connection used on every instance. If you are concerned about performance this approach won't cut it.

For models bound to a single unique table on only one database you can call establish_connection inside the model:

establish_connection "database_name_#{RAILS_ENV}" 

As described here: http://apidock.com/rails/ActiveRecord/Base/establish_connection/class

You will have some models using tables from one database and other different models using tables from other databases.

If you have identical tables, common on different databases, and shared by a single model, ActiveRecord won't help you. Back in 2009 I required this on a project I was working on, using Rails 2.3.8. I had a database for each customer, and I named the databases with their IDs. So I created a method to change the connection inside ApplicationController:

def change_database database_id = params[:company_id]     return if database_id.blank?      configuration = ActiveRecord::Base.connection.instance_eval { @config }.clone     configuration[:database] = "database_name_#{database_id}_#{RAILS_ENV}"      MultipleDatabaseModel.establish_connection configuration end 

And added that method as a before_filter to all controllers:

before_filter :change_database 

So for each action of each controller, when params[:company_id] is defined and set, it will change the database to the correct one.

To handle migrations I extended ActiveRecord::Migration, with a method that looks for all the customers and iterates a block with each ID:

class ActiveRecord::Migration     def self.using_databases *args         configuration = ActiveRecord::Base.connection.instance_eval { @config }         former_database = configuration[:database]          companies = args.blank? ? Company.all : Company.find(args)          companies.each do |company|             configuration[:database] = "database_name_#{company[:id]}_#{RAILS_ENV}"             ActiveRecord::Base.establish_connection configuration              yield self         end          configuration[:database] = former_database         ActiveRecord::Base.establish_connection configuration     end end 

Note that by doing this, it would be impossible for you to make queries within the same action from two different databases. You can call change_database again but it will get nasty when you try using methods that execute queries, from the objects no longer linked to the correct database. Also, it is obvious you won't be able to join tables that belong to different databases.

To handle this properly, ActiveRecord should be considerably extended. There should be a plugin by now to help you with this issue. A quick research gave me this one:

DB-Charmer: http://kovyrin.github.com/db-charmer/

I'm willing to try it. Let me know what works for you.