Implementing read and write databases in Rails

Database performance is critical to ensuring a smooth user experience in web applications. By distributing database loads, applications can significantly improve their overall performance. This article will guide you through the process of implementing read and write databases in Rails to speed up your application.

From Rails 6.0 introduced support for multiple databases, paving an expanded path for horizontal database scalability. This means that you can designate one database for writes (inserts, updates, deletes) and one or more others for reads (selects). This separation can help to balance your database load and improve overall application performance.

Setting up the database

First, let's update the database.yml file to distinguish between the primary (write) and replica (read) databases:

# config/database.yml

production:
  primary:
    <<: *default
    url: <%= ENV['DATABASE_URL'] %>
  primary_replica:
    <<: *default
    url: <%= ENV['DATABASE_REPLICA_URL'] %>
    database_tasks: false # disable tasks related to db, like migrations
    replica: true

In the YAML file above, we have used <%= ENV['DATABASE_URL'] %> for the primary database and <%= ENV['DATABASE_REPLICA_URL'] %> for the replica database.

Primary replica DB server

You will then need to set up a primary replica database server. Note that migrating to our replica db and synchronising data with the primary db is managed by your platform, so we set database_tasks: false.

Automatic connection switching

Rails provides powerful tools for managing connections to multiple databases. Here is how to adjust your application configuration to enable automatic connection switching.

# config/application.rb

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

Updating the ApplicationRecord

Then we will need to initialize rather than update the ApplicationRecord files separately in each model:

# config/initializers/database_connector.rb

ActiveRecord::Base.connects_to database: { writing: :primary, reading: :primary_replica }

# or in the main ApplicationRecord.rb file
class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  connects_to database: { writing: :primary, reading: :primary_replica }
end

Challenges & Adaptations

After implementation, sometimes you will see ActiveRecord::ReadOnlyError Write query attempted while in read-only mode errors. These were caused by instances using the GET method but attempting to update records. The solution is simple by wrapping code into that block.

ActiveRecord::Base.connected_to(role: :writing) do
  # ruby code that will use the writing role
end

Summary

In this article, we explored Rails implementation techniques, the ability to use multiple databases, and how to improve application performance by separating read and write roles. With this simple configuration, developers can further extend the capabilities of their Rails application and improve the experience for their users.

Happy Coding!