May 17, 2020

Multiple Databases with Rails 6 and RDS

Rails 6 shipped with the ability to use multiple databases in one application, making automatic connection switching as simple as adding a connects_to method in the respective class. To go a step further, we'll set up an Amazon RDS instance, which benefits team members by providing consistent access to the same database—which could contain a copy of production data that will be useful to test against—avoiding development environment configuration, and improving horizontal scaling.

AWS offers a free tier for RDS, with 750 hours of db.t2.micro instance usage, 20 GB of General Purpose (SSD) DB Storage, and 20 GB of backup storage for automated database backups. The free tier is available for 12 months from the account creation date.

"The service handles time-consuming database management tasks so you can pursue higher value application development." – AWS

We'll first want to create a database in the Amazon RDS console using the Standard Create method with PostgreSQL. You should set Publicly Accessible to Yes in order to connect from the Rails application.

Once the instance is ready, it will provide an endpoint:

<app>.ca-central-1.rds.amazonaws.com

If you're using PostGIS, you'll want to create the extension, and prefix the url with postgis:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION postgis_tiger_geocoder;postgis://<user>:<pass>@<app>.ca-central-1.rds.amazonaws.com:<port>/<database>

Now in the Rails application's database.yml file, we have to specify the url, database, and migrations_path. To add replica databases, include replica: true, and ensure it has the same database name:

default: &default
  adapter: postgis
  encoding: unicode
  postgis_extension: true
  schema_search_path: 'public,postgis'
  host: localhost
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000
  port: 5432

development:
  primary:
    <<: *default
    database: development
  primary_replica:
    <<: *default
    database: development
    replica: true
  rds:
    <<: *default
    url: endpoint
    database: development_rds
    migrations_path: db/migrate_rds
  rds_replica:
    <<: *default
    url: endpoint
    database: development_rds
    replica: true

Rails middleware adds basic automatic switching from primary to replica based on the HTTP verb:

module App
  class Application < Rails::Application
    config.active_record.database_selector = { 
      delay: 2.seconds 
    }
  end
end

Now all that's needed is to set up the models by connecting to the new database:

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  connects_to database: {
    writing: :primary,
    reading: :primary_replica
  }
endclass Geojson < ApplicationRecord
  connects_to database: { 
    writing: :rds,
    reading: :rds_replica
  }
end