Rails 6: Multiple DB Support

With the launch of Rails 6, one of the new features that was announced was for multiple DB support. This breather came to me when, in one of our applications, I was struggling to efficiently manage separate DBs. There are a number of reasons why one would want such support, such as:

• Using the 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 a master and the 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

xxxxxxxxxx

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

xxxxxxxxxx

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

• close the connection

xxxxxxxxxx

connection.close

You might also like: Rails Bundle Install and Gemfile

Such a headache!

But with 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 DBs.

1. First, check and update Ruby to version to 2.5, as Rails 6 requires Ruby 2.5 or newer.

2. Upgrade your Ruby version to 2.5 or newer:

   1. During the Ruby update, it might be possible to get syntax errors or deprecation warnings just because of a version upgrade

   2. Please update Ruby versions incrementally — do not directly jump to the latest version; this will cause you lots of issues

   3. Update to the next incremented version; resolve the errors and warnings; run and resolve the test cases and repeat the process

3. Upgrade Rails to the latest version in 5.2 series:
   1.  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.

4. Update Gemfile for Rails 6 version:

   1. Now it’s time to update the Rails version in your Gemfile; change the Rails Gem version in your Gemfile.

   2. Run the bundle update Rails command in the terminal to update Rails and other dependent Gems.

5. Run the Rails app:update in terminal:

   1. 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.

6. Uncomment defaults values in new_framework_defaults_6_0.rb:

7. Run migration:

   1. Now run the bundle install to install the remaining Gems and run the migration and resolve the issue if any

8. Run and fix TestCases:

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

9. Start the localhost and perform manual testing:

   1. I would suggest you do one round of manual testing for your app. Hit the rails s 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:

xxxxxxxxxx

production:

  database: my_primary_database

  user: root

  adapter: mysql

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:

xxxxxxxxxx

production:

  primary:

    database: my_primary_database

    user: root

    adapter: mysql

  primary_replica:

    database: my_primary_database

    user: root_readonly

    adapter: mysql

    replica: true

  blogs:

    database: my_blogs_database

    user: blogs_root

    adapter: mysql

    migrations_paths: db/blogs_migrate

  blogs_replica:

    database: my_blogs_database

    user: blogs_readonly

    adapter: mysql

    replica: true

Consider a few points that you need to heed when using replica for your database:

• For primary and replica databases, the database name should be the same because both the databases contain the same data. While using the replica database, you need to add  replica: true  to the database settings.

• The username for both the replica and the primary databases should be different; the primary user would have both read and write permissions while the replica user would have only read permission.

• While adding a new database, you need to take care of the migrations path as well. For it, you need to add a migrations_path  setting in the database.yml file as shown above. We haven’t set migrations path for the replica database, but we have replica: true for it.

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:

xxxxxxxxxx

class Post < ApplicationRecord

  self.abstract_class = true

  connects_to database: { writing: :blogs, reading: :blogs_replica }

end

Now that you can access the new database, let’s briefly discuss multiple database features:

1. 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.

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

xxxxxxxxxx

config.active_record.database_selector = 2.seconds

Rails only sends a GET or HEAD request to the primary only if the read request is within what we configured above. By default, it will be set to 2 seconds. You can change it based on your database infrastructure.

2. Manual connection switching between primary and replica database: 

To connect to the replica or primary database manually; Rails provides the ActiveRecord::Base.connected_to method.

There are some cases where your application needs to connect to primary or replica without bothering with the request type. In such cases, you can utilize the connected_to  method provided by ActiveRecord.

xxxxxxxxxx

ActiveRecord::Base.connected_to(database: blogs) do

  CollectPostsInformationWorker.perform(Post.all.pluck(:id, :title))

end

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

xxxxxxxxxx

ActiveRecord::Base.connected_to(database: { reading: :blogs }) do

  CollectPostsInformationWorker.perform(Post.all.pluck(:id, :title))

end

This will use the connection to read the replica of the blog's database and use it.

Now, let’s see what features Rails 6 does not provide with a multi-DB feature.

Features such as sharding, load balancing of replicas, and joining across the multiple databases are not supported in Rails 6.

Some of these features may hopefully be added in the future.

Further Reading

Rails Logger and Rails Logging Best Practices