DBIx::DataModel (DBIDM) is a light and powerful framework that simplifies interactions with relational databases. DBIDM makes simple database interactions easy and complex interactions possible - all using Perl.
In February I described three key advantages of DBIDM and in this article I will describe three more being:
- a small but discerning list of CPAN dependencies
- more efficient than raw SQL for joins
- a design philosophy that 'less is more' - bringing out the strengths of Perl and DBI
I will also argue that DBIDM shares some of the design philosophies and values with other exciting new Perl modules such as Dancer and Mojolicious.
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), using some of the same infrastructure such as SQL::Abstract.
A fundamental benefit of using DBIDM is that it allows definition of database interactions to be defined in Perl rather than SQL. I will demonstrate below why this can be a good thing. DBIDM also provides facilities for easily managing the results of SELECT statements.
You can find out more about DBIDM from the excellent documentation included with the module - see https://metacpan.org/module/DBIx::DataModel. For what it is worth my previous article also sketches a very simple usage scenario for DBIDM combined with the Dancer web application server.
DBIDM has been designed and is maintained by Laurent Dami.
In my previous article I described three key advantages of DBIDM:
- results are hashes;
- schema generation is fast, clean and effective; and
- the API allows efficient definition of INSERTs, UPDATEs and SELECTs in Perl.
In this article I will describe three more advantages of DBIDM being: 4. a small but discerning list of CPAN dependencies; 5. more efficient than raw SQL for joins; and 6. a design philosophy that 'less is more' as evidenced by the default approach to column mapping.
Two common themes that emerge are that DBIDM:
- is lightweight and yet powerful; and
- has been designed to take full advantage of the beautiful language and ecosystem that is Perl. In this regard, I believe that DBIDM shares similar design values and build qualities with other exciting modules that have emerged on CPAN in recent years such as Dancer and Mojolicious and therefore that DBIDM may fill a niche for Perl ORMs in a similar way that Dancer and Mojolicious have added value to the set of Perl web frameworks.
- A Small but Discerning Set of CPAN Dependencies
We have seen in recent years a trend in modules to use a only a small and discerning list of non-core CPAN dependencies. This is in evidence in many of the 'Tiny' namespace modules and is also true for the Mojolicious and Dancer web app frameworks.
The DBIDM dependency list can be viewed at https://metacpan.org/module/DBIx::DataModel. Note that many of the dependencies are core modules. The remaining eight or so non-core dependencies are a select set of high quality modules such as the invaluable DBI (of course), Try::Tiny and SQL::Abstract::More. SQL::Abstract::More (SQAM) is an extended version of SQL::Abstract that is provided with DBIDM. SQAM provides several benefits over SQL::Abstract including a more explicit mapping of the Perl based query API to the underlying SQL - making DBIDM code easier to read and write.
There are many advantages to keeping the dependency list small that don't need pointing out. But as somebody who mostly develops with Perl on Windows I will say that I am always relieved when a module has only a few non-core dependencies. Once the non-core dependency list grows above ten, the chances of a clean install on Windows diminish rapidly.
Many Perl developers value a manageable CPAN dependency list and DBIDM delivers in this regard.
- More Efficient than Raw SQL For Join Syntax
Dave Cross provides an excellent blog post pointing out that 'SQL is boring'. I feel that one of the reasons that SQL is boring - and tedious - is that it violates the 'Dont Repeat Yourself' principle. A clear example of this is the ANSI JOIN statement. If I have tables EMPLOYEE and DEPARTMENT with a foreign key joining EMPLOYEE.DEPARTMENT_ID back to DEPARTMENT.DEPARTMENT_ID and want to get joined results from both tables I have to write something like this:
SELECT E.LASTNAME, D.DEPT_NAME, E.D_BEGIN FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID WHERE E.D_BEGIN >= '2000-01-01'
The ON clauses is redundant. I will almost always be joining EMPLOYEE on DEPARTMENT in exactly the manner that I have already declared to the DBMS via the foreign key relationship.
I am aware that a 'NATURAL JOIN' syntax does exist for some platforms but this is based on column naming convention rather than foreign keys and is neither standard nor ubiquitous.
Fortunately DBIDM knows about my FK relationship via the schema file and allows me to do this:
my $lst = My::Schema->join(qw/Employee department/)
->select(-columns => [qw/lastname dept_name d_begin/],
-where => {d_begin => {'>=' => '2000-01-01'}});
Some observations:
* DBIDM does not require the redundant 'ON' statement
* DBIDM fixes a major annoyance with SQL lists where a trailing comma left after E.D_BEGIN will give a syntax error without a line number. By contrast Perl and DBIDM is more sensible (and powerful) with list syntax.
* DBIDM gives me an array of blessed hashes without further ado
* For those familiar with DBIC, you can see that DBIDM's use of SQAM makes it somewhat more explicit which parts of the Perl interface map to which parts of the generated SQL. SQAM uses named rather than positional parameters. SQAM has an explicit -where whereas SQL::Abstract relies on this being the first list element. I think the equivalent in DBIC / SQL::Abstract looks something like:
my $rs = $schema->resultset('Employee')->search(
{ 'd_begin' => {'>=' => '2000-01-01'}},
{ columns => [qw/lastname dept_name d_begin/]},
{ join => 'department' }
);
* On the downside, the DBIDM where clause syntax is my noisy and harder to remember than raw SQL.
This is not the only way to do joins in DBIDM. We can also expand columns using a syntax like this:
My::Schema::Activity->metadm->define_auto_expand(qw/employee department/);
In this way, if I query on Employee then my result will contain a 'department' key which - if examined - will contain a value being a hash ref of Department data for that Employee. This built in expansion capability of DBIDM is a very efficient way of obtaining a hierarchical tree structure for your data and is very useful when passing data to client tiers that have a rich domain model. Obtaining hierarchical data structures like this is not so easy with raw SQL and DBI.
- Less is More Design Philosophy
DBIDM is a mature (in its second major version) and well considered framework. It is clear that Laurent is highly discerning in design and is very careful to harness and expose the full capabilities of Perl and DBI through the framework. As such DBIDM can have less surface area in terms of its own API, but deliver more capability. The perfect example of this is the use of 'plain old hashes' as result sets which for simple things are very easy to use for any Perl developer. There is no need to remember framework methods or use accessors to get at result data. However for more complex things the hashes are blessed so that additional methods for data manipulation are available - such as expansion to related tables as described above. Laurent has a blog article that describes this philosophy much better than I can.
Another brilliant example of less being more with DBIDM is that the schema mapping file does not require mapping of every database column to an object attribute. This is in fact an outcome of the previous point about results being blessed hashes and also Perl's dynamic typing system. Most other ORMs that I have come across map every database column to an object attribute - which is laborious and requires keeping at hand a reference table to map the DBMS data types to the data types of the (statically typed) programming language. Again - this fails the 'Don't Repeat Yourself' principle. I already have a strongly typed database system - so repeating it in the ORM is a pain. On the other hand DBIDM knows about important columns - primary keys and foreign key columns - supporting joins and expansions. I can also optionally add column type information if I want to - such as declaring date type columns and providing handlers to support formatting data in transit to or from the database. But without all that un-necessary column mapping for plain old numeric or character fields I get a much smaller, cleaner and more manageable schema mapping file.
I feel that DBIDM is very much like Perl Dancer. Dancer helps me map http routes to plain old Perl subroutines and then manages sending the response back. In just the same way DBIDM helps me map database queries to plain old Perl hashes and will manage sending hashes back to the database for saving. Both do just enough to help me interface with the outside world (http or DBMS) and let me do the rest easily in plain old Perl.
In this article and my previous article I hope that I have started to make the point that DBIDM provides a very clean and efficient means of interacting with relational databases and in a manner congruous with the philosophy and implementation of Perl itself.
If you love the design philosophy of new modules like Dancer and are looking for something similar for interacting with your database then I would encourage your to look at DBIDM.
Alternatively if you are worn out from writing SQL by hand and would rather write Perl - but have been put off by the complexity of other ORMs - then DBIDM might be just what you need.
Either way I can highly recommend DBIDM as a very simple, elegant and 'Perlish' way of making database interaction really easy.
DBIDM is generously provided to the Perl community by Laurent Dami https://metacpan.org/author/DAMI.