Establish a connection to another database only in a block?

Question

In a rails application, I have this code in pure ruby :

class LinkCreator
  attr_accessor :animal

  def initialize(animal:)
    @animal = animal
  end

  def call
    "something#{link_id}"
  end

  private

  def link_id
    connection.execute(sql_request).first.first
  end

  def sql_request
    "SELECT field FROM table WHERE field_id = '#{field_id}' LIMIT 1"
  end

  def field_id
    animal.field_id
  end

  def connection
    ActiveRecord::Base.establish_connection(
      adapter:  "mysql",
      host:     ENV["MYSQL_HOST"],
      username: ENV["MYSQL_USERNAME"],
      password: ENV["MYSQL_PASSWORD"],
      database: ENV["MYSQL_DB_NAME"]
    ).connection
  end
end

As you can see, this is not a model but only a simple class. The problem is than the connection of activerecord is changed and the other requests, later, are executed on the new connection.

Is it possible to establish a connection only in a block and go back to the old connection. I know I can establish another connection but this is very bad for performance.

Answer 1

It would be nice if you keep all database connections in database.yml

development:
  adapter: mysql2
  other stuff...
  
db_2:
  adapter: mysql2
  other stuff..

other_envs:
.....

Then create a class

class OtherDB < ActiveRecord::Base
  establish_connection(:db_2)
end

From your controller you can access just like

OtherDB.table_name = "table_name"
OtherDB.first

Check my blog here http://imnithin.github.io/multiple-database.html

Answer 2

Have found most short example in Rails codebase at activerecord/test/support/connection_helper.rb and slightly adapted:

def with_another_db(another_db_config)
  original_connection = ActiveRecord::Base.remove_connection
  ActiveRecord::Base.establish_connection(another_db_config)
  yield
ensure
  ActiveRecord::Base.establish_connection(original_connection)
end

Usage (given that you have another_db: section in your database.yml):

with_another_db(ActiveRecord::Base.configurations['another_db']) do
    ActiveRecord::Base.connection.execute("SELECT 'Look ma, I have changed DB!';")
end

Answer 3

You can perform some queries within a block. First, define some module which will extend ActiveRecord, as below. This is a part of code used in production to change db connection per each request as well as to temporarily switch db to perform some queries within another database.

# RAILS_ROOT/lib/connection_switch.rb
module ConnectionSwitch
  def with_db(connection_spec_name)
    current_conf = ActiveRecord::Base.connection_config

    begin
      ActiveRecord::Base.establish_connection(db_configurations[connection_spec_name]).tap do
        Rails.logger.debug "\e[1;35m [ActiveRecord::Base switched database] \e[0m #{ActiveRecord::Base.connection.current_database}"
      end if database_changed?(connection_spec_name)

      yield
    ensure
      ActiveRecord::Base.establish_connection(current_conf).tap do
        Rails.logger.debug "\e[1;35m [ActiveRecord::Base switched database] \e[0m #{ActiveRecord::Base.connection.current_database}"
      end if database_changed?(connection_spec_name, current_conf)
    end

  end

  private
  def database_changed?(connection_spec_name, current_conf = nil)
    current_conf = ActiveRecord::Base.connection_config unless current_conf
    current_conf[:database] != db_configurations[connection_spec_name].try(:[], :database)
  end

  def db_configurations
    @db_config ||= begin
      file_name =  "#{Rails.root}/config/database.yml"
      if File.exists?(file_name) || File.symlink?(file_name)
        config ||= HashWithIndifferentAccess.new(YAML.load(ERB.new(File.read(file_name)).result))
      else
        config ||= HashWithIndifferentAccess.new
      end

      config
    end
  end
end
ActiveRecord.send :extend, ConnectionSwitch

Now you can use it as below:

ActiveRecord.with_db("db_connection_name") do
  # some queries to another db
end

Answer 4

If you want to connect to postgres sql, you can use pg ruby gem & add the below code inside the block.

postgres = PG.connect :host => <host_name>, :port => <port>, :dbname => <database_name>, :user => <user>, :password => <password>
    tables = postgres.exec(query)

    tables.num_tuples.times do |i|
      print tables[i]
    end

To connect to mysql db inside a block, use mysql2 ruby gem & add the below code inside the block.

db = Mysql2::Client.new ActiveRecord::ConnectionAdapters::ConnectionSpecification::ConnectionUrlResolver.new(<database_url>).to_hash

    data = db.query(<<-SQL)
      select * from students
    SQL

    print data

Answer 5

I use environment variables taken from Heroku's DATABASE_URL to connect to different databases:

class Database
  def self.development!
    ActiveRecord::Base.establish_connection(:development)
  end

  def self.production!
    ActiveRecord::Base.establish_connection(ENV['PRODUCTION_DATABASE'])
  end

  def self.staging!
    ActiveRecord::Base.establish_connection(ENV['STAGING_DATABASE'])
  end
end

e.g.:

Database.production!; puts User.all.map(&:name)
Database.staging!; puts User.all.map(&:name)

Answer 6

It might help to use an instance variable to store the connection. Something like this:

def connection
  @connection ||= ActiveRecord::Base.establish_connection(
    adapter:  "mysql",
    host:     ENV["MYSQL_HOST"],
    username: ENV["MYSQL_USERNAME"],
    password: ENV["MYSQL_PASSWORD"],
    database: ENV["MYSQL_DB_NAME"]
  ).connection
end

That way the existing connection is retrieved on future connection attempts, rather than establishing a new one.

Answer 7

Try active_record_slave gem:

Ruby gem: active_record_slave