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

http://imnithin.github.io/multiple-database.html – Nithin Feb 28 '15 at 07:36 — Nithin, Feb 28 '15 at 07:36

score 40 · Accepted Answer · answered Dec 02 '09 at 11:50

40

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:

answered Dec 02 '09 at 11:50

Simone Carletti

173,507
49
363
364

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 – marcgg Dec 02 '09 at 11:55
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. – Mika Dec 16 '13 at 09:18
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. – ndnenkov Nov 14 '17 at 09:18

score 28 · Answer 2 · answered Oct 04 '11 at 08:35

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.

score 12 · Answer 3 · answered Feb 25 '14 at 17:24

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"
    )

score 4 · Answer 4 · answered Dec 02 '09 at 12:08

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:

Allows you to easily manage AR models’ connections (switch_connection_to method)

Allows you to switch AR models’ default connections to a separate servers/databases

Allows you to easily choose where your query should go (on_* methods family)

Allows you to automatically send read queries to your slaves while masters would handle all the updates.

Adds multiple databases migrations to ActiveRecord

The plugin has been killed by the author, as of 02.01.2015, no-one has stepped in to maintain it. — Smar, Feb 02 '15 at 13:14

score 2 · Answer 5 · answered Feb 12 '21 at 14:59

2

As of Rails 6, multiple databases are supported: https://guides.rubyonrails.org/active_record_multiple_databases.html#generators-and-migrations

Sorry for the late and obvious answer, but figured it's viable since it's supported now.

answered Feb 12 '21 at 14:59

NM Pennypacker

6,704
11
36
38

score 2 · Answer 6 · answered Dec 14 '11 at 17:09

2

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

answered Dec 14 '11 at 17:09

Steven Soroka

19,404
4
52
40

4

Rails do use connection pool, it should prevent of overuse the certain connection limit. – Anatoly Jan 31 '14 at 23:02

score 1 · Answer 7 · answered Jul 23 '17 at 21:57

in your config/database.yml do something like this

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5

development:
  <<: *default
  database: mysite_development

test:
  <<: *default
  database: mysite_test

production:
  <<: *default
  host: 10.0.1.55
  database: mysite_production
  username: postgres_user
  password: <%= ENV['DATABASE_PASSWORD'] %>

db2_development:
  <<: *default
  database: db2_development

db2_test:
  <<: *default
  database: db2_test

db2_production:
  <<: *default
  host: 10.0.1.55
  database: db2_production
  username: postgres_user
  password: <%= ENV['DATABASE_PASSWORD'] %>

then in your models you can reference db2 with

class Customers < ActiveRecord::Base
  establish_connection "db2_#{Rails.env}".to_sym
end

score 1 · Answer 8 · answered Jan 13 '18 at 06:26

What you've described in the question is multitenancy (identically structured databases with different data in each). The Apartment gem is great for this.

For the general question of multiple databases in Rails: ActiveRecord supports multiple databases, but Rails doesn’t provide a way to manage them. I recently created the Multiverse gem to address this.

score 1 · Answer 9 · answered Apr 02 '19 at 17:57

The best solution I have found so far is this:

There are 3 database architectures that we can approach.

Single Database for Single Tenant
Separate Schema for Each Tenant
Shared Schema for Tenants

Note: they have certain pros and cons depends on your use case.

I got this from this Blog! Stands very helpful for me.

You can use the gem Apartment for rails

Video reference you may follow at Gorails for apartment

Multiple databases in Rails

9 Answers9

Linked