While on the one hand, ORMs make up most of the published best practices for interfacing with databases, (see the success of DBIx::Class, and many other similar products), on the other hand, there is a certain amount of backlash against them, eg ORM is an Antipattern.

I would add two further critiques of ORMs. First, most people use ORMs as their model layer (every Catalyst project I’ve seen does this, for instance), rather then implementing their model layer using the ORM as simply the database access layer. Using the ORM as your model layer violates abstraction— it ties your business logic intimately to the structure of your data. It makes you think of your data in terms of nouns defined by how the database itself is laid out. Instead, you should be thinking in terms of verbs— what do you want to do, not what do you want to do it to. That is, ask me how many users are online, not how many rows in the user table have the online column set to 1.

Second, the vast majority of ORMs require you to write your migration scripts by hand. You have to define, using the ORMs mini-language, what’s changed between two versions of your application. In fact, the only major exception to this that I’m aware of is DBIx::Class.

However, even given the downsides, they do have advantages. To me, they provide two main advantages over writing a model that uses SQL directly:

First, they solve the migration problem. You say “please update my database” and it goes out and reads your classes and updates your database to match them.

Second, they provide easy CRUD. You get easy user friendly methods for doing basic data manipulation.

You may notice that I didn’t include cross-database support. While this seems really nice at first, in my experience most projects will only ever run on one database. The main exception to this in the Perl world is running the test suite against SQLite. The model I suggest doesn’t quite support that, but in theory could be extended to allow for it.

While I do think the arguments against ORMs are often compelling, what they don’t do is provide alternative solutions to the problems that ORMs do solve well. So what I want to do here, is propose an alternative to the ORM model of database access.

Examples below are in MySQL’s dialect of SQL.

Migration and the Schema

You declare your schema as a series of SQL files, each containing CREATE statements. To migrate, you use a tool that parses the SQL and updates your database, much like class based migration tools would.

Example Schema1:

CREATE TABLE person (
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(40)
);

Example Schema2:

CREATE TABLE person (
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(40) NOT NULL,
   addr VARCHAR(200)
);

Migrating from Schema1 to Schema2 would run:

ALTER TABLE person
--  BEFORE name VARCHAR(40)
    MODIFY name VARCHAR(40) NOT NULL,
    ADD addr VARCHAR(200)

Migration is done either by comparing your schema directly with a database, or by comparing two different versions of schema. You either feed it directly to the database with a tool, or send the SQL to a file for vetting.

CRUD

A tool reads the SQL for table definitions and generates a series of stored procedures for basic data manipulation. This can be rerun if you want to regenerate these procs after changing the table.

Another tool reads any stored procedure definitions and generates a class for calling them. The procs have extended metadata to allow the helpers to return data rather then statement handles… eg an array of rows, a single value, a single row, a hash with key and value taken from two columns, etc.

The first tool, on Schema2 would create something like (along with other methods to delete and fetch rows):

CREATE PROCEDURE create_person(
    p_name VARCHAR(40),
    p_addr VARCHAR(200) )
RETURNS COLUMN id -- Note, special metadata here
MODIFIES SQL DATA
BEGIN
    INSERT INTO person ( name, addr ) VALUES ( p_name, p_addr );
    SELECT last_insert_id() AS id;
END

CREATE PROCEDURE update_person( 
    p_id BIGINT, 
    p_name VARCHAR(40), 
    p_addr VARCHAR(200) )
ARGS AS HASH -- More special metadata
MODIFIES SQL DATA
BEGIN
    UPDATE person SET name=COALESCE(p_name,name) addr=COALESCE(p_addr,addr) WHERE id=p_id;
END

And the second tool would create something like:

package MyProject::DB::Person;
use Any::Moose;
has dbh => (is=>'rw', isa=>'DBI::db', required=>1);
sub create {
    my $self = shift;
    my( $p_name, $p_addr ) = @_;
    my $sth = $self->dbh->prepare( "CALL create_person(?,?)" );
    $sth->execute( $p_name, $p_addr );
    $row = $sth->fetchrow_hashref;
    return $row->{'id'};
}
sub update {
    my $self = shift;
    my( $args ) = @_;
    my $sth = $self->dbh->prepare( "CALL update_person(?,?,?)" );
    $sth->execute( $args->{'id'}, $args->{'name'}, $args->{'addr'} );
}

From your software

You would then use the helpers to call these stored procs. As your application becomes more complicated you would likely want to write your own procs and subclass the generated helper class to make the helpers smarter. Again, in the same model as you would use with, for instance, DBIx::Class.

The example below assumes further helpers to fetch a record and delete a record.

my $person = MyProject::DB::Person->new(dbh=>DBI->connect(...));
my $pid = $person->create( "Alice", "123 Chestnut St" );
my $alice = $db->get($pid);
$alice->{"addr"} = "456 Fun Ave";
$person->update( $alice );
$person->delete( $alice->{'id'} );

I’ve used Moose regularly now for a couple years, but really only so far as most people do, as a handy accessor generator and type checker. But my most recent project has called on me to delve, first into Roles, which are a lovely way to teach old classes new tricks, but I found I wanted to do deeper magic…

Now, as I wrote about before, my project is a Node.js style event system for Moose. Moose classes have two types of things associated with them, attributes and methods.

I allow users to declare that their class will emit a particular event through a helper method. You just write:

has_event 'connected';

And now users can register a listener to be called when you emit the ‘connected’ event. Conceptually, these events are really a third type of thing associated with the class— they should inherit the way methods and attributes, but otherwise have no further meta data associated with them. They either exist or they don’t. So at first I thought perhaps metaclass traits would be helpful. But they don’t inherit, which is a key feature I need. If you subclass an event bearing class, your class is going to emit the same events it does (and maybe more).

Finally what I settled on was having the helper add specially named methods to the class. If the method exists, then the event exists and we emit it as usual. Otherwise we don’t. My has_event helper now looks like this:

my $stub = sub {};
sub has_event {
    my $class = caller();
    $class->meta->add_method( "event:$_" => $stub ) for @_;
}

With that settled, I had to export my helper, which was easy enough to do with Mo[ou]se::Exporter. This also allows me to reduce the amount of boiler plate in my class. Now, since my helper needs meta data, I would use with_meta and grab it from my @_, except that Mouse::Exporter doesn’t support with_meta =/. And so I do it the above way instead.

Also in the vein of reducing boiler plate, if you have to “use” my package to get my helpers, I didn’t want you to also have to “with” my Role. My impression had been that the base_class_roles argument to setup_import_methods would specify roles that should be added to the class that used my package, but this doesn’t seem to work for me. As such, I started with my init_meta calling $role_class->meta->apply( $importer->meta ) by hand. This, however, made it impossible for people to alias or exclude methods from my Role. So finally I created my own import that slurped in any -alias or -exclude arguments and passed them on to apply, sending the rest to the default import. Less elegant then I would like, but it does get the job done.

As a result, you can call:

package My::Class {
    use MooseX::Event -alias=>{ on => 'listen_on' };
    has_event 'foo';
    sub on { # Just an example of using a method with the same name as a method
                  # from the event role
       my $self = shift;
       $self->emit('foo');
    }
}
my $example = My::Class->new();
$example->listen_on( foo => sub { say "Hi" } );
$example->on(); # prints "Hi"