Connecting Rails 3.1 with Multiple Databases

Question

At ShowNearby we have been doing a very big migration to RoR 3.1 from PHP and we are facing several problems that may be some of you have solved before.

We have big amounts of data and we decided to segregate our DB into several DBs that we can handle separately. For example, our accounts, places, logs and others are split into several databases

We need to get migrations, fixtures, models, to play nicely, and so far it has been quite messy. Some of our requirements for a solution to be acceptable:

• one model should relate to one tables in one of the databases.
• rake db:drop - should drop all the database env we specify in database.yml
• rake db:create - should create all the database env we specify in database.yml
• rake db:migrate - should run migrations to the various databases
• rake db:test - should grab fixtures and drop them into the various databases and test unit/function/etc

We are considering setting separate rails projects per each database and connecting them with ActiveResource, but we feel this is not very efficient. Have any of you deal with a similar problem before?

Manus

We're considering upgrading from a PHP application to a rails one as well; did you have any luck with this? – Overture

Hi @Tommyixi: this was very long time ago and a lot has changed since then. In retrospect I think now that it's a better solution to aggregate them into one database than splitting it into multiple databases – Manus

Answer 1

To Wukerplank's answer, you can also put the connection details in database.yml like usual with a name like so:

log_database_production:
  adapter: mysql
  host: other_host
  username: logmein
  password: supersecret
  database: logs

Then in your special model:

class AccessLog < ActiveRecord::Base
  establish_connection "log_database_#{Rails.env}".to_sym
end

To keep those pesky credentials from being in your application code.

Edit: If you want to reuse this connection in multiple models, you should create a new abstract class and inherit from it, because connections are tightly coupled to classes (as explained here, here, and here), and new connections will be created for each class.

If that is the case, set things up like so:

class LogDatabase < ActiveRecord::Base
  self.abstract_class = true
  establish_connection "log_database_#{Rails.env}".to_sym
end

class AccessLog < LogDatabase
end

class CheckoutLog < LogDatabase
end

Doby

How to account for the environment change? So for instance in development I want to establish_connection with the log_dev database but in production I want to establish_connection with the log database. Can I just make a call to Rails.env ? – Yodle

@AzizLight establish_connection "log_database_#{Rails.env}" – Doby

Be forewarned. It appears that using this method leaves the connections on the additional databases open without reusing them. This will grind your application to a halt under heavy load. – Kidwell

@Kidwell Good point. I think you are referring to this behavior: github.com/rails/rails/issues/7019 The connection becomes coupled to the class; so if you need to reuse the connection, you should establish it on a abstract class and inherit from it instead of AR::Base. I updated my answer to reflect this. – Doby

Yep. Perfect. I was going to respond with this answer if you didn't shortly. :P – Kidwell

In case it throws anyone else off: The abstract class may not be loaded automatically. I needed to require the file it lives in myself at the top of each model's file. – Transcendental

Oh, that was because I didn't stick to the file naming conventions properly. I refactored and now it's autoloading properly. – Transcendental

Answer 2

Connecting to different databases is quite easy:

# model in the "default" database from database.yml
class Person < ActiveRecord::Base

  # ... your stuff here

end

# model in a different database
class Place < ActiveRecord::Base

  establish_connection (
    :adapter  => "mysql",
    :host     => "other_host",
    :username => "username",
    :password => "password",
    :database => "other_db"
  )

end

I would be wary of setting up multiple Rails projects as you will add a lot of overhead to data retrieval for your controllers, which could make things slow.

As for your questions about migrations, fixtures, models etc.: I don't think there will be an easy way, so please post separate questions and be as specific as you can.

Consolidating the DBs into one is not an option? It would make your life a lot easier!

Eustace

trouble is that connection pooling will not be used properly with the above sample – Antonio

Answer 3

Found a great post that will point others to the right way of doing this check out http://blog.bitmelt.com/2008/10/connecting-to-multiple-database-in-ruby.html

Set it up something like this:

database.yml (db config file)

support_development:
    adapter: blah
    database: blah
    username: blah
    password: blah

support_base.rb (a model file)

class SupportBase < ActiveRecord::Base
    self.abstract_class = true #important!
    establish_connection("support_development")
end

tst_test.rb (a model file)

class TstTest < SupportBase 
    #SupportBase not ActiveRecord is important!

    self.table_name = 'tst_test'

    def self.get_test_name(id)
        if id = nil
            return ''
        else
            query = "select tst_name from tst_test where tst_id = \'#{id}\'"
            tst = connection.select_all(query) #select_all is important!
            return tst[0].fetch('tst_name')
        end
    end
end

PS, this really doesn't cover migrations, I don't think you can do migrations on more than one DB with rake (although I'm not sure that is a hard 'cannot do', it may be possible). This was just a great way to connect and query other DBs that you don't control.

Discus

Answer 4

You might also want to append the Rails environment, so your development and test databases are not the same.

establish_connection "legacy_#{Rails.env}"

Speculative

Answer 5

The following article suggests defining new Rake tasks to achieve migrations against multiple databases. Each task sets up its own connection and then executes the migration with this connection and the specific database folder.

It also defines a familiar db:migrate that calls the two other tasks.

Including here incase the link becomes unavailable:

desc "Migrate the database through scripts in db/migrate directory."

namespace :db do
  task :migrate do
    Rake::Task["db:migrate_db1"].invoke
    Rake::Task["db:migrate_db2"].invoke
  end

  task :migrate_db1 do
    ActiveRecord::Base.establish_connection DB1_CONF
    ActiveRecord::Migrator.migrate("db/migrate/db1/")
  end

  task :migrate_db2 do
    ActiveRecord::Base.establish_connection DB2_CONF
    ActiveRecord::Migrator.migrate("db/migrate/db2/")
  end
end

Source: Ruby on Rails Connect to Multiple Databases and Migrations

Snatch

Answer 6

Hey this post is old but I've found a solution working on Rails 3.2 that might help someone else. https://mcmap.net/q/266425/-using-rails-migration-on-different-database-than-standard-quot-production-quot-or-quot-development-quot

Oshiro