Multiple database in rails

In normal rails application contain one database,but if we want rails application having more than one database that is multiple data base.   we can achieve this using multiple way.one of the way is i am showing here.it’s just 3 steps.


Lets take an e.g Project has many milestone and milestone has many task

In normal scenario model looks like following way

 
class Project < ActiveRecord::Base 
  has_many :milestones 
end
 
class Milestone < ActiveRecord::Base 
  has_many :tasks 
 belongs_to :project 
end
 
class Task < ActiveRecord::Base 
  belongs_to :milestone 
end

Now  we want that milestone is stored on milestone_db database and task on task_db database
Rails multiple database

Now to achieve this we have to do the following steps

1) Edit Database.yml


 milestone_dev: 
 reconnect: false
 encoding: utf8 
 username: <user_name> 
 adapter: mysql 
 database: milestone_db 
 pool: 5 
 password: <password> 

task_dev: 
 reconnect: false 
 encoding: utf8 
 username: <user_name> 
 adapter: mysql 
 database: task_db 
 pool: 5 
 password: <password> 

Now create milestone_db and task_db database.


2) Edit milestone.rb and task.rb


class Milestone < ActiveRecord::Base 
  #add this line to use milestone_db 
  self.establish_connection :milestone_dev 
  has_many :tasks 
  belongs_to :project 
end 
 

class Task < ActiveRecord::Base 
  #add this line to use task_db 
  set_table_name "tasks" 
  belongs_to :milestone 
end

3) After editing model edit migration file


class CreateMilestones < ActiveRecord::Migration 
  def self.connection 
    Milestone.connection 
  end 
 .....
end

Now rails application is ready with multiple database.

12 comments On Multiple database in rails

  • Good write up.

    I was wondering though, how would you handle migrations in this situation?

  • Yes fine. This is saved my time. But schema.rb contains the tables structures of only the database under the default ‘development’ environment. Table structures of other databases was not found. How to generate those too.
    Thanks and Regards

  • Great guide.
    But i’m facing troubles after I did this and I tried to use rake db:migrate.
    All the tables gets created in one database instead of creating one of the models in another specied (like in your example) database.

    Do you have any tips/tricks up your sleeves?

    Best regards

    • Hi,
      sorry for late reply,i have gone through rake task of db migrate it invoke db:schema:dump task.
      It will do
      File.open(ENV['SCHEMA'] || "#{RAILS_ROOT}/db/schema.rb", "w") do |file|
      ActiveRecord::SchemaDumper.dump(ActiveRecord::Base.connection, file)
      end

      so when we are migrating if we want to specify following self.connection
      add this line to migration and it will create another schema file(in this case schma_mile_dev.rb)
      File.open("#{RAILS_ROOT}/db/schema_milestone_dev.rb", "w") do |file|
      ActiveRecord::SchemaDumper.dump(self.connection, file)
      end

      And in schema_milestone_dev.rb


      ActiveRecord::Schema.define(:version => 0) do
      ActiveRecord::Base.establish_connection :milestone_dev
      end

      and run rake db:schema:load SCHEMA="#{RAILS_ROOT}/db/schema_milestone_dev.rb"
      it will load schema to database but in respective database.

      You can further customize rake task to simplify this process

    • Have you edit migration file? Please if not update it.If it is still issue please ping me again i will surely look into it.

  • Hi i have a problem

    I am running 2 rails apps.

    App1 can access App2’s database.
    Now in App1 i have course.rb and in App2 also i have course.rb
    How can i say “self.establish_connection :App2’s database” in App1 course.rb”

    How can i come out of this problem ??

    • Hi,
      Add another configuration in database.yml(APP2 setting) and try it out.
      I don’t know weather it is useful for you or not might be active resource is useful for you.

  • Thanks a lot for sharing the stuff, it was very helpful 🙂

  • hi
    i have develop the train_develop database and also train_pro, train_test,train_test1,train_test2…totally 8 database under a application.all database has same structure(tables everything).would i connect each database as properly?if possible means send a coding hints as soon as possible.thanks

    • Hi,
      Why you need different database for for same structures.If there are multiple environment like staging,development etc
      you can set environment and for respective environment you can add to database.yml file.
      Start server with respective environment.It will pick database as you mention in database.yml
      If this is not a case let me know i will guide you.

  • Thank you very much, I’ve found much more complicated solutions, and this is pretty simple, no monkey patches

  • I could make db:schema:dump work… I did monkey patch with:

    2 namespace :db do
    3 namespace :schema do
    4 task :dump do
    5 File.open(ENV[‘SCHEMA’] || “#{RAILS_ROOT}/db/schema.rb”, “w”) do |file|
    6 next unless (self.connection.is_a? ActiveRecord::ConnectionAdapters::AbstractAdapter rescue nil)
    7 ActiveRecord::SchemaDumper.dump self.connection, file
    8 end
    9 end
    10 end
    11 end

Leave a Reply to Amar Daxini Cancel Reply

Your email address will not be published.

Site Footer