A Dive into Active Record Connection Pools and Horizontal Sharding in a Gem

Rails is a great framework that enables us to avoid grungy but essential topics like database connection pooling with all the inherent complexities of multi-threading and error handling.

Inevitably though we occasionally find ourselves immersed in areas where Rails or other gems aren't there to do the heavy lifting.

This is one such tale and comes with a necessary health warning. Our attempt at solving our problem is just that. Is it a flawed solution, could there be better solutions? Quite possibly.

Our Rails apps use a homemade gem that provides access to a series of MySQL databases with one database per customer.

This horizontal sharding was handled by octoshark but a new version of the gem hadn't been released since 2016 and we were seeing

MySQL client is not connected

errors so it was time to find an alternative.

Other gems provide support for horizontal sharding but in the context of a Rails app as opposed to a gem and although horizontal sharding is now supported natively in Rails 6.1 thanks to @eileencodes, it wasn't obvious how to use it within a gem.

We fixed our gem's active record dependency to 6.0 and removed octoshark. Then we set about creating the relevant connection pools on class load and enabled switching between them by overriding the active record

connection

method in an abstract base class.

Note the solution we arrived at below has only been tested with puma.

In order for each http request to hit the correct database, we set the value for

RequestStore.store[:mygem_database_key]

(see RequestStore) prior to calling models in the gem.

With Rails 7.0 around the corner, we didn't want to stop at 6.0 so we upgraded our gem to active record 6.1, and not unexpectedly, the overriding of internal active record methods no longer worked.

After trial and error we arrived at the following solution:

In addition, we needed to monkey patch

ActiveRecord::ConnectionAdapters::AbstractAdapter

due to the error

undefined method current_preventing_writes for String

.

Overriding and monkey patching active record methods and classes are not always a great idea. They will likely constitute a barrier to future upgrades and can be a source of hard-to-find bugs but accessing the undercover power and capabilities of Rails can make a lot of sense when the alternative is a whole lot more challenging.