Terrence Brannon


DBIx::Cookbook - executable cookbook code for DBI(-based ORMs)?


   dbic_cmd distinct_count
   dbic_cmd grouping


DBIx::Cookbook is a working cookbook of code for DBI-based ORMs. It is based on the Sakila database schema (http://dev.mysql.com/doc/sakila/en/sakila.html). Currently, the majority of code is for DBIx::Class but all ORMs are encouraged to contribute code so that comparisons can be made between various ORMs for the same task.

If you only want to read the code samples, then simply see "RECIPES". If you want to run them on actual data, the see "INSTALLATION". And for those that want to contribute more code, see "CONTRIBUTING".


To get a feel for the Sakila database, refer to the structure section of the MySQL Sakila website. This section has a graphical database schema which is fairly useful when any complex joins are done in any of the examples.

Another option is to look at the DBIx::Class Result classes, since they have good pod documentation. Starting with DBIx::Cookbook::DBIC::Sakila::Result::Actor is a good idea.


The recipes section gives you a good overview of each ORM's capabilities with fully-working code that you can actually run!


R is the most common CRUD operation. DBIx::Cookbook::Recipe::Searching will show you common search tasks.


Installation of DBIx::Cookbook is simple (thanks to #perl-help on irc.perl.org):

 perl Makefile.pl
 make install

The sakila database is installed automatically

Just FYI, there is a sakila target that runs automatically when you run make. This target downloads the sakila database and loads it into MySQL. It also stores the MySQL auth credentials in lib/DBI/Cookbook/DBH.pm.

If you ever want to run it separately simply type

  make sakila

Run the examples

For DBIx::Class, you would execute scripts/dbic_cmd:

  dbic_cmd complex_where
  dbic_cmd paged
  dbic_cmd subquery

Invoke dbic_cmd without arguments to see all possible queries to run:

  metaperl@locohost:~/prg/DBIx-Cookbook$ ./scripts/dbic_cmd 
  Available commands:

             commands: list the application's commands
                 help: display a command's help screen

        complex_where: (unknown)
           custom_sql: (unknown)
              db_func: (unknown)
       distinct_count: (unknown)
            fetch_all: (unknown)
          fetch_first: (unknown)
         fetch_single: (unknown)
           get_column: (unknown)
             grouping: (unknown)
                paged: (unknown)
    predefined_search: (unknown)
     specific_columns: (unknown)
              sql_lhs: (unknown)
          stored_proc: (unknown)
             subquery: (unknown)
  subquery_correlated: (unknown)

skinny_cmd and rdbo_cmd currently implement fetch_all but no other examples.


It is highly desired to have code from as many ORMs as possible. In this section we will review the steps to install another ORM into DBIx::Cookbook.


Let's see how I added Rose::DB::Object to the Cookbook.

Make the ORM-database connection

The file lib/DBIx/Cookbook/RDBO/RoseDB.pm was used to form a connection to the database to Rose::DB::Object via the Rose::DB protocol.

Create a command class

DBIx::Cookbook uses MooseX::App::Cmd to separate the mechanics of scripting from command development. Let's copy lib/DBIx/Cookbook/DBIC.pm to lib/DBIx/Cookbook/RDBO.pm and modify it.

Write a ::Loader script

scripts/rdbo_loader was used to scan the entire database for entities and relations and store them in Perl classes

As a sidenote, I had to write a custom base class instead of using the autogenerated base class so that the as_tree method in Rose::DB::Object::Helpers was available to all row instances.

Write a command script

DBIx::Cookbook::RDBO::Command::fetch_all is the command that I implemented after writing the base command class and creating Rose::DB::Object classes for the database schema

Write a command

This time we copy lib/DBIx/Cookbook/DBIC/Command/fetch_all.pm to lib/DBIx/Cookbook/DBIC/Command/fetch_all.pm and modify it to load in the Rose::DB::Object schema and fetch the results of the table.


A similar set of steps was followed for DBIx::Skinny. I had some problems getting it setup

but the Skinny crew was very helpful both via the google group and IRC channel (as was Siracusa for RDBO).

The hardest part was again making the ORM-database connection. The file lib/DBIx/Cookbook/Skinny/Sakila.pm is how the connection was made.


Terrence Brannon, <metaperl at gmail.com>



Many thanks to ribasushi for help with Module::Install and my MANIFEST.SKIP


Yoshimi Keiji, Atsushi Kobayashi, Ryo Mikake


John Siracusa


Possum (Daniel LeWarne) - massive help on the etc/install-sakila.pl script!

Matthew Trout:

   [10:24] <mst> metaperl: I tend to use ExtUtils::MakeMaker::prompt
   for simple stuff 
   [10:24] <mst> metaperl: see Devel::REPL for using Term::ReadLine
   for more complex things 
   [10:35] <metaperl> Before releasing DBIx::Cookboox to CPAN, how to
   eliminate the need for config.bashrcwhich is being used through
   various .pm files via use lib "$ENV{DBIX_COOK}/etc/etc/" 
   [10:37] <mst> er. why not use FindBin?


Writing all the docs for this would have been much harder to review and debug without John Beppu's Pod::Server.


bingos helped out quite a bit with Module::Install in this thread and this thread

The DBI crowd

Jonathan Leffler detected many issues with my general CPAN release architecting, such as hardcoded paths, missing directories, etc.

Support, source code, etc

irc://irc.perl.org/#dbix-class - I can be found as 'metaperl' here

You can find documentation for this module with the perldoc command.

    perldoc DBIx::Cookbook

Source Code Repo


Known bug in search.cpan.org

On perlmonks I've reported an issue where search.cpan.org renders some links using .tt files instead of .pod files.


Copyright Terrence Brannon.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.