When connecting to a second database, take care not to overwrite existing connections

Sometimes, you may want to open up a second database connection, to a read slave or another database. When doing that, you must make sure you don't overwrite an existing connection.

The problem

While this may look good, it will actually cause all kinds of trouble:

Copydef with_other_database
  ActiveRecord::Base.establish_connection(slave_settings)
  yield
ensure
  ActiveRecord::Base.establish_connection(master_settings)
end

Putting aside that you are setting the general connection here (not generally a bad thing; even when setting only the connection of one class, the following applies), the above code connects to the second database and in any case will give you your old connection back (so far, so good). But in doing so, it will disconnect the existing connection.

Now consider this:

CopyFoo.transaction do
  Foo.create!
  
  with_other_database do
    raise 'Oops' if Bar.count > 9000
  end
end

On the database level, you will see these queries (simplified; the number at the beginning of each line is the connection ID):

Copy1 Connect
1 BEGIN
1 INSERT INTO foos
1 Quit
2 Connect
2 SELECT COUNT(*) FROM bars
2 Quit

That's it. Unfortunately, we are missing the COMMIT from the transaction -- so the Foo record we just created will actually never be written to the database, because we disconnected during an unfinished transaction. MySQL will for good reason not simply commit any open transactions when you disconnect (you might be doing stuff afterwards that could require to abort the transaction).

A better approach

As a general rule, never use ActiveRecord::Base.establish_connection and also don't use establish_connection on your other ActiveRecord classes. Instead, use a class to "proxy" your connection, like this:

Copyclass ReadDatabaseConnection < ActiveRecord::Base
  def self.abstract_class?
    true # So it gets its own connection
  end
end

ReadDatabaseConnection.establish_connection(slave_settings)

That will not touch your existing connection but open up a second one.

If you want to accomplish something similar to the with_other_database method from above, you can then use this connection in your block and switch to it and back to your regular connection.

There is also a gem that seems to be able to do all the magic for you: Octopus Show archive.org snapshot . I have not tried it, though.