PullMonkey Blog

21 Apr

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.





5 Responses to “Ruby on Rails - Multiple database connections”

  1. By sukeerthiadiga on Apr 21, 2008 | Reply

    seems to be very simple !!!
    will try and get to u back if any problem..
    Thanks any ways
    made my work simpler

  2. By Nuttapong on Apr 21, 2008 | Reply

    Thank you. That ’s I’m looking for.

    If I want to connect per action can I use with these code >>

    class Bidding < ActiveRecord::Base
    establish_connection "robot_database", :on => [:create, :save, :destroy]
    end

    I’ ll test and come back with result.

  3. By charlie on Apr 21, 2008 | Reply

    @Nuttapong - nice try :) the actions are not known to the model. I am sure there is a way, although, I can’t see what the use would be … any ideas?

  4. By Cakey on Apr 21, 2008 | Reply

    @Nutta
    how about
    putting establish_connection in callback blocks?

    haven’t tried it though

  1. 1 Trackback(s)

  2. Random Knowledge » Blog Archive » Ruby On Rails: Multiple Database Connections

Sorry, comments for this entry are closed at this time.