Connecting to multiple DBs in Ruby On Rails

Ruby on Rails is very handy framework for various situations of everyday in a  WEB developer day. However some time it is necessary to do some not common tasks, such as connect our application in multiple databases.

Image from www.askqtp.com

So here we go! I will show to you how to set ActiveRecord (Class responsible to abstract the database level) to connect to multiple relational databases (In a future post I'll show you how to work with NoSQL), similar, or differents like SQLite, MySQL, PostgreSQL, OracleSQL, SQL Server ...

Application Example: I want to create an application to manage projects. However I want to put user table in a external data base of application to other future applications also use this unified user base.

Step 1: Configure your database.yml

The first step is to configure your "config/database.yml" to make their connections, for example, we have this file:

# SQLite version 3.x
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 5
  timeout: 5000

This is the default setting with a base in SQLite, by default, configured with the name of the execution environments. Now we build a new configuration, I called it "external_database_1" note that we will connect to a MySQL database, which is perfectly possible as the hard work of how to deal with each type of database is carry by ActiveRecord. Also remember to reference the adapters GENs you are using, in this example "gem 'sqlite3 '; gem 'mysql2 '; "

development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 5
  timeout: 5000

external_database_1:
  adapter: mysql2
  encoding: utf8
  database: test_db
  pool: 5
  username: root
  password: root
  host: localhost

Step 2: Create a class to handle the connection

The second step is to create a class that served to manipulate the new connections, let's first create a file called "my_databases.rb" inside of folder "config/initializers/", all we need now is to create a class that inherits ActiveRecord::Base, for example:

class ProjectDB < ActiveRecord::Base
    self.abstract_class = true
    establish_connection Rails.env.to_sym
end

class TestDB < ActiveRecord::Base
    self.abstract_class = true
    establish_connection :external_database_1
end

Much attention at this point!! Why two class?!
Basically for organization and simplicity, so that each model be explicitly tied to which connection it belongs!

The first class "ProjectdDB" refers connections standards (:development, :test, :production) in SQLite. Note the value that is passed to the method "establish_connection" which is Rails.env.to_sym.

The second class "TestDB" refers to our external database in MySQL

Obs:. "self.abstract_class = true" means that the class is abstract, it can not be instantiated only inherited.

At this point we already have all our application configured to work with different database. Now I will show by example how we have to work with it. 

Example 1: Create the users table.

rails generate scaffold user name:string email:string
      invoke  active_record
      create    db/migrate/20121127205353_create_users.rb
      create    app/models/user.rb
      invoke    test_unit
      create      test/unit/user_test.rb
      create      test/fixtures/users.yml
      invoke  resource_route
       route    resources :users
      invoke  scaffold_controller
      create    app/controllers/users_controller.rb
      invoke    erb
      create      app/views/users
      create      app/views/users/index.html.erb
      create      app/views/users/edit.html.erb
      create      app/views/users/show.html.erb
      create      app/views/users/new.html.erb
      create      app/views/users/_form.html.erb
      invoke    test_unit
      create      test/functional/users_controller_test.rb
      invoke    helper
      create      app/helpers/users_helper.rb
      invoke      test_unit
      create        test/unit/helpers/users_helper_test.rb
      invoke  assets
      invoke    coffee
      create      app/assets/javascripts/users.js.coffee
      invoke    scss
      create      app/assets/stylesheets/users.css.scss
      invoke  scss
      create    app/assets/stylesheets/scaffolds.css.scss

As the users table should be in an external database, then:

1º - Changing the file "app/models/user.rb"

class User < ActiveRecord::Base
    attr_accessible :email, :name
end

to:

class User < TestDB
    attr_accessible :email, :name
end

replace ActiveRecord::Base by its subclass TestDB thus the application knows where users table will be created.

2º - Run migration appropriately

rake db:migrate RAILS_ENV=database_externa_1
   ==  CreateUsers: migrating
   ====================================================
   -- create_table(:users)
      -> 0.0711s
   ==  CreateUsers: migrated (0.0712s)
   ===========================================

passing the connection "external_database_1" as a parameter, so the table will be created in the outer database.

Example 2: Create the design table.

rails generate scaffold project user_id:integer name:string description:text
      invoke  active_record
      create    db/migrate/20121127212538_create_projects.rb
      create    app/models/project.rb
      invoke    test_unit
      create      test/unit/project_test.rb
      create      test/fixtures/projects.yml
      invoke  resource_route
       route    resources :projects
      invoke  scaffold_controller
      create    app/controllers/projects_controller.rb
      invoke    erb
      create      app/views/projects
      create      app/views/projects/index.html.erb
      create      app/views/projects/edit.html.erb
      create      app/views/projects/show.html.erb
      create      app/views/projects/new.html.erb
      create      app/views/projects/_form.html.erb
      invoke    test_unit
      create      test/functional/projects_controller_test.rb
      invoke    helper
      create      app/helpers/projects_helper.rb
      invoke      test_unit
      create        test/unit/helpers/projects_helper_test.rb
      invoke  assets
      invoke    coffee
      create      app/assets/javascripts/projects.js.coffee
      invoke    scss
      create      app/assets/stylesheets/projects.css.scss
      invoke  scss
      identical    app/assets/stylesheets/scaffolds.css.scss

As the projects table must be in the local database:

1º - Changing the file "app/models/project.rb"

class Project < ActiveRecord::Base
    attr_accessible :description, :name, :user_id
end

to:

class Project < ProjectDB
    attr_accessible :description, :name, :user_id

    belongs_to :user
end

replace ActiveRecord::Base subclass by its ProjectDB. Note that all relations between the models can be built normally, for the application it not makes more difference the location or type of databases. Then configure the model "user" as well:

class User < TestDB
    attr_accessible :email, :name

    has_many :projects
end

2º - Run migration appropriately

rake db:migrate
   ==  CreateUsers: migrating
   ====================================================
   -- create_table(:users)
      -> 0.0014s
   ==  CreateUsers: migrated (0.0015s)
   ===========================================
   ==  CreateProjects: migrating
   =================================================
   -- create_table(:projects)
      -> 0.0014s
   ==  CreateProjects: migrated (0.0015s)
   ========================================

See it not need to pass the connection parameter, it will uses the default of each environment.
Now, all models of your project must inherit from ProjecDB and not more of ActiveRecord::Base.
Okay, that's all you need to know to work with multiple relational databases in your Ruby on Rails project.
To test, go to terminal:

rails c
Loading development environment (Rails 3.2.8)
1.9.3p194 :001 > User.create(:name => "AJ Alves", :email => "aj.alves@zerokol.com")
   (0.1ms)  BEGIN
  SQL (0.3ms)  INSERT INTO `users` (`created_at`, `email`, `name`, `updated_at`) VALUES ('2012-11-27 21:37:28', 'aj.alves@zerokol.com', 'AJ Alves', '2012-11-27 21:37:28')
   (36.6ms)  COMMIT
 => #<user 21:37:28="21:37:28" 2="2" aj.alves="aj.alves" alves="alves" created_at:="created_at:" email:="email:" id:="id:" name:="name:" updated_at:="updated_at:" zerokol.com="zerokol.com"> 
1.9.3p194 :002 > Project.create(:user_id => 1, :name => "Teste", :description => "Um projeto de teste")
   (0.0ms)  begin transaction
  SQL (0.4ms)  INSERT INTO "projects" ("created_at", "description", "name", "updated_at", "user_id") VALUES (?, ?, ?, ?, ?)  [["created_at", Tue, 27 Nov 2012 21:38:16 UTC +00:00], ["description", "Um projeto de teste"], ["name", "Teste"], ["updated_at", Tue, 27 Nov 2012 21:38:16 UTC +00:00], ["user_id", 1]]
   (117.6ms)  commit transaction
 => #<project 1="1" 21:38:16="21:38:16" 2="2" created_at:="created_at:" de="de" description:="description:" este="este" id:="id:" m="m" name:="name:" projeto="projeto" teste="teste" updated_at:="updated_at:" user_id:="user_id:"> 
1.9.3p194 :003 > u = User.first
  User Load (0.4ms)  SELECT `users`.* FROM `users` LIMIT 1
 => #<user 1="1" 21:33:42="21:33:42" aj.zerokol="aj.zerokol" alves="alves" created_at:="created_at:" email:="email:" gmail.com="gmail.com" id:="id:" name:="name:" updated_at:="updated_at:"> 
1.9.3p194 :004 > u.projects
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."user_id" = 1
 => [#<project 1="1" 21:33:58="21:33:58" created_at:="created_at:" d="d" description:="description:" dsfsdf="dsfsdf" este="este" id:="id:" name:="name:" sdf="sdf" sdfs="sdfs" updated_at:="updated_at:" user_id:="user_id:">, #<project 1="1" 21:38:16="21:38:16" 2="2" created_at:="created_at:" de="de" description:="description:" este="este" id:="id:" m="m" name:="name:" projeto="projeto" teste="teste" updated_at:="updated_at:" user_id:="user_id:">] 
1.9.3p194 :005 > p = Project.first
  Project Load (0.2ms)  SELECT "projects".* FROM "projects" LIMIT 1
 => #<project 1="1" 21:33:58="21:33:58" created_at:="created_at:" d="d" description:="description:" dsfsdf="dsfsdf" este="este" id:="id:" name:="name:" sdf="sdf" sdfs="sdfs" updated_at:="updated_at:" user_id:="user_id:"> 
1.9.3p194 :006 > p.user
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
 => #<user 1="1" 21:33:42="21:33:42" aj.zerokol="aj.zerokol" alves="alves" created_at:="created_at:" email:="email:" gmail.com="gmail.com" id:="id:" name:="name:" updated_at:="updated_at:"> 
1.9.3p194 :007 >