Establish a connection to another database only in the block?
In a rails app, 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 a simple class. The problem is that the activerecord connection changes and the rest of the requests are made later on in the new connection.
Is it possible to establish a connection only in the block and return to the old connection. I know I can establish another connection, but this is very bad for performance.
source to share
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 exactly the same as
OtherDB.table_name = "table_name"
OtherDB.first
Check out my blog here http://imnithin.in/multiple-database.html
source to share
You can run some queries in a block. First, define some module that extends ActiveRecord as shown below. This is a piece of code used in production to change the db connection for every request, and also to temporarily switch the db to do some queries on a different 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 like below:
ActiveRecord.with_db("db_connection_name") do
# some queries to another db
end
source to share
We found the shortest example in the Rails codebase at activerecord / test / support / connection_helper.rb and adapted slightly:
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 (assuming you have a another_db:
section in yours database.yml
):
with_another_db(ActiveRecord::Base.configurations['another_db']) do
ActiveRecord::Base.connection.execute("SELECT 'Look ma, I have changed DB!';")
end
source to share
I am using environment variables taken from Heroku DATABASE_URL
to connect to various 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
eg:.
Database.production!; puts User.all.map(&:name)
Database.staging!; puts User.all.map(&:name)
source to share
It can help to use an instance variable to store the connection. Something like that:
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
This way the existing connection is retrieved on future connection attempts rather than creating a new one.
source to share
If you want to connect to postgres sql you can use pg ruby gem and add below code inside 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 the mysql2 ruby gem and 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
source to share