Skip to main content

Rails Multi-Database — Overview

Overview​

Rails has first-class support for multiple databases. You can:

  • define multiple databases (each may have a read replica),
  • define Base classes and models
  • setup mysql

This guide shows a practical setup using:

  • Primary app DB (example: PostgreSQL)
  • Secondary legacy DB (example: MySQL “jodgig”, often read-only)

Core concepts​

  • Roles: Rails uses :writing (primary) and :reading (replica).
  • Named databases: Keys in config/database.yml (e.g., primary, jodgig_replica).
  • Abstract base classes: One base class per DB (e.g., ApplicationRecord, JodgigRecord), then have models inherit from the right base.

Example database.yml​

# config/database.yml

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("WEB_CONCURRENCY") { 1 }.to_i * ENV.fetch("RAILS_MAX_THREADS") { 3 }.to_i %>
  host: <%= Rails.application.credentials.database&.primary&.host || "localhost" %>
  username: <%= Rails.application.credentials.database&.primary&.username || "postgres" %>
  password: <%= Rails.application.credentials.database&.primary&.password || "postgres" %>
  database: <%= Rails.application.credentials.database&.primary&.database || "jodapp" %>

default_replica: &default_replica
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("WEB_CONCURRENCY") { 1 }.to_i * ENV.fetch("RAILS_MAX_THREADS") { 3 }.to_i %>
  host: 127.0.0.1
  username: <%= Rails.application.credentials.database&.replica&.username || "root" %>
  password: <%= Rails.application.credentials.database&.replica&.password || "root" %>
  database: <%= Rails.application.credentials.database&.replica&.database || "jodgig" %>
  port: <%= Rails.application.credentials.database&.replica&.port || 3306 %>
  replica: true
  database_tasks: false

development:
  primary:
    <<: *default
    database: jodapp_api_development

  jodgig_replica:
    <<: *default_replica
    database: jodgig

Notes

  • Mark replicas with replica: true.
  • For external/legacy DBs you don’t manage with Rails, set database_tasks: false to avoid db:create/drop/migrate on them.

Base classes & models​

# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class
  establish_connection :primary
end
# app/models/jodgig_record.rb
class JodgigRecord < ActiveRecord::Base
  self.abstract_class = true
  establish_connection :jodgig_replica
end
# app/models/jodgig/job.rb
# this class
module Jodgig
    class Job < JodgigRecord
        self.table_name = "jod_jobs"
    end
end

References​