Connecting To MySQL using Ruby

So this time we will take a break off Rails, and go straight into the actually using Ruby alone. No abstraction.

Ruby has a simple abstraction layer (Ruby DBI), but lets skip that for now. We don’t want abstraction.

Installing MySQL

Well first you need MySQL installed. I have written a guide on how to get it installed, and some of the default security issues. Its on a page about using MySQL with Rails, but the installation is pretty much independent.

There are two ways to install MySQL in Ubuntu Feisty Fawn. The straight forward way is to install via the apt-get package, the second, the traditional RubyGems way.

Installing MySQL using apt-get

This shouldn’t be so tough.

sudo apt-get install libmysql-ruby[/sourcecodel]

<b>Nah.</b> Not tough at all.

<h3>Installing MySQL using RubyGems</h3>
First lets ensure that our Ruby development library, and the MySQL client libraries are installed.
[sourcecode language="plain" gutter="false"]sudo apt-get install ruby-1.8-dev libmysqlclient15-dev build-essentials

So let’s pull down the MySQL libraries via RubyGems

sudo gem install mysql

Note: If you see the following, choose for your platform. In this case I’d select number 3 for ruby.

Select which gem to install for your platform (i486-linux)

 1. mysql 2.7.3 (mswin32)

 2. mysql 2.7.1 (mswin32)

 3. mysql 2.7 (ruby)

 4. mysql 2.6 (ruby)

 5. Skip this gem

 6. Cancel installation

>

Documentation

There is documentation for the MySQL library online . So you can follow that.

Getting started with MySQL

Now if you ever want to use MySQL in your ruby application. You can insert the following at the top, and your good to go.

require 'rubygems' # Only if installed via RubyGems
require 'mysql'

So let’s make a connection to MySQL.

db = Mysql.new('localhost', 'user', 'password', 'database')

In fact, if it fails to make a connection, it will raise a Mysql::Error.

Mysql::Error: Access denied for user ‘root’@'localhost’ (using password: YES)

So if you really want to play safe, you can wrap a exception handling block around it.

begin
  db = Mysql.new('localhost', 'user', 'password', 'database')
rescue Mysql::Error
  puts "Oh noes! We could not connect to our database. -_-;;"
  exit 1
end

Or you could just let the raised exception halt the program entirely. But that’s not cool.

Simple Queries

So let’s begin with some simple queries!

begin
  results = db.query "SELECT * FROM users"
  puts "Number of users #{results.num_rows}"
  results.each_hash do |row|
    puts "User #{row.id}: #{row.name}"
  end
  results.free
ensure
  db.close
end

For starters, I wrap my code in a exception handling block, to ensure that no matter what happens in the code. The database connection is closed.

The returned results if of the class Mysql::Result, so there are various methods to use in how the results are interpreted. Check the documentation and scroll down to Mysql::Result. If you are familiar with other MySQL connectors, this should be a breeze.

In this instance, I used the each_hash method, so that each row is treated as a hash. (If you come from the PHP world, this is similar to mysql_fetch_assoc)
The each_hash method is an iterator, and in the style of Ruby iterators, makes it very easy to iterate over an return result set.

Prepared Statements

The Mysql Ruby connector also supports prepared statements.

begin
  insert_new_user = db.prepare "INSERT INTO users (name, age, gender) VALUES (?, ? ,?)"
  insert_new_user.execute 'aizatto', '20', 'male'

  insert_new_user.close

  statement = db.prepare "SELECT * FROM users WHERE name = ?"
  statement.execute 'aizatto'
  statement.fetch
  statement.close
ensure
  db.close
end

Conclusion

There you have it, a quick run down of using Mysql with Ruby. Hopefully it should get you started.