Establishing a Connection to a Non-Default Database in Rails 3.2

If you’ve ever built a Rails app in which some models don’t connect to the default database, you know that establish_connection is the method you would call make the connection.

But before I get to that: the space shuttle Endeavor flew directly over my street today, low and slow, with a fighter jet escort. My neighbors and I had an amazing view of it. It was piggybacked on a 747 transport for a publicity tour across California before being retired at a Southern California museum. It was exciting!

Shuttle Flyover

So. Let’s say you needed to talk to an external NASA database. You would create new keys in database.yml called nasa_development, nasa_test, and nasa_production:

# database.yml
development:
  # development configuration goes here

nasa_development:
  # development configuration to external database goes here

# same for test and production...

Then define your models like this:

class Astronaut < ActiveRecord::Base
  establish_connection "nasa_#{Rails.env}"
end

In this example, Rails looks for an ‘astronauts‘ table in the external database. (Automatic table naming still works. You can override that too, by calling self.table_name=).

In the past, when I’ve had multiple tables on a single connection, I always included a module that makes the connection. Let’s define models for Astronaut and Shuttle:

module NasaConnection
  extend ActiveSupport::Concern
  included do
    establish_connection "nasa_#{Rails.env}"
  end
end

class Astronaut < ActiveRecord::Base
  include NasaConnection
end

class Shuttle < ActiveRecord::Base
  include NasaConnection
end

That might have been overkill. I could just as well have done this:

class Astronaut < ActiveRecord::Base
  establish_connection "nasa_#{Rails.env}"
end

class Shuttle < ActiveRecord::Base
  establish_connection "nasa_#{Rails.env}"
end

In both of these techniques, establish_connection is called twice. This works in Rails 3.1.8 and earlier, but starting in 3.2.0 you will get runtime errors. An example:

class Astronaut < ActiveRecord::Base
  establish_connection "nasa_#{Rails.env}"
  has_many :missions
  has_many :shuttles, through: :missions
end

class Shuttle < ActiveRecord::Base
  establish_connection "nasa_#{Rails.env}"
  has_many :missions
  has_many :astronauts, through: :missions
end

class Mission < ActiveRecord::Base
  establish_connection "nasa_#{Rails.env}"
  belongs_to :astronaut
  belongs_to :shuttle
end

# test
shuttle = Shuttle.create name: "Endeavor"
shuttle.astronauts << Astronaut.create(name: "Mark Kelly")
assert_equal 1, shuttle.reload.astronauts.count # FAIL

Different database adapters will give you different errors. (I tried SQLite and Postgresql.) The problem is in ActiveRecord, not in the adapters.

Trying to Find a Fix

The fact that this doesn’t work any more seems to me like a bug in ActiveRecord. So I used a debugger to step through the establish_connection code in Rails 3.1.8 and 3.2.8, trying to identify the problem. I found a comment above the definition of the ConnectionHandler class that gave me a hint:

# suppose that you have 5 models, with the following hierarchy:
#
# |
# +-- Book
# | |
# | +-- ScaryBook
# | +-- GoodBook
# +-- Author
# +-- BankAccount
#
# Suppose that Book is to connect to a separate database (i.e.
# one other than the default database). Then Book, ScaryBook
# and GoodBook will all use the same connection pool. Likewise,
# Author and BankAccount will use the same connection pool.
# However, the connection pool used by Author/BankAccount
# is not the same as the one used by Book/ScaryBook/GoodBook.

Ah! So maybe ActiveRecord requires you to create a common base class for all models that will talk to the same external database. So I tried it. Notice that Astronaut, Shuttle, and Mission are all subclasses of NasaTable, which is a subclass of ActiveRecord::Base:

class NasaTable < ActiveRecord::Base
  establish_connection "nasa_#{Rails.env}"
end

class Astronaut < NasaTable
  has_many :missions
  has_many :shuttles, through: :missions
end

class Shuttle < NasaTable
  has_many :missions
  has_many :astronauts, through: :missions
end

class Mission < NasaTable
  belongs_to :astronaut
  belongs_to :shuttle
end

# test
shuttle = Shuttle.create name: "Endeavor"
shuttle.astronauts << Astronaut.create(name: "Mark Kelly")
assert_equal 1, shuttle.reload.astronauts.count

This code, like my earlier code, worked great in Rails 3.1. But it still failed in Rails 3.2, only now for a different reason: ActiveRecord tried to connect to a table called ‘nasa_tables‘. I thought it must be associating all three subclasses with that table.

Hmm, easy but annoying to fix, right? Force Rails to use the right table names:

class Astronaut < NasaTable
  self.table_name = 'astronauts'
  ...
end

class Shuttle < NasaTable
  self.table_name = 'shuttles'
  ...
end

class Mission < NasaTable
  self.table_name = 'missions'
  ...
end

Nope, that still didn’t work. Rails was still looking for the nonexistent ‘nasa_tables‘ table. Looks like self.table_name= is not going to solve this particular problem.

MoonEventually I found a discussion of a Rails issue where someone was having this problem. In their example, A was the base class and B was the subclass. @tenderlove said, “Out of curiosity, why would you do this? If there is no A table, you should set the class to be abstract.”

Abstract? I know how to define abstract classes in other languages like C++ but I’ve never heard of an abstract class in Ruby. Well, it turns out he’s talking about an ActiveRecord concept, not a Ruby concept. Any subclass of ActiveRecord can declare itself abstract by settingĀ self.abstract_class = true. This tells ActiveRecord that it shouldn’t look for a table to go with that class.

I Like Stuff that Finally Works

Armed with this information I finally landed on a solution that works in Rails 3.2. As you can see, 3.2 is much less flexible than 3.1 about how you define your class structure to talk to external databases. Whereas 3.1 had several techniques that would work, as far as I know the following is the only way to make it work in 3.2:

  1. Make a common base class for all models that need to talk to a non-default database.
  2. Tell ActiveRecord that this base class is abstract.
  3. Establish the connection in the base class.

So here’s our final code:

class NasaTable < ActiveRecord::Base
  self.abstract_class = true
  establish_connection "nasa_#{Rails.env}"
end

class Astronaut < NasaTable
  has_many :missions
  has_many :shuttles, through: :missions
end

class Shuttle < NasaTable
  has_many :missions
  has_many :astronauts, through: :missions
end

class Mission < NasaTable
  belongs_to :astronaut
  belongs_to :shuttle
end

# test
shuttle = Shuttle.create name: "Endeavor"
shuttle.astronauts << Astronaut.create(name: "Mark Kelly")
assert_equal 1, shuttle.reload.astronauts.count # IT WORKS

A Side Note About Connection Pools

It would be nice to have the flexibility that 3.1 had. But as long as there is a reasonable solution, I’m happy. I should mention, however, that if you call establish_connection multiple times with the same connection key (e.g. the old way where each table calls establish_connection), you end up creating multiple connection pools to the same database. This seems like a problem, as Sam Saffron has pointed out. If a single app talks to a single database through multiple connections, the best way to manage those connections is via a single pool–not multiple pools that don’t know about each other.

If you stick with the method I described above, where only one base class establishes a connection to each database, you can avoid this problem.

Astronaut

8 thoughts on “Establishing a Connection to a Non-Default Database in Rails 3.2”

  1. Nice article, Brian!

    Lucky for me, I have always used the “establish_connection in an abstract base class” for this situation–glad to hear it still works in Rails 3.2!

    P.S. Endeavor was piggybacked on a 747, not a 767. Two of them exist, and have been used since the first test shuttle, Enterprise, took to the skies. It was a beautiful sight to see, wasn’t it? It sounds like you got a much better view than I did!

  2. So, if I have 4 unicorn works and a pool size of 1. Do I see 1 or 4 connections? For some reason with the default connection, I see 4. When I do it the way you suggest with non-default databases, I see 1 connection. I was expecting 4. Any ideas why?

  3. Thanks a lot for sharing – this helped me out a ton… I was getting the “too many clients” Postgres error and I had no idea about the Rails 3.2 establish_connection bug.

Leave a Reply

Your email address will not be published. Required fields are marked *

Feel free to use <a>, <b>, <i>, <strong>, <em>, <strike>, <code>.

Code blocks:
[code language="ruby/javascript/html/css/sass/bash"]
[/code]