Usage Of SQL And NoSQL Databases In Single Rails Application(MySQL, PostgreSQL And MongoDB)

There are distinctive reasons why you should think about having various databases in your Ruby on Rails application. In my situation, I expected to store large quantities of data. Consider default database is MySQL. In our application database.yml file write connections for MySQL in normal way. After that, for connecting postgresql in the same application we need to create custom files. Create the custom database files to connect postgresql We’re going to set up a second database called “Stats” First of all, create the file config/database_stats.yml and populate it as you do with the primary database’s config file. Your file will look something like this: development:   adapter: postgresql   encoding: utf8   reconnect: false   database: db_info_development   pool: 5   host: localhost   username: postgres   password: We’re now going to create a directory that will hold the schema and all the migrations of the Stats database. Create directory with name db_stats in the rails root and copy the structure as mentioned below –db   –migrate   schema.rb   seeds.rb –db_stats   –migrate   schema.rb   seeds.rb The created files should be empty. Add Rake Tasks For handling stats database, we need to write custom tasks for creation, migrations and other functionalities. Create a file lib/tasks/db_stats.rake with the below content namespace :stats do   namespace :db do |ns|     task :drop do       Rake::Task[“db:drop”].invoke     end     task :create do       Rake::Task[“db:create”].invoke     end     task :setup do       Rake::Task[“db:setup”].invoke     end     task :migrate do       Rake::Task[“db:migrate”].invoke     end     task :rollback do       Rake::Task[“db:rollback”].invoke     end     task :seed do       Rake::Task[“db:seed”].invoke     end     task :version do       Rake::Task[“db:version”].invoke     end     namespace :schema do       task :load do         Rake::Task[“db:schema:load”].invoke       end       task :dump do         Rake::Task[“db:schema:dump”].invoke       end     end     namespace :test do       task :prepare do         Rake::Task[“db:test:prepare”].invoke       end     end     # append and prepend proper tasks to all the tasks defined here above     ns.tasks.each do |task|       task.enhance [“stats:set_custom_config”] do         Rake::Task[“stats:revert_to_original_config”].invoke       end     end   end   task :set_custom_config do     # save current vars     @original_config = {       env_schema: ENV[‘SCHEMA’],       config: Rails.application.config.dup     }     # set config variables for custom database     ENV[‘SCHEMA’] = “db_stats/schema.rb”     Rails.application.config.paths[‘db’] = [“db_stats”]     Rails.application.config.paths[‘db/migrate’] = [“db_stats/migrate”]     Rails.application.config.paths[‘db/seeds’] = [“db_stats/seeds.rb”]     Rails.application.config.paths[‘config/database’] = [“config/database_stats.yml”]   end   task :revert_to_original_config do     # reset config variables to original values     ENV[‘SCHEMA’] = @original_config[:env_schema]     Rails.application.config = @original_config[:config]   end end Once all of this setup is done, we can create the stats database and run its first migration:

$ rake stats:db:create $ rake stats:db:migrate

This will generate the Stats database schema file in db_stats/schema.rb. Add a custom migration generator We cannot use rails generator because the path hardcodes the db/migrate. Therefore, we need to have custom generator to create all migrations for the stats database. Create the generator in lib/generators/stats_migration_generator.rb and paste the following code: require ‘rails/generators/active_record/migration/migration_generator’ class StatsMigrationGenerator < ActiveRecord::Generators::MigrationGenerator source_root File.join(File.dirname(ActiveRecord::Generators::MigrationGenerator. instance_method(:create_migration_file) .source_location.first), “templates”)   def create_migration_file     set_local_assigns!     validate_file_name!     migration_template @migration_template, “db_stats/migrate/#{file_name}.rb”   end end With all of this in place, we can now generate migrations for the Stats database:

$ rails g stats_migration migration_name

This migration file gets created in the stats database migrate directory db_stats/migrate. After that, run the following command to migrate.

$ rake stats:db:migrate

Finalize connection and models for PostgreSQL We’re almost done. Add a new initializer file config/initializers/db_stats.rb and paste the following:

# save stats database settings in global var DB_STATS = YAML::load(ERB.new(File.read(Rails.root.join(“config”,”database_stats.yml”))).result)[Rails.env]

Configuring mongodb: Mongoid gem is an ODM (Object-Document-Mapper) framework for MongoDB in Ruby.

gem ‘mongoid’, ‘~> 5.1.0’

You can generate a config file by executing the generator. Mongoid will then handle everything else from there.

$ rails g mongoid:config

Then it creates mongoid.yml as below development:     clients:         default:             database: mongoid             hosts:                 -localhost:27017 Now in our application we have successfully integrated the MySQL, postgresql and mongodb Consider three models – User, Product, Image. Using these three models will help us connect different databases in our application. User model with MySQL connection class User < ActiveRecord::Base end $ rails g migration create_users field_names rake db:migrate This migration file gets created under db/migrate and it will store under MySQL database. class Product < ActiveRecord::Base     establish_connection DB_STATS end $ rails g stats_migration create_products       create  db_stats/migrate/20151201191642_create_products.rb This migration file gets created in the stats database migrate directory db_stats/migrate. class Image     include Mongoid::Document     field :name, type: String     field :description, type: String end No need for running any migrations for mongodb since it is ODM. This data is created under MongoDB. Feel free to contact us if you have any query.