Using both MySQL and Postgres with Rails 4 application

Question

I've built a Rails application using MySQL and now would like to move some models to a Postgres database for performance reasons, the tables are quite big and Postgres is faster.

There are no relations between these models and any others. I do not want to move the whole database to Postgres.

This has worked for me after installing the pg gem: Rails: mysql & postgres at the same time in the same app?

Now I have a problem: some cron jobs which load the same database.yml file stopped working because of those models that use the postgres database although the YML is ok, indented fine, reads fine, the "postgres" section is there, the Rails application works fine.

In the postgres-hosted models I do:

class Thing < ActiveRecord::Base
    establish_connection(:postgres)
    self.table_name = "thingies"
end

Then in the cron jobs:

dbconfig = YAML::load(File.open('../database.yml'))    
ActiveRecord::Base.establish_connection(dbconfig["production"])
ActiveRecord::Base.establish_connection(dbconfig["postgres"]) 

Then when I load one of the postgres models I get this error:

/usr/lib/ruby/gems/2.3.0/gems/activerecord-4.2.5/lib/active_record/connection_adapters/connection_specification.rb:248:in `resolve_symbol_connection': 'postgres' database is not configured. Available: [] (ActiveRecord::AdapterNotSpecified

How can I solve this?

Later edit: I have done this instead of establishing a connection manually for each database.

dbconfig = YAML::load(File.open('../database.yml'))
ActiveRecord::Base.configurations = dbconfig

Now the errors above are gone, but the MySQL models throw errors as it appears ActiveRecord establishes the connection to postgres and leaves it open.

Is there a way to specify what connection to use for the models that don't specify what connection to use? Sorts of a default?

Answer 1

Solved by putting models into sub-classes of ActiveRecord and managing connections from them sub-classes.

This also solved my next problem which would have been maintaining a pool of several hundred connections per type of model.

You can have two classes to subclass your models from:

class PostgresRecord < ApplicationRecord
    self.abstract_class = true
    establish_connection POSTGRES_DATABASE
end

and

class MysqlRecord < ApplicationRecord
    self.abstract_class = true
    establish_connection MYSQL_DATABASE
end

The constants come from initializers, eg:

POSTGRES_DATABASE = YAML::load( ERB.new( File.read("#{ Rails.root }/config/database.postgres.yml") ).result )[Rails.env.to_s]

Define your pool size in each .yml file.

https://www.thegreatcodeadventure.com/managing-multiple-databases-in-a-single-rails-application/

You can use different directories for db related files eg migrations schema seeds, I use the default "db" for MySQL and "db_pg" for Postgres. You can also set up the generators and rake tasks:

lib/generators/pg_migration_generator

require 'rails/generators/active_record/migration/migration_generator'

class PgMigrationGenerator < 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_pg/migrate/#{file_name}.rb"
  end
end

And the rake task:

lib/tasks/pg.rake

desc "Managing the pg database"
task spec: ["pg:db:test:prepare"]

namespace :pg do
    desc "Manage database"

  namespace :db do |ns|
    desc "Pg"

    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 ["pg:set_custom_config"] do
        Rake::Task["pg:revert_to_original_config"].invoke
      end
    end
  end

  task :set_custom_config do
    @original_config = {
      env_schema: "db/schema.rb",
      config: Rails.application.config.dup
    }

    # set config variables for custom database
    ENV['SCHEMA'] = "db_pg/schema.rb"
    Rails.application.config.paths['db'] = ["db_pg"]
    Rails.application.config.paths['db/migrate'] = ["db_pg/migrate"]
    Rails.application.config.paths['db/seeds.rb'] = ["db_pg/seeds.rb"]
    Rails.application.config.paths['config/database'] = ["config/database.postgres.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

I am in process of putting together a template for Rails applications with out of the box support for different database back-ends (PG, MySQL, Mongo, Arango...) within a Docker stack, basically a one liner to set up the whole thing, it will pop up soon on Github.