DBIx::Perlish - getting rid of SQL from Perl code

This is a first installment of several posts about DBIx::Perlish Perl module.

Database handling in your program consists of queries and “everything else”.

DBI makes handling of the “everything else” part easy in Perl. It presents a unified interface to deal with a lot (although not all) idiosyncrasies of various numerous RDBMSes.

But mostly, when doing DB programming, you deal with queries. Which DBI does not help you much with.

Which are done using SQL.

SELECT * FROM users,groups
  WHERE
      users.groups_id = groups.id AND
      users.id = 42;

Which is a domain-specific language, from your point of view as a Perl programmer.

DSLs are all the rage nowadays, and I won’t dispute their advantages and usefulness.

But SQL is a very large DSL, as they go.

You are a smart programmer, so you learned SQL. You might even appreciate the fact that it is a declarative language, not unlike Prolog, which definitely adds a bit of excitement to the mundane task of writing your queries.

But every time you are writing Perl code that works with databases you need to constantly switch mental gears between Perl, the language you like (otherwise you would not be reading this), and SQL, the language very different from Perl. This is taxing. It costs you in lost productivity.

Even if you clearly separate in your code (and you should) those parts dealing with DB handling and those that are not, you still need to write those queries, and to switch languages back and forth.

At best, SQL in your Perl code severely disrupts the code flow by virtue of being a different language and thus looking very different from Perl.

my $r = $dbh->selectall_arrayref(
   "SELECT * FROM users, groups
    WHERE
      users.groups_id = groups.id AND
      users.id = ?",
   {Slice=>{}}, 42);

So you clearly have a problem that needs a solution.

One of the possible solutions is to use an object-relational mapper module such as Class::DBI, or DBIx::Class, or Jifty::DBI.

Such solutions have their merits. There are many arguments in favour of them. There are also arguments against them. I am not going to discuss them any further here, I would only like to point out that even if you use an object-relational mapper, you still cannot completely avoid writing SQL: sometimes because you need to construct a query that your mapper of choice does not support through its abstraction, and sometimes for efficiency reasons; the majority of the existing mappers do not deal well with collections, and provide you with shims to add your custom SQL code in strategic places.

Another solution, the solution which I am trying to sell you is to try to get away from using SQL as a domain-specific language in your Perl code altogether.

This involves creating yet another domain-specific language specifically for doing database queries with a (nice for Perl programmers) distinction that it actually looks very much like Perl. In fact, syntactically it is a valid Perl, altough semantically it is still a declarative language suitable for making relational database queries.

my $uid = 42;
my @r = db_fetch {
    my $u : users;

    $u->groups_id == groups->id;
    $u->id == $uid;
};

More on this in a future post.