DBIx::Simple, a kinder and gentler way to use DBI

| No Comments

I wrote something about SQL::Abstract in this recent article, so I will now show the features of a small module which works perfectly well with the above mentioned one, but also without it: DBIx::Simple. This package provides a nicer interface to DBI, and allows to use SQL::Abstract to build queries (but does not require it).

DBI works just fine, but it sometimes gets boring to write the code which uses it: most of time you have to prepare_cached your query and then execute it; to retrieve the data you have to use fetchall_arrayref or one of the similar long-named methods, often passing them the needed parameters. DBIx::Simple makes all of this... simpler. For example, if you need to fetch some rows into an hashref you just write:

use DBIx::Simple;

my $db = DBIx::Simple->new(
    'dbi:mysql:database=mydb;host=myhost', 'myuser', 'mypass'

my $res = $db->query(
    'select id, username, address from users where role = ?',

my $rows = $res->hashes;

So, it's similar to the plain DBI version, but it's cleaner. To initialize the DBIx::Simple object you can pass the DSN to the constructor (as in this case), or a DBI object if you already happen to have one - which is actually quite common in a situation where you are migrating an application to DBIx::Simple and you have parts of the code which still use DBI.
$db->query is an unified method which allows you to perform any type of query, returning a result object, which is roughly the equivalent of DBI's statement handler. It's however easier to deal with, and provides some useful extra methods as well. Here goes some usage examples:

my $row = $res->hash;        # reference to the hash of a single row
my $row = $res->array;       # reference to the array of a single row
my $rows = $res->hashes;     # reference to an array with all the rows (as hashes)
my $rows = $res->arrays;     # reference to an array with all the rows (as arrays)

Any method can also be called in list context, so that it returns an hash or array instead of the reference to it. For instance:

my %row = $res->hash;        # hash (list context)

Now suppose you need to get all the rows (as in our example) as hashes, but you'd like to have them in an hash the keys of which are the contents of one of the columns. Just do this:

my $rows = $res->map_hashes('username');
print join '-', $rows->{'username'};

While it's true that using the map function will lead you to the same results, having a ready-to-use method helps you to keep you code clear. There are other methods for mapping, have a look at the documentation of the module to know them all.

As I wrote before, DBIx::Simple also integrates nicely with SQL::Abstract. The query of our example would therefore become:

my $res = $db->select(
    [qw/ id username address /],
    { role => 'customer' }

A SQL::Abstract object will be automatically created the first time you call one of the methods which use it (you need to have the module installed, of course). You can also provide an object of yours, which is recommended as you'll be able to use SQL::Abstract::Limit:

use DBIx::Simple;
use SQL::Abstract::Limit;

my $db = DBIx::Simple->new(
    'dbi:mysql:database=mydb;host=myhost', 'myuser', 'mypass'

$db->abstract = SQL::Abstract::Limit->new(
    limit_dialect => $db->dbh

There's also a $res->text method which is very useful for debugging, as it outputs the result of a query in a variety of formats.

All in all, DBIx::Simple provides no additional features to DBI, but it's like sugar spread on it (it should really have been named DBI::Sweet). It's usage will make you code far more readable, and quicker to write.

Leave a comment

About this Entry

This page contains a single entry by Michele Beltrame published on August 7, 2006 5:34 PM.

Good bye, Syd! was the previous entry in this blog.

Pic de Coma Pedrosa is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.



OpenID accepted here Learn more about OpenID
Powered by Movable Type 5.14-en