Categories
activerecord database mysql ruby-on-rails transactions

Switching between multiple databases in Rails without breaking transactions

I am setting up a Rails app with multiple databases. It uses ActiveRecord::Base.establish_connection db_config to switch between the databases (all of which are configured in database.yml).

establish_connection apparently breaks pending transactions on each call. One negative consequence is testing, where use_transactional_tests must be disabled (leading to undesirably slow tests).

So … how can a Rails app maintain multiple transactions on different databases at the same time? (To clarify, I’m not looking for a fancy cross-database transaction. Just a way for the database client, ie the Rails app, to maintain multiple transactions simultaneously, one per database.)

The only solution I’ve seen is putting establish_connection directly in the class definition, but that assumes you have a database dedicated to specific classes. I am applying a user-based sharding strategy, where a single record type is distributed across multiple databases, so the database needs to be switched dynamically in code.