Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Ruby script connect to Mysql2 using database.yml without Rails

Tags:

ruby

yaml

mysql2

I'd like to connect to a mysql database using the mysql2 gem in a Ruby script but without Rails or ActiveRecord, yet reading the config/database.yml file so as not to expose the user name and password directly inside the Ruby script. I can connect if I use ActiveRecord like this:

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

But if I try the same trick for the Mysql2 connection I get an error:

client = Mysql2::Client.new(dbconfig['production'])

Obviuosly the syntax is different, I need something like:

client = Mysql2::Client.new(:host => "localhost", :username => "user", :password => 'password', :database => 'db', :socket => '/tmp/mysql.sock')

But don't want to expose the user name and password directly inside the script.

so how could I grab all the data from the config/database.yml and pass it to the Mysql2::Client.new() method?

Thanks.

Edit

Just wanted to clarify that to finally get it to work I modified the accpeted answer a bit by doing this:

client = Mysql2::Client.new(:host => dbconfig['hostname'], :username => dbconfig['username'], :password => dbconfig['password'], :database => dbconfig['database'], :socket => '/tmp/mysql.sock')

Simply doing Mysql2::Client.new(config) would not work because it would not pick up the username and password.

like image 504
kakubei Avatar asked Oct 02 '12 10:10

kakubei


2 Answers

Any method that accept a hash can be fed with the result of a YAML parsing.

You may have two issue here :

  • YAML.load_file returns a hash where the keys are strings and not symbols. From what I read from https://github.com/brianmario/mysql2/blob/master/lib/mysql2/client.rb#L17 and https://github.com/brianmario/mysql2/blob/master/lib/mysql2.rb#L36 it accepts both symbols and strings, so this isn't the problem.
  • Mysql2 connect method uses different keys, like host instead of hostname in database.yml, you might consider creating a method that converts the keys if you don't or can't modifiy the database.yml.

The following code should work :

config = YAML::load_file("config/database.yml")["development"]
config["host"] = config["hostname"]

client = Mysql2::Client.new(config)
like image 108
jhchabran Avatar answered Dec 04 '22 05:12

jhchabran


My solution was similar to the accepted answer, except my database.yml had erb snippets for environment variable references:

development:
  <<: *default
  database: <%= ENV['DEV_DB_NAME'] %>
  username: <%= ENV['DEV_DB_USER'] %>
  password: <%= ENV['DEV_DB_PASS'] %>

So I load through ERB first:

require 'erb'
require 'mysql2'

config = YAML.load(ERB.new(
  File.new("config/database.yml").read).result(binding))['development']
config["host"] = config["hostname"]
client = Mysql2::Client.new(config)

Thought it might help someone else as this was the first search result I found. Thanks to question and accepted answer!

like image 24
joshweir Avatar answered Dec 04 '22 04:12

joshweir