MySQL thread-safe multi-database switching in Rails 5

README.md

The code here is a tech demo showing how to switch between databases in Rails 5, in a thread-safe manner.

BACKGROUND: HOW NOT TO CHANGE DATABASES

The simple way to switch databases is:

ActiveRecord::Base.establish_connection :user_shard1
puts User.find(1) # this is executed on the database keyed on "user_shard1" in database.yml
ActiveRecord::Base.establish_connection :development

This should work in a single-thread environment, so it's safe to use in a single-threaded web server like Unicorn or WEBrick. However, in a multi-thread environment like Puma or Sidekiq, it will cause major bugs because ActiveRecord's state - ie which database it's using - is global for the whole process. Threads will constantly be switching this global state and then reading/writing to/from the wrong database.

THE RIGHT WAY TO SWITCH DATABASES

The solution, as in the code example here, is to DIY pool management. Any general ActiveRecord statements will use the default database as normal (ie "development" or "production"). The pools should be established on initialisation and any code that needs to use another database should use pool.with_connection, which is a Rails method on the database pool class that will not affect global state. This is also nice from a clean-code perspective, because we don't need to reset to using the default database afterwards. It's just a block that temporarily uses a different database.

REFERENCES

• http://www.wordofmike.net/j/shard-query-rails-querying-multiple-databases
• https://stackoverflow.com/questions/43691840/switching-between-multiple-databases-in-rails-without-breaking-transactions

multidb.rb

begin

  # Get the hash (i.e. parsed) representation of database.yml
  databases = Rails.configuration.database_configuration
  
  # Get a fancier AR-specific version of this hash, which is actually a wrapper of the hash
  resolver = ActiveRecord::ConnectionAdapters::ConnectionSpecification::Resolver.new(databases)
  
  # Get one specific database from our list of databases in database.yml. pick any database identifier (:development, :user_shard1, etc)
  spec = resolver.spec(:user_shard1)
  
  # Make a new pool for the database we picked
  pool = ActiveRecord::ConnectionAdapters::ConnectionPool.new(spec)
  
  # Use the pool
  # This is thread-safe, ie unlike ActiveRecord's establish_connection, it won't leak to other threads
  pool.with_connection { |conn|
    
    # Now we can perform direct SQL commands
    result = conn.execute('select count(*) from users') # result will be an array of rows
    puts result.first
    
    # We can make AR queries using to_sql
    # See http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html
    sql = User.where('created_at > ?', 1.day.ago).limit(5).to_sql # generate SQL string
    raw_users = conn.select_all sql # get list of hashes, one hash per matching result

  }
  
rescue => ex
  puts ex, ex.backtrace
ensure
  pool.disconnect!
end

Thanks Mike. So doing just User.where.. wouldn't work?

Thank you. This is really useful and I am using something similar. But I get a new thread hanging (related to the connection pool) once all the code is ran. Do you know the best way to kill that thread after the pool is disconnected?