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 problem

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

A solution for active record 6.0

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

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]

A solution for active record 6.1

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

undefined method current_preventing_writes for String

Final thoughts

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.

Also published at https://dev.to/jolyon/horizontal-sharding-in-a-gem-a-dive-in-to-active-record-connection-pools-1ci4

Disclaimer: The author provides this code and software “AS IS”, without
warranty of any kind, express or implied, including but not limited to fitness for a particular purpose and non-infringement. In no event shall the
author be liable for any claim, damages or other liability in connection with the software or code provided here