Question

This is a Ruby programming tutorial for the MySQL database. It covers the basics of MySQL programming with Ruby. It uses the mysql module. The examples were created and tested on Ubuntu Linux.

There is a similar MySQL C API tutorial , MySQL Visual Basic tutorial , or MySQL Python tutorial on ZetCode.

You may also consider to look at the MySQL tutorial , too.

MySQL & Ruby

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is on part of the very popular LAMP platform consisting of Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. Ruby is a dynamic, reflective, general-purpose object-oriented programming language. Recently it became very popular in web programming, mainly due to the successful Ruby on Rails framework.

mysql module

The mysql module is a Ruby interface to the MySQL server. It provides the same functions for Ruby programs that the MySQL C API provides for C programs.

$ sudo gem1.9 install mysql

Here we install the Ruby module for the MySQL database.

Before we start

We are going to create a new database user and a new database. To do this, we use the mysql client program.

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.67-0ubuntu6 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> SHOW DATABASES;
+--------------------+
| Database       |
+--------------------+
| information_schema | 
| mysql        | 
+--------------------+
2 rows in set (0.00 sec)

We connect to the MySQL server using the root account. We show all available databases with the SHOW DATABASES statement.

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.02 sec)

We create a new mydb database. We will use this database throughout the tutorial.

mysql> CREATE USER user12@localhost IDENTIFIED BY '34klq*';
Query OK, 0 rows affected (0.00 sec)
 
mysql> USE mydb;
Database changed
 
mysql> GRANT ALL ON mydb.* to user12@localhost;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit;
Bye

We create a new database user. We grant all privileges to this user for all tables of the mydb database.

MySQL server version

In the first example, we will get the version of the MySQL database.

#!/usr/bin/ruby

require 'mysql'


begin
  con = Mysql.new 'localhost', 'user12', '34klq*'
  puts con.get_server_info
  rs = con.query 'SELECT VERSION()'
  puts rs.fetch_row  
  
rescue Mysql::Error => e
  puts e.errno
  puts e.error
  
ensure
  con.close if con
end

In this script, we get the server version. We do it in two different ways.

require 'mysql'

We import the mysql module. The module has the classes and methods to work with the MySQL database.

con = Mysql.new 'localhost', 'user12', '34klq*'

We create the connection object. The parameters include the host name, user name and password. In our case the host name is localhost, e.g. our computer.

puts con.get_server_info

The Mysql object that we have created has a get_server_info method. It returns the version of the MySQL server installed.

rs = con.query 'SELECT VERSION()'
puts rs.fetch_row  

Another way to get the version is to execute the SELECT VERSION() SQL statement. We fetch the data. Since we retrieve only one record, we call the fetch_row method.

rescue Mysql::Error => e
  puts e.errno
  puts e.error

We check for errors. This is important, since working with databases is error prone.

ensure
  con.close if con
end

In the end, we release the resources.

$ ./version.rb
5.5.9
5.5.9

The output might look like the above.

Listing databases

The MySQL Ruby module has a list_dbs method, which returns available databases.

#!/usr/bin/ruby

require 'mysql'

begin
  con = Mysql.new 'localhost', 'user12', '34klq*'

  con.list_dbs.each do |db|
    puts db
  end
  
rescue Mysql::Error => e
  puts e.errno
  puts e.error
  
ensure
  con.close if con
end

In this script, we print all available databases on our MySQL server.

con.list_dbs.each do |db|
  puts db
end

The list_dbs method returns an array of available database names. Using the each method of the array, we print each item of the array to the console.

$ ./listdb.rb
information_schema
mydb
test
world

On my system, I had the above databases created.

Creating and populating a table

We create a table and populate it with some data.

#!/usr/bin/ruby

require 'mysql'

begin
  
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
   
  con.query("CREATE TABLE IF NOT EXISTS \
    Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
  con.query("INSERT INTO Writers(Name) VALUES('Jack London')")
  con.query("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
  con.query("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
  con.query("INSERT INTO Writers(Name) VALUES('Emile Zola')")
  con.query("INSERT INTO Writers(Name) VALUES('Truman Capote')")   
  
rescue Mysql::Error => e
  puts e.errno
  puts e.error
  
ensure
  con.close if con
end

We create a Writers table and add five authors to it.

con.query("CREATE TABLE IF NOT EXISTS \
    Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")

To execute an SQL statement, we use the query method. This SQL statement creates a new database table called Writers. It has two columns. Id and Name.

con.query("INSERT INTO Writers(Name) VALUES('Jack London')")
con.query("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
...

We use the INSERT statement to insert authors to the table. Here we add two rows.

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name        |
+----+-------------------+
|  1 | Jack London     |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola    |
|  5 | Truman Capote   |
+----+-------------------+
5 rows in set (0.00 sec)

After executing the script, we use the mysql client tool to select all data from the Writers table.

Retrieving data

Now that we have inserted some data into the database, we can retrieve it back.

#!/usr/bin/ruby

require "mysql"

begin
  
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

  rs = con.query("SELECT * FROM Writers")
  n_rows = rs.num_rows
  
  puts "There are #{n_rows} rows in the result set"
  
  n_rows.times do
    puts rs.fetch_row.join("\s")
  end
 
rescue Mysql::Error => e
  puts e.errno
  puts e.error
  
ensure
  con.close if con
end

In this example, we retrieve all data from the Writers table.

con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

The last parameter of the constructor is the database name, to which we connect.

rs = con.query("SELECT * FROM Writers")

This SQL statement selects all data from the Writers table.

n_rows = rs.num_rows

We get the number of rows in the result set using the num_rows method of the result set object.

n_rows.times do
  puts rs.fetch_row.join("\s")
end

Here we fetch each row with the fetch_row method. It returns a row as an array of fields. By default, the fields are separated by new line, when printed. With the join method, we print each row on one line. The fields are separated by one space.

$ ./retrieve1.rb
There are 5 rows in the result set
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

Next we present another way to retrieve data from the table.

#!/usr/bin/ruby

require "mysql"

begin
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

  rs = con.query("SELECT * FROM Writers")
  
  rs.each do |row|
    puts row.join("\s")
  end
    
rescue Mysql::Error => e
  puts e.errno
  puts e.error
  
ensure
  con.close if con
end

We print all data from the Writers table. This time we use an each method of the result set to traverse the data.

rs.each do |row|
  puts row.join("\s")
end

We iterate through the result set using the each method.

$ ./retrieve2.rb
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

This is the output of the example.

We can traverse data in form of a Ruby hash.

#!/usr/bin/ruby

require 'mysql'

begin
  
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'  
  rs = con.query "SELECT * FROM Writers WHERE Id IN (1, 2, 3)"
  puts "We have #{rs.num_rows} row(s)"
  
  rs.each_hash do |row|
     puts row['Id'] + " " + row['Name']
  end    
  
rescue Mysql::Error => e
  puts e  
ensure
  con.close if con
end

In the example, we use the each_hash iterator. Records from the result set can be retrieved by their column names.

rs.each_hash do |row|
  puts row['Id'] + " " + row['Name']
end   

We go through the result set with the each_hash method. Each returned row is a Ruby hash; a collection of key-value pairs. The keys are the column names.

$ ./retrieve3.rb
We have 3 row(s)
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger

Ouput of the example.

Multiple statements

MySQL supports multiple statement execution. This must be enabled by a special option.

#!/usr/bin/ruby

require 'mysql'


begin
  
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
  con.set_server_option Mysql::OPTION_MULTI_STATEMENTS_ON

  rs = con.query "SELECT Name FROM Writers WHERE Id=1;
          SELECT Name FROM Writers WHERE Id=2;
          SELECT Name FROM Writers WHERE Id=3"

  puts rs.fetch_row    
      
  while con.next_result
    rs = con.store_result  
    puts rs.fetch_row
  end     
     
rescue Mysql::Error => e
  puts e.errno
  puts e.error
  
ensure
  con.close if con
end

In this example, we have three SELECT statements in one query.

con.set_server_option Mysql::OPTION_MULTI_STATEMENTS_ON

First we need to enable the multiple statements processing with the Mysql::OPTION_MULTI_STATEMENTS_ON .

rs = con.query "SELECT Name FROM Writers WHERE Id=1;
        SELECT Name FROM Writers WHERE Id=2;
        SELECT Name FROM Writers WHERE Id=3"

Here we define three SELECT statements. They are separated by a semicolon.

puts rs.fetch_row 

The query method returns the first result set. We fetch a row from this result set.

while con.next_result
  rs = con.store_result  
  puts rs.fetch_row
end   

We get additional result sets until there are no more statements left to process.

$ ./multiplest.rb
Jack London
Honore de Balzac
Lion Feuchtwanger

Running the example.

Metadata

Metadata is information about the data in the database. Metadata in a MySQL system contains information about the tables and columns, in which we store data. Number of rows affected by an SQL statement is a metadata. Number of rows and columns returned in a result set belong to metadata as well.

#!/usr/bin/ruby

require 'mysql'

begin
  
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'  
  rs = con.query "SELECT * FROM Writers WHERE Id IN (1, 2, 3)"
  puts "We have #{con.field_count} fields"
  puts "We have #{rs.num_rows} row(s)"
  puts rs.fetch_row.join("\s")
  
rescue Mysql::Error => e
  puts e  
ensure
  con.close if con
end

In this script, we find out the number of rows and columns from an SQL query.

rs = con.query "SELECT * FROM Writers WHERE Id IN (1, 2, 3)"

This SQL statement returns three rows. Each row has two columns.

puts "We have #{con.field_count} fields"
puts "We have #{rs.num_rows} row(s)"

These two lines return the number of columns and rows in the result set. Note that here a field is a synonym for a column. The returned data is metadata.

puts rs.fetch_row.join("\s")

Here we return one row from the result set. This is the original data stored in our database table.

For INSERT , DELETE and UPDATE statements there is a method called rows_affected . This method returns the number of rows affected by these three statements.

#!/usr/bin/ruby

require 'mysql'

begin
  
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'  
  con.query "DELETE FROM Writers WHERE Id IN (1, 2, 3)"
  
  puts "The query has affected #{con.affected_rows} rows"
  
rescue Mysql::Error => e
  puts e  
ensure
  con.close if con
end

In our example, we delete first three rows from the Writers table.

con.query "DELETE FROM Writers WHERE Id IN (1, 2, 3)"

An SQL statement, which deletes first three rows of the Writers table.

puts "The query has affected #{con.affected_rows} rows"

Here we get the number of rows that were affected by the above SQL statement. This number belongs to the metadata.

$ ./affected.rb
The query has affected 3 rows

mysql> SELECT * FROM Writers;
+----+---------------+
| Id | Name      |
+----+---------------+
|  4 | Emile Zola  |
|  5 | Truman Capote |
+----+---------------+
2 rows in set (0.00 sec)

We execute the affected.rb script and check for changes in the Writers table. Three rows have been deleted.

In the next example, we are going to check for metadata about a field.

#!/usr/bin/ruby

require 'mysql'


begin
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
  rs = con.query "SELECT * FROM Writers WHERE Id=1"
  field = rs.fetch_field_direct 1
  
  puts "Table name: #{field.table}"
  puts "Field name: #{field.name}"
  puts "Field length: #{field.length}"
  puts "Field type: #{field.type}"
  
rescue Mysql::Error => e
  puts e
  
ensure
  con.close if con
end

We get one record from the database. We get the field's table name, colum name, length and type.

rs = con.query "SELECT * FROM Writers WHERE Id=1"

This query returns one row. It has two columns.

field = rs.fetch_field_direct 1

Using the fetch_field_direct method, we get a specific record. More precisely, the record from the intersection of the first row, second column.

puts "Table name: #{field.table}"
puts "Field name: #{field.name}"
puts "Field length: #{field.length}"
puts "Field type: #{field.type}"

We get the metadata using attribute readers of the field object.

$ ./metadata.rb
Table name: Writers
Field name: Name
Field length: 25
Field type: 253

This is the output of the example.

In our last example relating to the metadata, we will print all rows from the table with their column names.

#!/usr/bin/ruby

require 'mysql'

begin
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
  rs = con.query "SELECT * FROM Writers"
  
  fields = rs.fetch_fields  
  puts "%3s %s" % [fields[0].name, fields[1].name]
  
  rs.each_hash do |row|
    puts "%3s %s" % [row['Id'], row['Name']]
  end
  
rescue Mysql::Error => e
  puts e
  
ensure
  con.close if con
end

We print the contents of the Writers table to the console. Now, we include the names of the columns too.

fields = rs.fetch_fields  
puts "%3s %s" % [fields[0].name, fields[1].name]

In the first step, we get the column names. They are printed using the standard Ruby string formatting abilities.

rs.each_hash do |row|
  puts "%3s %s" % [row['Id'], row['Name']]
end

Now the data is fechted and printed to the console. We do some formatting too.

$ ./columnheaders.rb
 Id Name
  1 Jack London
  2 Honore de Balzac
  3 Lion Feuchtwanger
  4 Emile Zola
  5 Truman Capote

Ouput of the script.

Prepared statements

Now we will concern ourselves with prepared statements. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance.

#!/usr/bin/ruby

require 'mysql'

name = "Stefan Zweig"

begin
  
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'  
  pst = con.prepare "INSERT INTO Writers(Name) VALUES(?)"
  pst.execute name
    
rescue Mysql::Error => e
  puts e  
  
ensure
  con.close if con
  pst.close if pst
end

In the above example, we insert a new row into the Writers table. We use a prepared statement.

pst = con.prepare "INSERT INTO Writers(Name) VALUES(?)"

The prepare method is used to create a prepared statement. The ? character is a placeholder. Later we bind a value to this placeholder.

pst.execute name

We bind a value from the name variable to the placeholder and execute the prepared statement.

pst.close if pst

The prepared statement is closed.

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name        |
+----+-------------------+
|  1 | Jack London     |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola    |
|  5 | Truman Capote   |
|  6 | Stefan Zweig    |
+----+-------------------+
6 rows in set (0.00 sec)

After the script was successfully run, we see a new author in the Writers table.

Writing images

Some people prefer to put their images into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with lots of images. Images are binary data. MySQL database has a special data type to store binary data called BLOB (Binary Large Object).

mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)

For this example, we create a new table called Images .

#!/usr/bin/ruby

require 'mysql'

begin
  
  fin = File.open("woman.jpg" , "rb")
  img = fin.read
  
rescue SystemCallError => e    
  puts e
ensure
  fin.close if fin 
end


begin
  
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'  
  pst = con.prepare("INSERT INTO Images SET Data='%s'" % img.unpack('H*'))
  pst.execute  
  
rescue Mysql::Error => e
  puts e
  
ensure
  con.close if con
  pst.close if pst
end

In the above script, we read a JPG image and insert it into the Images table.

fin = File.open("woman.jpg" , "rb")
img = fin.read

We open and read an image. The read method returns the data as string.

pst = con.prepare("INSERT INTO Images SET Data='%s'" % img.unpack('H*'))

This string data is placed into the prepared statement. Before doing so, it is decoded using the unpack method of the Ruby string object. Decoding is necessary, because the image object has many special characters which cannot be processed normally.

Reading images

In the previous example, we have inserted an image into the database table. Now we are going to read the image back from the table.

#!/usr/bin/ruby

require 'mysql'

begin
  
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'      
  rs = con.query "SELECT Data FROM Images LIMIT 1"  
  f = File.new "woman2.jpg", "wb"
  f.write rs.fetch_row.pack 'H*'

rescue Mysql::Error, SystemCallError => e
  puts e
  
ensure
  con.close if con
  f.close if f
end

We read one image from the Images table.

rs = con.query "SELECT Data FROM Images LIMIT 1" 

We select one record from the table.

f = File.new "woman2.jpg", "wb"

We create a writable binary file.

f.write rs.fetch_row.pack 'H*'

We fetch the data from the previous SQL statement and write it to the file. The fetch_row method returns an array object. Before the data is written to the file, it is put back into the original format with the pack method of the array. For both operations, decoding and reversing, we use the same directive, 'H*' . It stands for hex string.

Now we should have an image called woman2.jpg in our current directory. We can check if it is the same image that we have inserted into the table.

Transaction support

A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.

By default, MySQL runs in the autocommit mode. In this mode, all changes to the tables are immediately effective. To prevent this, we have to turn off the autocommit mode. After disabling autocommit, changes to transaction-aware tables are not permanent immediately. To store the changes, we must call the COMMIT statement or ROLLBACK to revert them. The Ruby MySQL has convenience methods for these SQL statements, commit and rollback .

The MySQL database has different types of storage engines. The most common are the MyISAM and the InnoDB engines. There is a trade-off between data security and database speed. The MyISAM tables are faster to process and they do not support transactions. The commit and rollback methods are not implemented. They do nothing. On the other hand, the InnoDB tables are more safe against the data loss. They support transactions. They are slower to process.

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES 
  -> where TABLE_SCHEMA = 'mydb' AND TABLE_NAME='Writers';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| Writers  | InnoDB |
+------------+--------+
1 row in set (0.00 sec)

The engine of the Writers table is InnoDB, which supports transactions.

#!/usr/bin/ruby

require 'mysql'

begin
  con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

  con.autocommit false
  
  pst = con.prepare "UPDATE Writers SET Name = ? WHERE Id = ?"
  pst.execute "Leo Tolstoy", "1"
  pst.execute "Boris Pasternak", "2"
  pst.execute "Leonid Leonov"

  con.commit
  
rescue Mysql::Error => e
  puts e
  con.rollback
  
ensure
  pst.close if pst
  con.close if con
end

In this script, we try to update three rows.

con.autocommit false

The autocommit mode is disabled.

pst = con.prepare "UPDATE Writers SET Name = ? WHERE Id = ?"
pst.execute "Leo Tolstoy", "1"
pst.execute "Boris Pasternak", "2"
pst.execute "Leonid Leonov"

We execute three UPDATE statements. The last one is incorrect. The second parameter is missing.

con.commit 

If all is OK, changes are committed to the table.

rescue Mysql::Error => e
  puts e
  con.rollback

In case of an error, changes are rolled back.

$ ./update.rb
execute: param_count(2) != number of argument(1)

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name        |
+----+-------------------+
|  1 | Jack London     |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola    |
|  5 | Truman Capote   |
|  6 | Stefan Zweig    |
+----+-------------------+
6 rows in set (0.00 sec)

Running the script gives an error. However, the transaction was rolled back and the first two rows were not changed.

This was MySQL Ruby tutorial. You may be also interested in PostgreSQL Ruby tutorial , SQLite Ruby tutorial , or MongoDB Ruby tutorial .