Perlish – DBIx::Simple

Posted by Joe Topjian on April 18, 2011 under Development | Be the First to Comment

Introduction

Over the years, I’ve spent a lot of time with Perl DBI. It’s a great interface for working with databases, but sometimes I find it a little too heavy. A few months ago I found DBIx::Simple and now all is right with the world.

Table of Contents

• DBIx::Simple
  • Return a Single Value
  • Return Multiple Rows from One Column
  • Create a Hash Indexed by a Column
• Conclusion

DBIx::Simple

To start using DBIx::Simple, create a DBI object in a similar manner to normal DBI.

#!/usr/bin/env perl
use strict;
use warnings;
use DBIx::Simple;

my $dbh = DBIx::Simple->connect( 'DBI:mysql:database=foo', 'user', 'password' ) or die DBIx::Simple->error;

Now comes the fun stuff. With DBI, I use a familiar four-step routine:

• Prepare
• Execute
• Fetch
• Finish

While clean and methodical, these four steps can become a pain to use over and over again.

With DBIx::Simple, it can be as easy as:

    for my $row ($db->query('select username, email from users')->hashes) {
        print "Username: $row->{username}, Email: $row->{email}\n";
    }

Here, everything is rolled into one step.

note: You don’t have to combine the Query and Fetch steps — they can be split. I personally like to for the conciseness.

Query results can be returned in a number of different ways. I’ll explain a few ways, but all can be found in DBIx::Simple‘s perldoc or examples doc.

Return a Single Value

If you know that your result will only have a single value, you can do:

my $sum = $dbh->query('select 2 + 2')->list;

Return Multiple Rows from One Column

Building on the previous example:

my @sums = $dbh->query('select 2 + 2 union select 3 + 3')->flat;

Create a Hash Indexed by a Column

This example creates a hash of users with a Key based on the first column and Value based on the second:

my %users = $dbh->query('select id, username from users')->map;

Conclusion

DBIx::Simple is one of my favorite Perl modules. If you frequently use DBI, I would encourage you to take a look at it.