DBIx::DataModel - Elegant Database Interaction with Perl

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.

8 Comments

Thanks for this article. I like the approach of DBIDM that you describe. One of the things that keeps preventing me from using DBIC (and thus so far I use raw DBI/SQL) is the apparent complexity of it all. Perhaps using DBIDM is simpler and more convenient.

One thing I would like to see is some benchmarks, against DBIC/etc and also against raw DBI/SQL.

Just my 2c: there is a massive rewrite of a part of DBIC being public-trialed right now, which among other things addresses the benchmark you cite.

Numbers before: http://paste.debian.net/plain/234081
Numbers after: http://paste.debian.net/plain/234080

The entire announcement: http://lists.scsys.co.uk/pipermail/dbix-class/2013-February/011109.html

One of the things that keeps preventing me from using DBIC (and thus so far I use raw DBI/SQL) is the apparent complexity of it all.

I hear this sentiment a lot, but any time I reach out to ask for a clarification - I don't get anything back. It is frustrating :) I would *so much* appreciate a comment, or an email, or a blogpost that would point out complexities that *feel* unjustified. A criticism like this would not go unnoticed - either the points made are going to be explained away, or acknowledged and eventually fixed.

As you can see from the recent performance-enhancing work - the project is being developed and is making changes. It is however incredibly hard to deliver changes "the people want", without these same people speaking up. So please consider gisting-up a wishlist or something ;)

Hi Ross,

Thanks for this very kind article on DBIDM; I'm delighted to find out that you are so happy with it.

One additional point that you did not mention (and was actually one of the main reason why I wrote DBIDM) is that joins are treated quite differently from other ORMs. A join is not decomposed into individual tables; it's an object in it's own right, that _inherits_ from its component tables. I'm using this all the time for several big, mission-critical applications.

Thanks again, and I'm looking forward to reading your next blogs !

@Peter: I think the complexity is just the whole conceptual framework that comes with DBIC, in particular the whole resultset and chaining bit.

This isn't like other Perl ORMs and it's not like SQL either. I think that it's like C#'s Linq but I'm not aware of many other tools that do this (and it doesn't have Linq's quite nice syntax).

Then add in the absolutely *horrid* API provided by SQL::Abstract for where clause construction and it's even more to learn.

Note that despite my hate for SQL::Abstract I like the rest of DBIC. I'd love to see DBIC::Linq or something so I didn't have to deal with SQL::Abstract's API.

@autarch

Please consider tracking the progress and hopefully participating in this new development: http://dbix-class.35028.n2.nabble.com/quot-the-apparent-complexity-of-it-all-quot-td7578005.html

As far as the "horrid SQLA API" - eventually DBIC will accept other horrid APIs, including that of Fey. Ideally it will happen transparently, so that resultset constructors will understand all of the dialects. Work on this is very very very alpha at the moment, and there isn't much to show. I just wanted to mention that it is being considered.

@autarch : I agree that the way to express "where" clauses in SQL::Abstract is horrid; but this comes mostly from the fact that it uses positional parameters instead of named parameters (so you get all those empty hashrefs or arrayrefs and never know which is which).

This is why I wrote SQL::Abstract::More, used in DBIDM, which improves on several points of that API, in particular for "where" clauses.

Probably SQL::Abstract::More could be very useful to DBIC users too; but unfortunately DBIC offers no way to replace SQLA by a subclass of it...

@Peter: maybe if you just offered that possibility, that would already be a cheap way of improving DBIC API.

Leave a comment

About Ross Attrill

user-pic Keeping it simple with Perl