RDBI - Low-level Database Access Re-imagined

RDBI is intended primarily as an alternative to the heavier database layers in the Ruby ecosystem. It provides a consistent interface to databases for working with query languages directly, instead of providing an extremely high level interface which does this work for you. While usable, it largely targets high-level database libraries, similar to how rack targets web frameworks.

I’d like to get started

If you’d prefer to head straight to sinking your teeth into the API, here’s a path down the rabbit hole:

RDBI is what you’ll use to get your RDBI::Database handle. If you need collections of database handles, look at RDBI::Pool.
RDBI::Database contains methods for dealing with database level operations and preparing and executing RDBI::Statement objects.
RDBI::Statement works with RDBI::Cursor to yield RDBI::Result objects, which leverage RDBI::Result::Driver classes to yield data.
If you’re interested in how schemas and types are dealt with, see RDBI::Schema, RDBI::Column, and RDBI::Type.

Give me a code sample already!

# connect to an in-memory sqlite3 database:
dbh = RDBI.connect(:SQLite3, :database => ":memory:")

# execute this CREATE TABLE statement:
dbh.execute("create table foo (bar integer, baz varchar)")

# prepare an insert statement for execution with two placeholders:
dbh.prepare("insert into foo (bar, baz) values (?, ?)") do |sth|

  # and execute it with bound variables:
  sth.execute(1, "foo")
  sth.execute(2, "bar")
  sth.execute(3, "quux")
end

# get a result handle from a select statement:
result = dbh.execute("select * from foo")

# and fetch the first row
result.fetch(:first) # [1, "foo"]

What `is` RDBI all about, anyway?

Here are some important pieces of information about RDBI that you may find compelling (or off-putting. We’re pragmatists.):

RDBI is, at the time of this writing, fewer than 1000 lines of code.
RDBI is light and fast. Eventually we will show you benchmarks.
RDBI can be tested without a database, or even a database driver.
RDBI is almost 100% thread-safe.
RDBI can transform your results through a driver system. Want a CSV? Use the CSV driver, don’t bother transforming it yourself. Transform to JSON or YAML with another gem. Drivers can be independently installed, used and swapped.
RDBI contains no monkeypatching, core extensions, or other hell that will conflict with your other libraries.
RDBI is designed around properties of a relational database, but there is nothing in it that demands one – use it with Mongo or Redis if you want.
RDBI database drivers are small – our sqlite driver is about 150 lines and our PostgreSQL driver is about 300. Our mock driver is about 50.
RDBI has an active community of experienced Rails and Ruby programmers.

I’d like some more, please.

# result objects are very flexible and amenable to method chaining:
dbh.execute("select * from foo").fetch(2) # [[1, "foo"], [2, "bar"]]

result = dbh.execute("select * from foo")

# select iteratively, then rewind to the first item:
result.fetch(2)
result.rewind

# change the way the results are presented:
result.as(:CSV).fetch(2) # '1,"foo"\n2,"bar"\n'

# :CSV is shorthand for RDBI::Result::Driver::CSV. you can also use literal
# class names:
result.as(RDBI::Result::Driver::CSV)

# or maybe your own:
result.as(MyCoolDriver)

# Here's another included driver:
str = result.as(:Struct).fetch(:first)
str.bar # 1
str.baz # "foo"

result.rewind

# select a single item in CSV format
csv = result.fetch(:first, :CSV)

# get the whole thing as an array of structs, keyed by column
ary_of_struct = result.as(:Struct).fetch(:all)

# as() automagically rewinds for you, so select twice for multi-dimension
# presentations:
ary = result.as(:Array).fetch(:all)

# and we're done! Disconnect from the database.
dbh.disconnect

Here are some things that it does:

Connection pooling with aggregate transforms of your connections (that’s a fancy way of saying it uses Enumerable in the Pools). It can be responsible for n segmented pools which relate to different logical databases.
Native client binding and interpolated binding for databases that do not support it.
Don’t like our drivers? No one’s requiring you to use them – RDBI drivers aren’t coupled with RDBI in any way.
Result drivers can be used to transform your output into whatever you need – never write a transformation skeleton again.
Result handles can be used to work with results like real data structures. Rewind them, ask the database to re-query the data, select a struct then select an array (without requerying), select n items at a time as tuples (which may be more than one or less than all).
Cursors are used underneath the hood to ensure as performant a situation as your database (and underlying driver) can provide.
RDBI’s core test suite passes in MRI 1.8, 1.9, and JRuby 1.5.

Aaaaaand here are some things RDBI won’t do:

RDBI won’t write your queries for you. (There are libraries that use RDBI for that.)
RDBI won’t dictate your schema.
RDBI won’t prevent you from being stupid or clever.
It won’t save you tons of time because you can’t be bothered to think about how you access your data.
It won’t make you (or anyone, really) a rockstar.
Do not taunt RDBI.

Show me even more awesome!

# retrieve cached handles 5 times -- handles will be yielded twice if there
# is a smaller Pool size: 
5.times do
  RDBI.connect_cached(:SQLite3, :database => ":memory:")
end

# omg! this handle is really already connected!
dbh = RDBI.connect_cached(:SQLite3, :database => ":memory:")

# finer-grained control via RDBI::Pool:
# 2 connections:
pool = RDBI::Pool.new("my_pool_name", [:SQLite3, :database => ":memory:"], 2)

# zomg!
dbh = pool.get_dbh

# oh lordy lord! still 2 connections
10.times { pool.get_dbh }

pool.disconnect # disconnect the entire pool
pool.reconnect  # reconnect the entire pool

pool.resize(10) # resize the pool to 10 connections.

Who is responsible for this madness?

Erik Hollensbe (erikh)
Pistos (… Pistos)
Lee Jarvis (injekt)
James Tucker (raggi)

I found a bug!

We use the trackers in the github RDBI project: github.com/RDBI for each gem. Please find the appropriate place to add your ticket.

Not sure? Just add it to the rdbi tracker: github.com/RDBI/rdbi/issues

I’d like to patch and/or help maintain RDBI. How can I?

Fork the project: github.com/RDBI
Make your feature addition or bug fix.
Please add tests for it, or indicate there are none. Patches without tests will get integrated slower and must be very compelling.
We use jeweler for our repository management – patches that mess with this will be rejected regardless of merit.
If you fork it permanently, be prepared to support it; we won’t.

Let’s chat

#ruby-dbi on irc.freenode.net
[email protected] - for developers