DBI is a database api based on Perls’ DBI. It’s great for those occasions where you want to interact with a database in a script, or perhaps when you have a really lightweight app that doesn’t need an ORM framework.

Install

Ok, it took quite a bit of searching around but this is the magic recipe that will get everything you need installed on ubuntu.

Mysql on ubuntu

sudo apt-get install mysql-client
sudo apt-get install libmysqlclient15-dev

Gems

sudo gem install dbi
sudo gem install mysql
sudo gem install dbd-mysql

Usage

First of all let’s load up all the dependencies

require 'dbi'
require 'mysql'
require 'dbd-mysql'

Now let’s make a connection to a db, obviously replacing the schema, hostname etc. with your own.

dbh = DBI.connect('DBI:Mysql:schema:hostname', 'username', 'password')

For the examples let’s assume that we have a table, people, that contains

Select

DBI provides two select methods, select_all and select_one. Each will return rows or a row that contain values that can be indexed using the name of a column or the index of the column. DBI will map the values in your columns to ruby classes automatically.

row = dbh.select_one("SELECT * FROM people;")
puts row[:id]                 # 1
puts row[:id].class         # Fixnum
puts row[0]                   # 1
puts row[0].class           # Fixnum
puts row[:name]            # jim
puts row[:name].class    # String

rows = dbh.select_all(statement)
puts rows[0][:id]       # 1
puts rows[0][:name]  # jim
puts rows[1][:id]       # 2
puts rows[2][:name]  # paul

Insert

For operations that update the db you can use the method ‘do’. Note that we’ve used the ? to indicate where our values will go in the query and then supplied them as arguments at the end, this is to avoid SQL injection attacks.

dbh.do("INSERT INTO people (id, name) VALUES (?,?)", nil, 'bob')

Update

We can use ‘do’ for updates as well

dbh.do("UPDATE people SET name=? WHERE id=?", "mark", 3)

Delete

You get the idea…

So there you go, a whirl wind tour to get you up and running. For more in depth instructions I recommend this tutorial. The ruby DBI homepage has extra information including the rdocs. Finally, this github version of the project also has some good information in its’ readme, particularly regarding different db drivers.

Install mysql

apt-cache search mysql-client

Choose one and install (sudo apt-get install xxx)

Install dev headers

apt-cache search libmysql

Choose one that ends in dev and install

Install gem

sudo gem install mysql