Perl - database access - DBD, DBI and DBIx modules

Archive - Originally posted on "The Horse's Mouth" - 2010-12-22 02:58:21 - Graham Ellis

Perl's DBI module provides an excellent link from SQL databases to Perl, and the assorted DBD modules that fit underneath it allow it to operate well for a whole lot of different databases. But they are only excellent as far as they go - for the SQL statements have to be generated in your application code, rather than keeping the database instructions clear of your code. Enter the DBIx module, which provides an interface from Perl language variables to database tables. Using DBIx (which calls DBI then DBD internally), you can extract the SQL from the application and generate it, encapsulated, within the DBIx classes.

The diagram shows - in the top line - how you need to code both your application and your SQL to application translation if you do not use DBIx. In contrast, the bottom line shows how you only need to code your application if you use DBIx.

I've started with a "Control" example - showing direct access to an SQLite database through DBI and DBD - source code [here] (and there's an older version using MySQL [here]). You'll note if you look at this code that it's quite short, but contains all sorts of SQL statements.

My second example uses DBIx. Look at this:

  my $schema = D2::Main->connect('dbi:SQLite:d2_test.sqlite');

  my @teamadditions = ( ["Tom", 5], ["Jerry", 6]);

  $schema->populate('People', [ [qw/name pid/], @teamadditions ]);

Those three lines of application code ... which contain no SQL ... add two rows to an SQL table using DBIx. The source code (with two use statements to complete it!) may be found [here].

The "magic" of DBIx is in one of those uses - that pulls in separate datatype definitions:

  package D2::Main;

  use base qw/DBIx::Class::Schema/;

  __PACKAGE__->load_namespaces;

  1;

Which in turn pull in the table definition:

  package D2::Main::Result::People;

  use base qw/DBIx::Class::Core/;

  __PACKAGE__->table('people');

  __PACKAGE__->add_columns(qw/ pid name /);

  __PACKAGE__->set_primary_key('pid');

  1;

(You can download these two files [here] and [here])

The final file for this application - which sets up the database and some initial test data - is [here].

You'll find sample outputs from the programs, and details of how to run it, in the first file - and it's a straightforward and complete DBIx example; I'll go so far as to say it's the simplest I've seen around as the examples in the official documentation show off a lot more facilities - but set the hurdle far higher for you when you first want to try DBIx.

I have allowed myself to add further complexity (generating selects with joined tables, sorting my output, etc) in my third example. Once you've tried the example above, have a look at this set of files:

SQL to set up the tables - [here].
The main application, showing two examples of data extraction - [here]. INCLUDES SAMPLE RUN and DOCUMENTATION
The model loader - [here].
Defining a table of computers (and how it relates to a people table) - [here].
Defining a table of people (and how it relates to a computers table) - [here].

Once you get the hand of DBIx, it's a nice way to help keep the model layer in its own compartment, though you still need to know SQL subjects such as database normalization to come up with good designs. We're covering Perl to Database interfaces on this week's Perl for Larger Projects course, and we also cover it briefly on the more introductory Perl Programming Course. The DBD and DBI modules are usable by people who are quite new to Perl; some of the DBIx data structures, though, are rather ambitious for the first week - easy to use once you know them, but perhaps not too easy to learn.

Main Content

Perl - database access - DBD, DBI and DBIx modules