Ross Attrill

DBIx::DataModel (DBIDM) is an Object Relational Mapper for Perl that is very elegant, simple and effective. It helps you to efficiently interact with your database.

This article discusses three of the great features of DBIDM and how I find they help me to develop software more efficiently. The three features discussed are:

• results are hashes - so it is very easy to manipulate and debug query results,


• schema generation is fast, clean and effective - so we very quickly get a useful map within Perl of just the most important aspects of our database, and


• the API allows inserts, updates and selects to be defined extremely efficiently - keeping the easy things easy

The article also provides a very brief introduction to DBIDM and some notes on getting started with it. There is also a rough example demonstrating how I combine DBIDM with Perl Dancer.

Introduction

DBIDM is an Object Relational Mapping module that provides a Perl API for database operations such as SELECT, INSERT, UPDATE and DELETE. DBIDM is similar to DBIx::Class (DBIC) and uses some of the same infrastructure such as SQL::Translator and SQL::Abstract.

DBIDM relies on a schema mapping file that defines the most essential information about your database including tables, primary keys and foreign key relationships between tables. This mapping file can be generated by hand or automatically by reverse engineering from your database.

Once you have your schema mapping place, DBIDM then provides a very efficient API to perform database selections, inserts, updates and deletion. Complicated retrievals can be defined using Perl data structures since DBIDM builds in SQL::Abstract - which is the same SQL Abstraction engine as used by the widely used DBIC ORM.

The net result of all of this is that database interactions can now be coded extremely efficiently from within Perl.

The Benefits of DBIDM

With Perl, there is usually more than one way to do things and database interaction is no exception. For example
* DBI provides a rock solid, highly mature and proven API for database interactions using raw SQL
* DBIx::Simple provides an improved API on top of DBI for SQL coded interactions
* DBIx::Class provides a widely used and respected API for dealing with databases at a higher level of abstraction using Perl objects and methods

All of the various approaches have different costs and benefits - but for me DBIx::DataModel provides a pure Perl based API to my database without adding excessive complexity and overhead.

I will now describe three of the benefits that I see with DBIx::DataModel in terms of interacting with databases with Perl.

1. Results are Hashes

One of the most significant benefits that I find with DBIDM is that the results of select are just blessed arrays of hashes.

So if I just want to select all the rows from my table $name, using my schema connection $schema then I just do this:

my $rows = $schema->table($name)->select();

My $rows is now a reference to an array of hashes which I can immediately push through Data::Dumper to visualize, or send to my serializer. In my context, I use DBIDM with Perl Dancer, so if I just return $rows at the end of my route handler, then Dancer automatically serializes my results as JSON. It is important to note that $rows is a blessed reference, with two implications:
a) DBIDM embeds additional functions that I can use with my results - such as an ability to automatically expand child records
b) If your serializer (JSON) can't cope with blessed references then you must use the DBIDM provided unbless method prior to serialization

The benefit of results being simple Perl data structures is that I can manipulate the data using ordinary Perl - and I don't have to remember or look up any additional module specific methods to slice and dice my results. This allows me to work more efficiently and makes my code simpler.

2. Schema Generation is Fast, Efficient and Small

When I am building new systems I design the database data-model first and then build my services layer on top of that. As such, I want to set up my ORM schema mapping by reverse engineering from my database.

DBIDM does schema reverse engineering very efficiently via DBIx::DataModel::Schema::Generator which builds on top of SQL::Translator.

The result is a very tidy and contained schema mapping file containing a mapping of tables to objects, information about table primary keys and relationships between tables based on database foreign keys. The schema file does not contain details of every database column / attribute nor any auto generated methods.

Because this schema mapping file is small and contained there are several benefits:
* I don't end up with masses of auto generated mapping files and code inflating my code base
* Everything I need to know about by database to schema mapping is easily found within one file
* Schema generation is really fast - normally completing in a few seconds (compared to minutes for DBIC)

I find the speed benefit particulary important during the early phases of project design when I am often adding and modifying tables very quickly.

3. An efficient API for Selects, Inserts and Updates

DBIDM provides a really efficient API for selects, inserts and updates. I find that in many cases, database interactions become a single line of code. This might be well demonstrated by example. The following indicative code combines a dancer route that receives a POST of employee details and a service based on DBIDM to save those details to the database.

package MyPackage::EmployeeRoutes;
use Dancer ':syntax';

use MyPackage::EmployeeService;

our $service = MyPackage::EmployeeService->new();

post '/employee' => sub {

    my $hr = {  first_name => param('first_name'), 

                last_name  => param('last_name')};

    my $id = $service->insert($hr);

    return $id;

};

package MyPackage::EmployeeService;

use Method::Signatures;

use MyPackage::Schema;    # The DBIDM Schema

our $schema = MyPackage::Schema->singleton();

our $dao = $schema->table('Employee');

method insert($hr) {

    my $id = $dao->insert($hr);

}

In summary, the Dancer route unpacks the post parameters into a hash reference and the service inserts these details with a single line of code. If I can be sure that the names of my post parameters match my database column names, this could be made even more efficient by getting the $hr from the Dancer 'params' method without explicitly specifying the post parameters.

The benefit to me as a developer is simple and efficient coding - labourious hand coded SQL INSERTs have been avoided.

Getting Started with DBIDM

DBIDM includes a very comprehensive set of quality documentation. This documentation includes a quick start guide
https://metacpan.org/module/DAMI/DBIx-DataModel-2.32/lib/DBIx/DataModel/Doc/Quickstart.pod which may be helpful - particularly if you are happy to generate your schema by hand. If you already have your database model in place and want to reverse engineer your DBIDM mapping from that, then make sure that you look at the Schema Generator. I recommend that you install Lingua::EN::Inflect - an optional dependency before using the schema generator - as it will ensure better quality plural names in the schema mapping file.

Summary

DBIDM is a fantastic piece of Perl software helping you interact with your database in a manner that is consistent with the Perl philosophy that easy things should be easy and hard things possible.

In this article I have described three of the key strengths of DBIDM being:

• results as simple arrays of hashes;


• fast and efficient schema generation; and


• an elegant API for database interactions.

These are only some of the strengths of DBIDM and in a future article I hope to describe some more.

I can definitely recommend DBIDM as a great piece of software for interacting with your database using Perl.

Acknowledgments

DBIDM is provided to ther Perl community by Laurent Dami. Laurent is actively improving and supporting DBIDM. I would like to thank Laurent for providing this excellent software.