Connecting Rails 3.1 with Multiple Databases

Question

At ShowNearby, we are very much moving to RoR 3.1 with PHP, and we are facing some problems that some of you have already solved.

We have a large amount of data, and we decided to divide our database into several databases, which we can process separately. For example, our accounts, places, magazines and other sections are divided into several databases.

We need to get migration, fixtures, models to play beautifully, and so far it has been pretty dirty. Some of our requirements for an acceptable solution:

• one model should belong to one table in one of the databases.
• rake db: drop - should delete the entire env database, which we will specify in database.yml
• rake db: create - should create the entire env database, which we will specify in database.yml
• rake db: migrate - you should run migrations to various databases.
• rake db: test - should capture the lights and drop them into various databases and the test block / function / etc

We are considering the possibility of creating separate rail projects for each database and connecting them to ActiveResource, but we believe that this is not very effective. Have any of you come across a similar problem before?

Thank you so much!

Answer 1

In response to Wukerplank, you can also put connection data in database.yml, as usual, with this name:

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 

So that these annoying credentials are in your application code.

Edit: If you want to reuse this connection in several models, you must create a new abstract class and inherit it, because the connections are closely related to the classes (as explained here , here , and here ), and new connections will be created for each class.

If so, set things up like this:

 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 

Answer 2

Connecting to various databases is quite simple:

 # 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 creating multiple Rails projects, since you added a lot of overhead for finding data for your controllers, which might slow down.

Regarding 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 possible.

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

Answer 3

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

Set up something like this:

database.yml (db configuration file)

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

support_base.rb (model file)

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

tst_test.rb (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 apply to migration, I don’t think that you can perform migrations on more than one database with a rake (although I’m not sure that it’s hard “can not”, maybe it is possible), It was just a great way connect and request other databases that you do not control.

Answer 4

You can also add a Rails framework, so your development and testing databases do not match.

 establish_connection "legacy_#{Rails.env}" 

Answer 5

the following article proposes defining new Rake tasks to achieve migration across multiple databases. Each task establishes its own connection and then migrates with that connection and a specific database folder.

It also defines the familiar db:migrate , which raises two other tasks.

Including here, the link is not available:

 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 Connecting to Multiple Databases and Migrations

Answer 6

Hey, this post is old, but I found a solution working on Rails 3.2 that might help someone else. stack overflow