Ruby script connect to Mysql2 using database.yml without Rails

Question

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.

I've added an answer which should cover your needs, but you may have another issue, would you mind pasting your errors in the case my answer doesn't fit ? — jhchabran, Oct 02 '12 at 15:35

score 11 · Accepted Answer · answered Oct 02 '12 at 15:34

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)

I should clarify that directly with `Client.new(config)` did not work. I had to specify the fields separately. Look at the edit in the question to see what I mean. — kakubei, Oct 03 '12 at 08:59
Ah yeah, this is even simpler to work around the keys difference, I don't know where my head was to not suggest that yesterday :) — jhchabran, Oct 03 '12 at 13:07

score 1 · Answer 2 · answered May 15 '16 at 08:40

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!

score -1 · Answer 3 · answered Oct 02 '12 at 10:55

-1

You can use sequel to do just that. The advantage of using sequel is that you can use good Ruby style to talk to your db. There are lots of examples on its documentaiton page.

answered Oct 02 '12 at 10:55

three

8,262
3
35
39

sequel looks interesting, but after a quick scan of the page, nowhere do I see where I can use a databse.yml file to connect to it. All the connection options I see specify the user and password in the file. – kakubei Oct 02 '12 at 11:14
`DB = Sequel.connect('postgres://localhost/blog' :user=>USER, :password=>PASSWORD)` you can put `USER` and `PASSWORD` into a separate file like `USER = "me"; PSSWORD="secret"` and then `require "file"` – three Oct 02 '12 at 15:35
So... how does this achieve the "reading from database.yml" in the original question? Sorry, it's a -1. – aqn Jan 08 '13 at 20:12
@aqn, of course not. The accepted answer had none of that either because an ORM doesn't have to read yml files. So I gave an alternative way to access and provide secure username/password storage. Obviously you can also use a YAML file and require the yaml lib to waste a little bit of extra memory for something that can as easily done without. – three Jan 08 '13 at 22:58
@three: keep in mind this is probably being done as part of a larger project, and there may be very good reasons to use database.yml. Even if the reasons aren't that good in your opinion, it's really up to them to decide what approach they want to take, and it's not really helpful to tell them they should do something differently than they specify in the question. – iconoclast Aug 06 '13 at 20:09

Ruby script connect to Mysql2 using database.yml without Rails

3 Answers3