Skip to content

Instantly share code, notes, and snippets.

@coldnebo
Created October 11, 2018 16:05
Show Gist options
  • Save coldnebo/b4c2b2758f5290c237eaed1c7b79e4a8 to your computer and use it in GitHub Desktop.
Save coldnebo/b4c2b2758f5290c237eaed1c7b79e4a8 to your computer and use it in GitHub Desktop.
description of our Rails multi-database configuration for @eileencodes
---
# 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
# 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
---
# 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
@coldnebo
Copy link
Author

coldnebo commented Oct 11, 2018

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment