Setting up Rails 6 multiple databases on Heroku

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.

When we create a new brand new Rails 6 app, it is configured to use only one database. To start using multiple databases, we need to switch the database configuration from something called as two-tier configuration to three-tier configuration.

Two tier v/s Three tier database configuration

To understand what it means, let's take a look at sample configuration from database.yml

production:
  database: cats_and_dogs_production
  adapter: postgresql

This is an example of two-tier configuration. The two tiers are the environment and the database configuration.

The three-tier configuration includes a middle tier for specifying multiple databases. It looks like this.

production:
  database_1:
    database: cats_and_dogs_production
    adapter: postgresql
  database_2:
    database: insights_production
    adapter: mysql2

When Rails finds this three tier configuration in the database.yml it will treat your Rails application as multi database application.

So to use multiple databases, we need to change the default two-tier configuration to three-tier configuration in the database.yml.

I created a sample Rails app to start with locally which has following database configuration for development environment.

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3

We will change this two tier configuration to three tier configuration for multiple databases.

development:
  primary:
    <<: *default
    database: db/development.sqlite3

primary indicates the main database the application will use. This is the same database configuration that was present for a single database application that we saw earlier. The only difference is that now it is three tier instead of two.

Rails treats primary key in a special manner. It is your main database.

Now we can add a second database animals to the database.yml.

development:
  primary:
    <<: *default
    database: db/development.sqlite3
  animals:
    <<: *default
    database: db/animals_development.sqlite3

The primary database must come first and all the other databases must come after that. Though this is not mentioned anywhere I ran into random issues related to animals migrations being run in primary database if the order is switched both locally and on Heroku.

So far so good!

Creating models and migrations

Next step is to create few models. By default, all models in a Rails application inherit from ApplicationRecord and connect to single database. But now we want some of our models to connect to the primary database and some to the animals database. Rails 6 provides a way to connect to a database from a model as follows.

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

The connects_to line simply tell Rails that the Lion model will connect to animals database for both writing and reading purpose. The word animals here matches with the animals key in our updated database.yml.

In real world scenario, you could use a read replica for reading operations to reduce load on the master database.

Ideally, instead of repeating this configuration in every model which needs to connect to animals database, we should create a base class for all animals models.

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

Now our models are configured to connect to the second database. Let's actually create some data to get started. We first need to create the tables in the animals database.

When we create migrations, by default they are created for primary database in the db/migrate directory. If we want to create migrations for animals directory, Rails recommends storing them in a separate directory. We need to add a configuration option migrations_paths in the database.yml for this purpose.

development:
  primary:
    <<: *default
    database: db/development.sqlite3
  animals:
    adapter: sqlite3
    database: db/animals_dev.sqlite3
    migrations_paths: db/animal_migrate

We can create migrations for animals database by passing the database name to the migration generator command.

rails generate migration AddLions country:string age:integer --db=animals
Running via Spring preloader in process 86684
      invoke active_record
      create db/animal_migrate/20190805164716_add_lions.rb

This creates the new migration in animal_migrate directory which we have configured in the database.yml.

If we forget to add the migrations_paths key to the database.yml then Rails does not raise any error as of now but it means it will not create separate directory for the migrations related to animals database.

When we run rails db:migrate it runs migrations for all our databases including animals. We can also run migrations for a particular database.

rails db:migrate:primary
rails db:migrate:animals

At this point of time, we are successfully using multiple databases locally. Time to go to production on Heroku.

Deploying multi database app on Heroku

We need to add pg gem for deploying on Heroku and update the production section of the database.yml. The first step is to change to three tier configuration for the primary database. But Heroku relies on the DATABASE_URL environment variable instead of connecting via username and password from datbase.yml.

There was a bug in Rails 6 RC2 related to multiple databases and DATABASE_URL which is fixed in the 6-0-stable branch. So use it instead of RC2 to work with multiple databases on Heroku.

Heroku sets the DATABASE_URL environment variable and it is used by Rails automatically so we don't need to specify it in the configuration.

production:
  primary:
    adapter: postgresql

Now to use the animals database, we need to provision it. If you are just playing around, you can add a PostgreSQL hobby addon to your app for this purpose.

Heroku provisions one PostgreSQL addon for your app by default. So you need to provision one more for using multiple databases.

The Heroku PostgreSQL addon generates a environment variable which has the configuration for the new database. In my case it was HEROKU_POSTGRESQL_OLIVE_URL which we will use to connect to the animals database.

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

That's all! Now our app is configured to use multiple databases on Heroku. When we deploy and run rails db:migrate everything will be setup nicely and we can start using multiple databases on Heroku with Rails 6.

The code for this article can be found here. In the next article we will learn advanced techniques supported by multi database feature in Rails 6.

Subscribe to my newsletter to be on the Road to Rails 6.