Multiple database connections with Rails

Recently a client requsted that I add a new admnistration panel to an existing Rails application. I thought to myself, “That sounds simple enough?”; welll, things are never as simple. In addition to this control panel, they wanted to use an existing MySQL database and its contents which, of course, did not follow the Rails naming conventions. Anyhow, after some research here is what I came up with.

Our first stop is in the database.yml file; the file should look something like this:

# database.yml 
development:
  adapter: mysql
  encoding: utf8
  username: root
  password: 
  database: example_development

This shouldn’t be a suprise to you. The database specified above is the default database that all of our Active Record models will use; if we want to add another connection we will need to start by speficing the connection. This connection can be another MySQL, PostgreSQL, or even an Oracle connection.

# database.yml 
development:
  adapter: mysql
  encoding: utf8
  username: root
  password: 
  database: rails_development
 
# here we specify new connection
pixel_development:
  adapter: mysql
  encoding: utf8
  username: legacy_username
  password: legacy_password
  database: legacy_database

Now that we have the connections specified, we need to move to our model; it is in here where we redirect the request to a non-standard connection. I have created a new model called “Pixel” which will interface with a database used for tracking pixels.

# app/models/pixel.rb
class Pixel < ActiveRecord::Base
end

Unless we specificy another database connection, Rails will use the default enviroments database; in this case “development”. Let’s change this by adding the following line:

# this will use our "pixel_development" database
class Pixel < ActiveRecord::Base
  establish_connection :pixel_development
end
 
 
# but let's make this more dynamic so we can use 
# develpoment, production, and test databases
class Pixel < ActiveRecord::Base
  establish_connection :pixel_#{RAILS_ENV}
end

Now we should be good to go! Any requests to the Pixel model will connect to the second database. Say the table name is set in stone and cannot be modified? No problem.

# this will use our "pixel_development" database
class Pixel < ActiveRecord::Base
  establish_connection :pixel_#{RAILS_ENV}
  set_table_name "your_table_name_here"
end