Working with databases is something programmers, especially Web programmers, often need to do. Most (reputable) database backends provide a way to use Structured Query Language (SQL) queries to interact with the database. That's usually where the similarity ends. Working with MS SQL Server, Oracle, and MySQL databases typically means you must acquire connection libraries unique to a specific database backend to interact with a database with SQL.

Since we're talking about Perl, let's use PostgreSQL as an example. There is a CPAN module called Pg that gives you a set of subroutines for interacting directly with a PostgreSQL database backend.

Here is the example usage from the Pg POD:

use Pg;
my $conn = Pg::connectdb("dbname=template1");
my $res  = $conn->exec("SELECT * from pg_user");
while (@row = $res->fetchrow) {
    print join(" ", @row);
}

If you wrote a whole application using this Pg module and then someone came along and said, "Hey, I like your application, but we use MySQL," you'd probably plant your face into your palm pretty hard.

DBI

In the 1990s, Tim Bunce contributed the DBI module to CPAN. DBI is database abstraction layer meaning that it sits between your applications and any database backend and gives you (the programmer) a generic set of facilities for interacting with databases.

Examine at how the Pg example could be accomplished using DBI:

use DBI;
my $dbh = DBI->connect('dbi:Pg:dbname=template');
my $sth = $dbh->prepare('SELECT * FROM pg_user');
my $rv = $sth->execute;
while(my @row = $sth->fetchrow_array) {
    print join(' ', @row;
}

The first thing to notice here is the DBI->connect() line. In many cases, this line is the only one you would need to change to migrate a DBI application from one database backend to another. The first parameter passed to the connect() function is a DBI Data Source Name -- or DSN.

Some examples of DSNs that you may use with DBI:

• dbi:mysql:database=shoppinglist;host=db1
• dbi:Pg:dbname=bookshelf;host=192.168.1.22;port=5432
• dbi:SQLite:dbname=/var/db/addrbook.db
• dbi:CSV:f_dir=/home/joe/csvdb
• dbi:Oracle:host=oracle;sid=oracle

DBD modules

The interface from DBI to each specific database backend is provided by DBI Drivers or DBD modules. In addition to drivers for most common database backends, there are some unusual and unique drivers as well such as DBD::CSV which provides the means to use SQL queries to interact with data in comma-separated values text files.

Stop worrying about quoting

One thing that typically comes up when working with non-DBI database interaction methods is worrying about value quoting. SQL requires that column values be quoted with single-quote characters unless the value is a number. For example:

INSERT INTO TABLE friend (first_name, last_name, age) 
VALUES ('Joe', 'Smith', 15);

This is, of course, assuming the age column is an integer. Think about zip codes. The person who designed the schema for the database you're working with might have made the assumption that a zip code would always be a 5-digit number and therefore defined the zipcode as an integer type. Another person might have considered the possibility of zip+5 zip codes and defined the column as VARCHAR(10) and values would therefore need to be quoted inside single quotes.

Fortunately, if you use DBI properly, you won't have to worry about quoting because you can use placeholders and bind values in query strings. See the example below:

my $sth = $dbh->prepare('INSERT INTO friend (first_name, last_name, age) 
VALUES (?, ?, ?)');
my $rv = $sth->execute( $first_name, $last_name, $age);

The question marks in the prepare() call are placeholders and the parameters passed to execute() are the corresponding bind values.

One of the reasons DBI uses a prepare() call followed by an execute() call instead of one call to execute a query is so you can reuse a prepared query with multiple bind values. Notice this example which reads from a CSV file and populates a database table:

my $sth = $dbh->prepare('INSERT INTO friend (first_name, last_name, age) 
VALUES (?, ?, ?)');

while(<CSV>) {
    my ($first_name, $last_name, $age) = split /,/;
    my $rv = $sth->execute( $first_name, $last_name, $age);
}

Fetching data

In the first example above which showed how the Pg module interacted with a PostgreSQL database backend, the fetchrow() call returned an array of values in a row of results. This is fairly limited and by no means provides the result data in all the ways a programmer would to use it. For example, one glaringly absent piece of information is the field names.

DBI provides multiple calls for fetching result data. Below is an example of the fetchall_hashref() call which gives you access to all rows in a result as a referenced hash.

my $sth = $dbh->prepare('SELECT * FROM friends');
my $rv = $sth->execute;
my $friend_hash = $sth->fetchall_hashref('id');

For simple queries like this, it may makes sense to use DBI's selectall_hashref() call, which results in even fewer lines of code:

my $friend_hash = $dbh->selectall_hashref(
    'SELECT * FROM friends', 'id');

The resulting hashref, when dumped using Data::Dumper might look like this:

$VAR1 = {
      '1' => {
               'id' => 1,
               'age' => 15,
               'last_name' => 'Smith',
               'first_name' => 'Joe'
             },
      '3' => {
               'id' => 3,
               'age' => 33,
               'last_name' => 'Jansen',
               'first_name' => 'Stuart'
             },
      '2' => {
               'id' => 2,
               'age' => 25,
               'last_name' => 'Johnson',
               'first_name' => 'Roger'
             }
    };

In conclusion

This short article only scratches the surface on DBI, but it hopefully gives the reader an idea of the power and flexibility provided by this valuable CPAN module.

For those who are looking for more indepth information O'Reilly and Associates has published a fine book on DBI, co-written by Tim Bunce, Programming the Perl DBI which is highly recommended. And then there's always the DBI PODs: Type perldoc DBI after/if you've got DBI installed on your system.