How to Manage Multiple Databases in Rails 6

With the launch of Rails 6, one of the new features that were announced was of multiple database support.

This breather came to me when, in one of our applications, I was struggling to manage separate DBs efficiently. There are several reasons why one would want such support, such as:

Using SQL Database for storing user data and NoSQL for location data

Having multiple SQL Databases to manage separate apps but an app might need direct access to the other DB

Managing multiple NoSQL and Redis Databases to store different kinds of data, etc

When faced with using multiple Databases, usually, one serves as master and other servers as a slave. Because I was managing this before the release of rails 6, I was using a not-so-optimal (read patched/hacky) way to get this done. I created a separate YAML file for the second database to store the connection information. Here is a sample file:

second_database.yaml
  adapter: db_adapter
  encoding: db_encoding
  pool: db_pool
  username: db_username
  password: db_password
  database: db_name
  host: db_host

To access multiple databases, every time I would:

Establish the connection by reading the connection information from the YAML file

config = YAML.load_file(second_database.yaml')
connection = ActiveRecord::Base.establish_connection(
  adapter:  config['adapter'],
  host:     config['host'],
  database: config['database'],
  username: config['username'],
  password: config['password']
)

Query the data

employees = connection.execute("SELECT * from employees")

Close the connection

connection.close

Such a headache!

But the release of the new Rails 6, you probably won’t have to go through the pain (and squirm at your own code) to manage multiple Databases.

As soon as Rails 6 was launched, I upgraded my application to leverage the benefits of multiple DB support. Let’s go step by step to understand the upgrade and set up for multiple databases.

Firstly, check and update ruby version to 2.5 as rails 6 must require ruby 2.5 or newer.

Upgrade your ruby version to 2.5 or newer:

During the ruby update, it might be possible to get syntax errors or deprecation warnings just because of a version upgrade.
Please update ruby versions incrementally - do not directly jump to the latest ruby version; this will cause you lots of issues.
Update to next incremented version; resolve the errors and warnings; run and resolve the test cases and repeat the process.

Upgrade rails to the latest version in 5.2 series:

As this article only covers upgrade guidelines from rails 5.2 to rails 6; please make sure you’re on the latest version in the 5.x series.

Update gemfile for rails 6 version:

Now it’s time to update the rails version in your Gemfile; change the rails gem version in your gem file.
Run bundle update rails command in terminal to update rails and other dependent gems.

Run rails app:update in terminal:

By running this command, you will find some new config settings are added to your application. I suggest you use the diff tool option (d) for each file change.

Uncomment defaults values in new_framework_defaults_6_0.rb:

Run migration:

Now run bundle install to install remaining gems and run the migration and resolve the issue, if any.

Run and fix TestCases:

After this massive upgrade, let’s test your application. Run your TestCases; solve if any failed and solve deprecation warning.

Start the localhost and perform manual testing:

I would suggest you do one round of manual testing for your app. Hit the rails and start testing your app manually.

Bingo, now you run on Rails 6!

Now let’s move further for multiple DB setup with rails 6.

With rails 6 multiple DB support, you can have more than one database with a replica (read-only copy) of each database.

Now, consider your rails application with a single primary database, and now after upgrading to Rails 6, you need to add a new database for some of the new tables. The current version of your database.yml looks like:

With rails 6; you can add a replica for your primary database and also can add a new database by updating your database.yml like this:

Consider a few points which you need to take care when using replica for your database:

For the primary and replica database, the database name should be the same because both the database contains the same data. While using the replica database, you need to add a
```
replica: true
```
to the database settings.
Username for both replica and primary database should be different; the primary user would have both reads and write permissions while replica user would have only read permission; not write.
While adding a new database, you need to take care of the migrations path as well. To implement it, you need to add
```
migrations_path
```
setting in the database.yml file as shown above. Though we have a replica database, we haven’t set a migration path for the same.

After this step, you will have a new database added; now, let’s set up a model for it. To use a new database, you need to add one abstract model class to your app; as shown below:

Now you can access the new database; now let’s briefly discuss multiple database features:

1. The automatic connection switching between primary and replica database:

To use a read-only database to the application, you need to configure middleware for automatic connection switching.

Automatic connection switching allows your application to switch between primary and replica databases based on HTTP request methods.
For example, if your application receives POST, PUT, DELETE, or PATCH request, the app automatically writes to the primary database. And, for the requests such as GET or HEAD, the application reads from the replica one.

To configure middleware for automatic connection switching; uncomment or add the following line to application config.

Rails only send GET or HEAD request to primary only if the read request is within what we configured above; by default, it will be set to 2 seconds. You have the liberty to change it as per your database infrastructure.

2. The manual connection switching between primary and replica database:

To connect to the replica or primary database manually; rails provide

ActiveRecord::Base.connected_to

method.

Sometimes, your application needs to connect to primary or replica irrespective of the request type. In such cases, you can utilize the

connected_to

method provided by ActiveRecord.

By using the above code, you can force your application to connect to the blogs’ database irrespective of the request type.

Now, this command uses the connection to read replicas of the blogs’ database and use it.

What are the features not supported with multiple databases by Rails 6?

Here are they,

Sharding
Load balancing of replicas
Joining across the multiple databases

We can expect these features to come up soon and help us configure, use, and manage multiple databases with Rails 6 super effectively.