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.
(written 2010-12-22, updated 2010-12-24)

 S200 - MySQL - SQLite
  [2561] The future of MySQL - (2010-01-03)
  [2744] PyQt (Python and Qt) and wxPython - GUI comparison - (2010-04-28)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2746] Model - View - Controller demo, Sqlite - Python 3 - Qt4 - (2010-04-29)
  [2786] Factory methods and SqLite in use in a Python teaching example - (2010-05-29)
  [3139] Steering our Python courses towards wxPython, SQLite and Django - (2011-01-19)
  [3269] Files or Databases? MySQL, SQLite, or Oracle? - (2011-04-28)
  [4006] Ruby / SQLite3 example program, showing JOIN v LEFT JOIN - (2013-02-16)
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)
  [4024] SQL databases from Python - an SQLite example - (2013-03-02)

S156 - Interfacing Applications to MySQL Databases
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)
  [644] Using a MySQL database from Perl - (2006-03-13)
  [663] Python to MySQL - (2006-03-31)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [1381] Using a MySQL database to control mod_rewrite via PHP - (2007-10-06)
  [1450] Easy selection of multiple SQL conditions from PHP - (2007-11-30)
  [1518] Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [2381] Checking the database connection manually - (2009-08-28)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [3447] Needle in a haystack - finding the web server overload - (2011-09-18)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [4436] Accessing a MySQL database from Python with mysql.connector - (2015-02-21)

P308 - Using SQL Databases from Perl
  [515] MySQL - an FAQ - (2005-12-03)
  [975] Answering ALL the delegate's Perl questions - (2006-12-09)
  [1224] Object Relation Mapping (ORM) - (2007-06-09)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)

Back to Learning Object Orientation in Perl through bananas and perhaps Moose

Previous and next or Horse's mouth home

Forward to Looking ahead and behind in Regular Expressions - double matching

Thank you - and Happy Christmas
AND and OR operators - what is the difference between logical and bitwise varieties?
The week before Christmas
Looking ahead and behind in Regular Expressions - double matching
Perl - database access - DBD, DBI and DBIx modules
Learning Object Orientation in Perl through bananas and perhaps Moose
Making Perl class definitions more conventional and shorter
Contrast in pictures
The Christmas Season has arrived
Setting your user_agent in PHP - telling back servers who you are