PullMonkey Blog

Ruby on Rails – Multiple database connections

Found a need for this information while answering questions on railsforum.
So, let's say that we want to use two databases and let's even say that we want to use an Oracle database and a MySQL database. How can this be done? To start, we must decide which database will be our default database. In this scenario, I chose MySQL. Let's see what that looks like:

1
2
3
4
5
6
7

# in your database.yml file
development:
  adapter: mysql
  username: root
  password: 
  database: example_development

So we have all seen that before. Now all of our active record models will use this mysql connection.
But, I need to use data from an oracle database, so let's setup that connection:

1
2
3
4
5
6
7
8
9
10
11
12
13

# in your database.yml file
development:
  adapter: mysql
  username: root
  password: 
  database: example_development

oracle_development:
  adapter: oracle
  username: root
  password: 
  database: example_oracle_development

Neat, we have two development connections. Now we have to tell which models to use which connection. Well, actually, we just need to tell the oracle models to connect to oracle_development, all the other models will default to development. I have a model named user, and it's records are kept in an oracle database. Our user model looks like this initially:

1
2
3
4

#RAILSROOT/app/models/user.rb
class User < ActiveRecord::Base 
end

The line we need to add is this:

1
2

establish_connection :oracle_development

Even better, we can make this dynamic, so when we are in the test or production environment, we don't need to change the establish_connection line.

1
2
3

# use RAILS_ENV where RAILS_ENV is generally development, test or production
establish_connection "oracle_#{RAILS_ENV}"

So the final product could look something like this:

database.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

dev_basics: &dev_basics
  username: root
  password: 

<% %w(development test production).each do |env| %>
<%= env %>:
  <<: *dev_basics
  adapter: mysql
  database: example_<%= env %>

oracle_<%= env %>:
  <<: *dev_basics
  adapter: oracle
  database: example_oracle_<%= env %>
<% end %>

Oracle model example

1
2
3
4

class User < ActiveRecord::Base 
  establish_connection "oracle_#{RAILS_ENV}"
end

That should be it.