Setting up Rails 6 multiple databases on Heroku
Tags: rails, tutorial
Published: 2019-08-06 06:51:49

---
title: Setting up Rails 6 multiple databases on Heroku
published: true
tags: rails, tutorial
canonical_url: https://prathamesh.tech/2019/08/06/setting-up-rails-6-multiple-databases-on-heroku/
---

Rails 6 is just around the corner. The release candidate two was recently [released.](https://weblog.rubyonrails.org/2019/7/30/Rails-6-0-rc2-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

<!--kg-card-begin: code-->
```ruby
production:
  database: cats_and_dogs_production
  adapter: postgresql
  
```
<!--kg-card-end: code-->

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.

<!--kg-card-begin: code-->
```yaml
production:
  database_1:
    database: cats_and_dogs_production
    adapter: postgresql
  database_2:
    database: insights_production
    adapter: mysql2
```
<!--kg-card-end: code-->

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](https://github.com/prathamesh-sonpatki/mutli-db-heroku-example/commit/838da5d929d7be28ad2742194bba340b23895bbb#diff-e31bdf70b15c8f84344c332efe06900d) to start with locally which has following database configuration for development environment.

<!--kg-card-begin: code-->
```yaml
default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3
```
<!--kg-card-end: code-->

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

<!--kg-card-begin: code-->
```yaml
development:
  primary:
    <<: *default
    database: db/development.sqlite3
```
<!--kg-card-end: code-->

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

<!--kg-card-begin: code-->
```yaml
development:
  primary:
    <<: *default
    database: db/development.sqlite3
  animals:
    <<: *default
    database: db/animals_development.sqlite3
```
<!--kg-card-end: code-->

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

<!--kg-card-begin: code-->
```ruby
class Lion < ApplicationRecord
  connects_to database: { writing: :animals, reading: :animals }
end

```
<!--kg-card-end: code-->

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.

<!--kg-card-begin: code-->
```ruby
class AnimalBase < ApplicationRecord
  connects_to database: { writing: :animals, reading: :animals }
end
```
<!--kg-card-end: code-->

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.

<!--kg-card-begin: code-->
```yaml
development:
  primary:
    <<: *default
    database: db/development.sqlite3
  animals:
    adapter: sqlite3
    database: db/animals_dev.sqlite3
    migrations_paths: db/animal_migrate
```
<!--kg-card-end: code-->

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

<!--kg-card-begin: code-->
```sh
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
```
<!--kg-card-end: code-->

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.

<!--kg-card-begin: code-->
```sh
rails db:migrate:primary
rails db:migrate:animals
```
<!--kg-card-end: code-->

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 D[ATABASE\_URL environment variable](https://devcenter.heroku.com/articles/rails-database-connection-behaviorhttps://devcenter.heroku.com/articles/rails-database-connection-behavior) instead of connecting via username and password from `datbase.yml`.

> There was a [bug](https://github.com/rails/rails/issues/36736) in Rails 6 RC2 related to multiple databases and DATABASE\_URL which is [fixed](https://github.com/rails/rails/pull/36756) in the 6-0-stable branch. So [use it](https://github.com/prathamesh-sonpatki/mutli-db-heroku-example/commit/2b754000aed079aeac5dd3ac13fc873ce5eca4d7) 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.

<!--kg-card-begin: code-->
```yaml
production:
  primary:
    adapter: postgresql
```
<!--kg-card-end: code-->

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.

<!--kg-card-begin: image-->

![](https://prathamesh.tech/content/images/2019/08/multi-db---Resources---Heroku-2019-08-05-22-45-25.png)

<!--kg-card-end: image-->

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.

<!--kg-card-begin: code-->
```yaml
production:
  primary:
    adapter: postgresql
  animals:
    migrations_paths: db/animal_migrate
    adapter: postgresql
    url: <%= ENV["HEROKU_POSTGRESQL_OLIVE_URL"] %>
```
<!--kg-card-end: code-->

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](https://github.com/prathamesh-sonpatki/mutli-db-heroku-example). In the next article we will learn advanced techniques supported by multi database feature in Rails 6.

Subscribe to my [newsletter](https://www.prathamesh.tech/mailing-list) to be on the **_Road to Rails 6_**.