description of our Rails multi-database configuration for @eileencodes

database.yml

---

# defaults for all envs (local/deployed), (nonprod/prod)
defaults: &defaults
  encoding: utf8
  adapter: mysql2
  read_timeout: 10
  write_timeout: 10
  connect_timeout: 10
  reconnect: true
  # in deployed environments, we use [Tungsten](https://docs.continuent.com/tungsten-clustering-5.3/release-notes-5-3-3.html)
  # to map localhost to a desired mysql cluster, so all our deployed conns default to localhost.
  host: 127.0.0.1

# non-prod environments (mysql virtualization) e.g. RAILS_ENV = stage, etc.  
# (we have 11 nonprod RAILS_ENVs in addition to development and test, which have been ommitted for brevity)
nonprod_defaults: &nonprod_defaults
  <<: *defaults
  # the database/pw in nonprod envs are different than in prod, to prevent accidental access, etc.
  database: xxx
  password: xxx



# ============ DEPLOYED ENVIRONMENTS ===============

# RAILS_ENV="stage".
stage: 
  <<: *nonprod_defaults

# RAILS_ENV="prod" has it's own db and pw.
prod: 
  <<: *defaults
  database: xxx
  password: xxx



# ============= LOCAL DEVELOPMENT =================
# These aliases are for use in RAILS_ENV="development" 


# in local development, we don't have Tungsten config, so we have to point directly at a MySQL alias.
# we only use these configs in RAILS_ENV=development.
local_stage: &local_stage
  <<: *nonprod_defaults
  host: xxx

# for local development, we must use a read-only replica, we are not allowed to connect to prod dbs.
local_prod: &local_prod
  <<: *nonprod_defaults
  host: xxx
  port: xxx
  username: xxx
  password: xxx

# local sqlite db for local development and test
local_sqlite: &local_sqlite
  adapter: sqlite3
  database: db/local.sqlite3
  pool: 5
  timeout: 5000


# RAILS_ENV="development"
# we can 'point at' deployed databases to pick up additional data backfilled from production.
# i.e. if we db:migrate with local_sqlite, we get the schema but not the data.  In test we can use
# fixtures to mock data.  But often we also need to debug against real data, which involves 
# 'pointing at' a database instance to debug, hence this structure.
development:
  <<: *local_stage 

# RAILS_ENV="test" -- only for rspec/etc.
# WARNING: NEVER use this with anything but local_sqlite.  
# Test runs during unit test and will drop existing databases afterwards to clean up.  
# You don't want this droping deployed databases becuase you'll lose data not in the migrations!!
test: 
  <<: *local_sqlite
  database: db/test.sqlite3

reportdb.rb

# from http://stackoverflow.com/questions/1298909/multiple-database-connection-in-rails
$config = YAML.load_file(File.join(File.dirname(__FILE__), '../../config/reportdb.yml'))

# using a single abstract class to share the connection as described in
# http://stackoverflow.com/a/6126706/555187 
class ReportDB < ActiveRecord::Base
  establish_connection $config[Rails.env]
end

reportdb.yml

---

# defaults for all envs (local/deployed), (nonprod/prod)
defaults: &defaults
  mode: dblib
  adapter: sqlserver
  encoding: utf8
  username: xxx
  password: xxx
  database: xxx
  timeout: 0


# ============ DEPLOYED ENVIRONMENTS ===============

# RAILS_ENV="stage".
stage: &stage
  <<: *defaults
  dataserver: xxx

# RAILS_ENV="prod"
prod:
  <<: *defaults
  # the password is different in prod.
  password: xxx
  dataserver: xxx
  

# ============= LOCAL DEVELOPMENT =================

# NOTE: there is no local_stage, because we are using a DSN (i.e. dataserver) reference which 
# FreeTDS resolves to a specific database connection in freetds.conf.  This file is maintained by OPS.
# The freetds.conf can be used in either deployed or local contexts, so we can use the *stage alias directly
# in development.

# NOTE: the prod alias is not accessible outside of the deployed prod env.

# local sqlite db for local development and test
local_sqlite: &local_sqlite
  adapter: sqlite3
  database: db/local.sqlite3
  pool: 5
  timeout: 5000



# RAILS_ENV="development"
# we can 'point at' deployed databases to pick up additional data backfilled from production.
# i.e. if we db:migrate with local_sqlite, we get the schema but not the data.  In test we can use
# fixtures to mock data.  But often we also need to debug against real data, which involves 
# 'pointing at' a database instance to debug, hence this structure.
development: 
  <<: *stage


# RAILS_ENV="test" -- only for rspec/etc.
# WARNING: NEVER use this with anything but local_sqlite.  
# Test runs during unit test and will drop existing databases afterwards to clean up.  
# You don't want this droping deployed databases becuase you'll lose data not in the migrations!!
test: 
  <<: *local_sqlite
  database: db/test.sqlite3

Hi @eileencodes!

Thanks for asking for feedback on how different apps use multiple database connections in Rails. Here's how we use it in one of my applications:

• We have one MySQL db which we use as our Rails db (database.yml).
• We also have two MSSQL databases which we use. I show one of them (reportdb.yml) and how we bind it in our models (reportdb.rb).

Our multiple database use is collecting separate information rather than dealing with replicas for varying read/write performance. But I like the example in your PR -- it might address pains in our WAN replication, so I'm keeping it in mind.

Although we don't manage replica/clustering directly, we do have infrastructure supported by OPS that manages that complexity in a different way.

• On the MySQL side, we have Tungsten which maps localhost connections to clusters according to a central config managed by OPS.
• On the MSSQL side, we use FreeTDS to map DSNs to server aliases via freetds.conf managed by OPS. The server aliases are actually MSSQL clusters which are joined with other WAN clusters via replication queues.