Sources used:
- Setting up multiple databases in Rails: the definitive guide by Roberto Ostinelli
- Rails Multi-Database Best Practices Roundup by Ken Collins, CustomInk
- Using ActiveRecord with multiple databases by John Baker, Pivotal Labs.
There are many different scenarios why you may want to have more than one database in your Rails application. For instance, to segregate large DB into several small DBs like accounts, logs, etc. Or you may have client that already has some database replication going on in their deployment. Another use case is migrating from an old schema to a new one. Last, but not least, it can also be helpful if there are various data requirements within one application, like when some data is more critical and is stored on a high-availability database cluster. In my case I needed a read only connection to retrieve data collected by other service that didn’t have an API.
If you want to implement Database Sharding then there is well-known Octopus gem to use. But in other scenarios it is also not difficult to setup additional database without any gem dependencies.
When I was looking for guides and best practices on setting up multiple databases I have read many articles across the net. Some of them were really helpful and I referenced them above. In this post I am going to sum up everything I’ve learned.
1) Create files and directories for second database
You will probably want to separate database files so the initial DB would not mess with the new one. So new nodes in project structure will look like this:
-- config
database_second.yml
-- db_second
|-- migrate
schema.rb
seeds.rbIn this way second database files will live in isolation from the primary database.
db_second/schema.rb, db_second/seeds.rb and db_second/migrate should be just empty.
The database_second.yml is just a usual database configuration file, nothing specific there.
2) Rake tasks
Below is a set of custom Rake tasks that provide the same functionalities that Rails provides us for the primary database. The detailed explanation of this file is available in Roberto Ostinelli post Setting up multiple databases in Rails: the definitive guide
Once all of this is set up, let’s create the second database and run its first migration:
$ rake second:db:create
$ rake second:db:migrateThis will generate the Stats database schema file in db_second/schema.rb
3) Add a custom generator
* You can also take a look at customer generator part in original post.
Unfortunately, it is not possible to simply use Rails’ generator ActiveRecord::Generators::MigrationGenerator because it hardcodes the parent directory of the migration (notice the path hardcoded to the directory db/migrate in last but one line here below):
That is why a custom generator to create migrations for the second database is required. It is possible to inherit from ActiveRecord::Generators::MigrationGenerator and monkey patch this specific function:
Here templates directory is initialized and pointed to the original one used by the superclass. Then migration_template base directory is being set to a Second database migration directory.
With this patch it is possible to create migration for the second db from the command line:
rails g second_migration create_awesome_stuff#=> create db_second/migrate/20151201191642_create_awesome_stuff.rb
and run it:
rake second:db:migrate4) Create initializer
Extract second database settings to a constant. This constant is going to be used in second database models abstract parent class:
# config/initializers/db_second.rbconfig=YAML::load(ERB.new(File.read(Rails.root.join(“config”,“database_second.yml”))).result)DB_SECOND = config[Rails.env]
DO NOT freeze this constant otherwise active record will not be able to delete credentials from connection configuration hash and, therefore, will raise an error. See the snippet below from ActiveRecord 4.2 stable branch:
5) Create Second database base class
In order to map rails model to second database you have to manually establish connection to db. There are many examples across the net (in blog posts, on StackOverflow and even in comments to rails api dock) where people suggest to call another DB inside a model like this:
class SecondBaseUser < ActiveRecord::Base
establish_connection DB_SECOND
endDO NOT do this. This way not only leads to many lines of duplicated code in models but also opens a new connection to database per each model!(Read John Baker post Using ActiveRecord with multiple databases and AR docs about this)
There is a better way. It is possible to create an abstract superclass for all second database models and inherit it from ActiveRecord::Base. I also suggest to create a separate namespace for all second database models.
module Second
class Base < ActiveRecord::Base
self.abstract_class = true
establish_connection DB_SECOND
end class User < Second::Base
end
end
This approach utilizes shared connections to second database and also DRY’s models.
It is also possible to redefine table name generation for second database models inside an Abstract base class:
module Second
class Base < ActiveRecord::Base
self.abstract_class = true
establish_connection DB_SECOND
def self.inherited(subclass)
super
subclass.table_name = # Your code here…
end
end
end6) Maintain second db schema in tests
In order to automatically load changes in second database in test environment I added these lines to my test support files:
if defined?(ActiveRecord::Migration)
current_db = Rails.configuration.database_configuration[Rails.env]
ActiveRecord::Base.establish_connection DB_SECOND
ActiveRecord::Migration.maintain_test_schema!
ActiveRecord::Base.establish_connection current_db
ActiveRecord::Migration.maintain_test_schema!
end7) Optional: Database cleaner for RSpec users
If you are using DatabaseCleaner gem, you can set it to clean the models that use the Stats database too:
Please note that with this config DBCleaner would not clean second database before example. When I tried to enable second database cleaning before example it led to multiple database errors during specs runtime which I could not resolve and, unfortunately, I haven’t got a spare moment to investigate this problem deeper.
See also:
