ActiveRecord’s with_connection is now useful!

A few days ago my with_connection (originally cleanup_connection) patch finally went in to rails trunk. It changes the way with_connection works a little bit, but should be mostly backwards compatible.

Note, you’re probably only going to care about this if you use ActiveRecord in long running threads.

Before this patch, with_connection was useless for most Rails devs’ situations unless we wanted to execute raw SQL:

ActiveRecord::Base.connection_pool.with_connection do |conn|
  User.find(1)  # this will NOT use the connection passed to the block (up to Rails 2.3.x), it
                    # will create a new one, or use the one that existed before we called with_connection
  conn.execute("select raw sql here")  # this is the only way to use the connection
end

So that kinda sucked. Because the idea of with_connection is exactly what you want when you have lots of long running threads. You want to check out a connection, use ActiveRecord as you normally would (and have it use that new connection), and then check it back in whenever you’re done instead of leaving it lying around. This way you could have hundreds of threads using ActiveRecord sporadically with a small sized connection pool.

That’s what this patch allows you to do. Now with_connection will checkout a connection and set it as the main connection for the current thread (if it doesn’t already exist). If a connection already exists it will do nothing. This has the nice effect of allowing you to wrap your ActiveRecord code as closely as possible with with_connection. If with_connection always checked out a new connection no matter what, then you’d want to wrap your code at as high a level as possible which in turn would mean you would be keeping connections checked out of the pool when you didn’t really need them.

Here’s a (very contrived) example:

# shorthand
def db(&b); ActiveRecord::Base.connection_pool.with_connection(&b); end

class User
  def update_score
    db do
      self.score = calculate_score
      save!
    end
  end

  def calcuate_score
    db { self.score_cards.sum(&:points) }
  end
end

100.times { User.all.each {|u| Thread.new { u.update_score } } }

In this example we could call calculate_score directly, or indirectly through update_score, and we would never checkout more than one connection from the pool. We could also feel free to call these methods from any number of threads and know that when the calls finish their connections will be checked back into the pool without having to deal with any special ActiveRecord cleanup methods.

If this is the type of thing you are doing or want to be doing but you don’t want to actually to have wrap all your code in those annoying blocks, you can check out this monkeypatch (blog post here) which essentially wraps all DB touching ActiveRecord methods with with_connection for you.