Advanced multi-db techniques in Rails 6

Rails 6 is just around the corner. The release candidate two was recently released. This post is part of the Road to Rails 6 series which will prepare you for Rails 6.

Check the part one of this post where we saw how to setup an app with multiple databases in Rails 6 and deploy it to Heroku.

In this post, we will not discuss about multiple databases but we will discuss about read and write instances of same database and how to manage them within a Rails application.

I am using the terms read and write instances for read replica and primary/master instance of the database in this article. It means that read operations should be handled by read replica instance and write operations should be handled by the write/primary/master instance and should be synced to the replica instance for subsequent reads.

Read Replicas

We earlier saw that we can connect to the animals database for both read and write operations.

class AnimalBase < ApplicationRecord
  connects_to database: { writing: :animals }
end

We can start using a read replica for read operations from the animals database by passing a reading role to the connects_to call above.

class AnimalBase < ApplicationRecord
  connects_to database: { writing: :animals, reading: :animals_replica }
end

This role is different than the database roles that are part of major SQL databases. This role specifies which kind of operation you want to perform on the database, read or write and it is specific to how Rails implements the read write operations.

We need to specify the animals_replica database configuration in the database.yml to make this work.

production:
  primary:
    adapter: postgresql
  animals:
    migrations_paths: db/animal_migrate
    adapter: postgresql
    url: <%= ENV["HEROKU_POSTGRESQL_OLIVE_URL"] %>
  animals_replica:
    adapter: postgresql
    url: <%= ENV["HEROKU_POSTGRESQL_PURPLE_URL"] %>
    replica: true

Make sure that you are not using same database instance for both reading and writing roles. Rails recommends that replica database instance is separate and has a readonly user created. Rails assumes that this is done by you manually and you are just providing the connection information for Rails to use.

We have not added the migrations_paths configuration for the replica database but we have added replica: true for it. The replica: true configuration is important and conveys to Rails that this database configuration should be treated as replica database.

Now that we have specified which database should be used for write operations and which one to use for read operations, Rails will take care of switching between them based on the request type.

Automatic connection switching between read replica and write databases

If our application is getting any request which is modifying the database such as POST, PUT, DELETE, PATCH then that request is automatically sent to the write database by Rails. If the application is getting any request which is only reading something from the database then it will go to replica. But if we are getting a read request immediately within 2 seconds after a write request then it will go to the primary database. If your application does not warrant showing real time data to the users, then you can get away with live data not getting updated in the read replica immediately but Rails provides a way to make sure that at-least for first two seconds after the write operation, read requests are handled by the write instance. This assumes that your read replica will get updated within 2 seconds with the live data. This setting can be controlled by following configuration.

config.active_record.database_selector = 2.seconds

We can configure it in the environments/production.rb as per replica lag. The timestamp of last write operation is stored in the application session by Rails and is controlled by config.active_record.database_resolver_context.

This automatic connection is included in the Rails app as a rack middleware so it is only available for HTTP requests to the application. For background jobs or rake tasks, by default Rails sends all queries to the write instance. Rails also allows to manually switch between read and write databases which we will see next.

Manual switching between write and read databases

Rails provides ActiveRecord::Base.connected_to method to switch between read and write databases.

Post.connected_to(role: :reading) do
  post_ids = Post.all.pluck(:id)
  ProcessBulkPostRepliesWorker.perform(post_ids: post_ids)
end

This makes sure that the block uses read replica. It looks up all the connections connected to the reading role and then performs the queries in the block using that connection. If the connection does not exist, it will make one before executing the block.

We can also pass database to the connected_to method.

ActiveRecord::Base.connected_to(database: animals) do
  CollectLionsInformationWorker.perform(Lion.all.pluck(:id, :continent, :age))
end

We can also pass the role under the database key.

ActiveRecord::Base.connected_to(database: { reading: :animals }) do
  CollectLionsInformationWorker.perform(Lion.all.pluck(:id, :continent, :age))
end

This will use the connection to the read replica of the animals  database and use it.

But we can't pass role and database both to the connected_to method. We can pass only one of them. This means that, when we want to switch roles from reading to writing to reading then we are expected to call connected_to on correct class. We can use any of the model like Post which is connected to the database for which we want to switch the role. Or we can also use ActiveRecord::Base and pass the reading role if we want to connect to the read replica of the primary  database.

When we are passing database key to the connected_to method, we are switching the database altogether for eg. from primary to animals for the duration of the block. If we don't pass the role for the new database, connection is  automatically  made with the writing role.

In this way we can manage connecting to different databases in ad-hoc scripts, background jobs or even for specific requests which can't rely on automatic connection switching provided by Rails.

What is not provided by the multi-db feature in Rails 6?

Features such as sharding, load balancing replicas, joining across multiple databases are not supported out of the box in Rails 6. Some of these features may be added in future.

Want to stay on the Road to Rails 6?

Subscribe to my newsletter and get a weekly email to know more about how to make your app ready for Rails 6.