Managing multiple databases in Rails 6

Written by Lukas Fittl
May 23, 2019

If you’ve worked with Ruby on Rails you likely have some understanding of how your database works with Rails, traditionally that has always meant specifying a single database per environment in your config/database.yml, possibly together with an environment setting like DATABASE_URL. Based on that configuration all reads and writes will access the database.

With Rails 6 this is about to change, thanks to the work of Eileen M. Uchitelle together with contributors from GitHub, Basecamp and Shopify. In the upcoming Rails 6 (currently in RC1), you will be able to easily change which database server you are connecting to, to support a variety of scenarios such as using read replicas and splitting your database into dedicated components.

The most interesting part, which we wanted to detail in this post, is related to configuring automatic queries against a read replicas, or follower database.

First, let’s see how we can configure an additional database in Rails 6. In your config/database.yml, you can now specify multiple connections like this:

production:
  primary:
    <<: *defaults
    url: <%= ENV['DATABASE_URL'] %>
  follower:
    <<: *defaults
    url: <%= ENV['FOLLOWER_DATABASE_URL'] %>

Next, we configure our model to use this new database connection. There are two default connection types, called "reading" and "writing", for which we’d want to specify the database it should connect to:

class ApplicationRecord < ActiveRecord::Base
  connects_to database: { writing: :primary,  reading: :follower }
end

Now, this doesn’t actually change anything yet. By default all queries would still go to the writing database, even if they are read queries.

First, if we want to verify this is working manually, we can enforce the use of the read database like this:

ActiveRecord::Base.connected_to(role: :reading) do
  puts MyObject.count
end

But that would be a lot of work to modify our application code. Instead, we can tell Rails to automatically utilize the reading role for GET and HEAD requests (which are not supposed to make database changes). We can configure it like this in our environment or application config file:

config.active_record.database_selector = { delay: 2.seconds }
config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session

What this will do is automatically send all queries in a read request to the follower, but only if no write happened on the connection for clock time of 2 seconds. The delay here is intended to give time for asynchronous replication to catch up, so that you don’t read old data from a follower.

If you do have exceptions where a GET or HEAD request actually does write to the database, you can utilize the following to mark a section as writing:

def show
  # load resource and render information
  ActiveRecord::Base.connected_to(role: :writing) do
     @resource.update last_seen_at: Time.now
  end
end

Note that this solution in Rails 6 does not monitor replication lag in any way. Also be aware that queries outside of a request cycle, or read queries inside write requests would not be automatically sent to the follower. Instead you would have to add an explicit “connects_to” block around such sections. We encourage to use this new functionality with some caution.

A clear use case for this new functionality would be to send long-running offline reports to a read replica. This would allow those reports to not interrupt your production workloads and traffic, while simplifying your overall application architecture.

Lukas Fittl

Written by Lukas Fittl

Former cloud engineer at Citus Data & Microsoft. Founding team at Product Hunt, Soup.io, Spark59, & pganalyze. Speaker at Linux Open Source Summit, Microsoft Ignite, & lots of Postgres events. Loves biking & the Golden Gate Bridge.