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

Solution 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

Solution 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.

Solution 3

I got past this by adding this to the top of my models using the other database

class Customer < ActiveRecord::Base
  ENV["RAILS_ENV"] == "development" ? host = 'devhost' : host = 'prodhost'

  self.establish_connection(
      :adapter  => "mysql",
      :host     => "localhost",
      :username => "myuser",
      :password => "mypass",
      :database => "somedatabase"
    )

Solution 4

You should also check out this project called DB Charmer: http://kovyrin.net/2009/11/03/db-charmer-activerecord-connection-magic-plugin/

DbCharmer is a simple yet powerful plugin for ActiveRecord that does a few things:

1. Allows you to easily manage AR models’ connections (switch_connection_to method)
2. Allows you to switch AR models’ default connections to a separate servers/databases
3. Allows you to easily choose where your query should go (on_* methods family)
4. Allows you to automatically send read queries to your slaves while masters would handle all the updates.
5. Adds multiple databases migrations to ActiveRecord

Solution 5

It's worth noting, in all these solutions you need to remember to close custom database connections. You will run out of connections and see weird request timeout issues otherwise.

An easy solution is to clear_active_connections! in an after_filter in your controller.

after_filter :close_custom_db_connection

def close_custom_db_connection
  MyModelWithACustomDBConnection.clear_active_connections!
end

View more solutions

Comments

[marcgg]

Couldnt have said it better. Environments also makes it easier to have a different db system between production and development for instance. I wouldn't recommend doing this thought

[Mika]

I plan to do a similar choice because I expect certain database tables to have hundreds of millions of records. Splitting the database into separate instances per client would allow me to have a single application server with multiple database servers.

[Anatoly]

Rails do use connection pool, it should prevent of overuse the certain connection limit.

[Smar]

The plugin has been killed by the author, as of 02.01.2015, no-one has stepped in to maintain it.

[ndnenkov]

Your last two links are no longer relevant. And the first one might become outdated with a new version of rails. Please inline the related pieces of code in the answer.