Connecting to Multiple Databases in a Rails App

Note (Updated 2025): This post was originally written in 2011 for Rails 2/3. A Rails 7+ example is included at the end.

Traditionally, a Rails application connects to a single database. Models inherit from ActiveRecord::Base, which uses connection details from config/database.yml through the establish_connection method.

In one of my projects, I had an old pinlocations table with geolocation data (pincode, lat/lng, etc.). Rather than duplicating the work to populate this data in a new schema, I wanted my new Rails app to connect directly to the old database, read from that table, and populate new tables.

A Simple Solution Without Gems

While there are plugins like connection_ninja, secondbase, or octopus, in my case a gem was overkill. All I needed was access to one legacy table.

I created a temporary model pointing to the old database:

class Pinlocation < ActiveRecord::Base
  establish_connection(
    adapter:  "postgresql",
    host:     "localhost",
    username: "*****",
    password: "*****",
    database: "old_database"
  )
end

From Rails console, you can confirm the connection:

$ rails console
>> location = Pinlocation.first

This fetches a record from the old_database.

Migrating Data

Once the connection worked, I wrote a script to read data from the old DB and insert it into the new one:

out_file = "db/data/scripts/output.txt"
open(out_file, 'w') do |f|
  f.puts "Total no. of records to be imported: #{Pinlocation.count}"

  Pinlocation.all.each do |location_old|
    begin
      location_new = PinLocation.new(
        pincode: location_old.pincode,
        name:    location_old.name,
        lat:     location_old.lat,
        lng:     location_old.lng
      )
      # Map district, state, etc.
      location_new.save
    rescue ActiveRecord::RecordInvalid => invalid
      puts invalid.record.errors
      f.puts invalid.record.errors
    end
  end
end

Modern Alternatives (2025)

Today, Rails provides first-class multi-database support (introduced in Rails 6). You can define multiple connections directly in config/database.yml and map models to databases without hacks:

production:
  primary:
    adapter: postgresql
    database: main_db
  analytics:
    adapter: postgresql
    database: analytics_db

Then, in your models:

class AnalyticsRecord < ApplicationRecord
  connects_to database: { writing: :analytics }
end

This is cleaner, fully supported, and works with Rails features like migrations, replication, and role-based connections.

For one-off migrations, Rails’ built-in tools or ETL libraries (like ActiveRecord Import) are preferable over writing custom scripts.

Wrap-up

• Back in 2011, establish_connection was the simplest way to connect to multiple databases.
• Today, prefer Rails’ built-in multi-database support for maintainability and clarity.
• If you only need to pull in one table temporarily, the legacy approach still works - but Rails 6+ makes this a solved problem.

You might also like

• Loading CSV Data in Rails
• Retrieve a Random Row with Active Record