Building notes, projects, and occasional rants

Perl and database access

The base of all database work with Perl is the DBI module. There is no possible argument about this.

If you need something more high-level, things get dicey.

For the last 2 years I've been using the most excellent DBIx::Class. From all the ORM's that I've used so far, it is the best one out there and I still recommend it if you want to get up and running fast (quick tip: start with DBIx::Class::Schema and the load_namespaces() API, do not use load_classes()).

DBIx::Class has some great features:

  • comprehensive test suite: self-explanatory;
  • extensible: you can add pretty complex things on top of DBIx::Class. You can override every method using the magical Class::C3 foundation;
  • manage schema versions: you can use your DBIx::Class::Schema to manage your SQL schema, including versioning and updates. Some manual tweaking is required, no out-of-the-box tool to do this for you, but that's expected anyway;
  • nested transactions: this is killer feature for me. I've worked on a system (IPGng::SQL for those who know what that is) in the past (2001/2003 timeframe) that has this also, and it makes your code much more robust and simple.

Not everything is a good fit for me, though. There are two main sticky points.

The first is the use of SQL::Abstract as the query representation language.

Learning a new language that I can only use in the context of Perl is a personal waste of time. I have been using SQL with Oracle, MySQL, Postgres and SQLite for quite some time, and I can pretty much do whatever I need with it. Even when I hit a wall, I have a couple of Celko books behind me that usually help me jump over them.

So for me, the SQL::Abstract advantages aren't actually.

The second sticky point is a mismatch between DBIx::Class and my needs in terms of Object-oriented modeling.

Usually I have one entity in my object model (say for example Members) that is mapped to multiple tables (personal data, login information).

With DBIx::Class, I have the business logic dispersed into Result and ResultSet classes, per table, not per entity. This sort of works, but I find it messy for more complex projects.

So for a new project that I'm starting, I'm not using DBIx::Class. I don't have a full replacement, just parts that I'm enjoying putting together.

The first two modules that will be part of the final solution are DBIx::Simple and SQL::Interp.

They provide the minimal set of tools that such lower layer over DBI should have.

Above that, I'm still working on it. So far I have a wish list:

  • transactions: nested transactions with commit/rollback hooks;
  • DSL for schema versions: must have data dictionary features.

The first part will probably be a small Transaction module. I'll most likely use code like the txn_do of DBIx::Class::Storage, I like the syntax very very much.

The commit/roolback hooks are required because I need a "almost" two-phase commit protocol.

Picture this: you have two systems, a transactional database and a non-transaction messaging/pubsub system. Inside a DB transaction, you publish some events. If those events reach subscribers before the DB transaction is committed, they will not find the DB up-to-date.

What I need is to delay the actually publishing of the events until the final commit of the DB.

The second wish is a DSL that I can use to generate the SQL, with tools to manage upgrades between versions. This will be a SQL::Translator-based project, with a Parser class for my DSL.

This allows me to use all the Producers that the SQL::Translator project already has, and also the tools to diff SQL schemas.

The data dictionary part is the important feature. I want to declare types, including Perl code to validate and format values, in a central place, and then use them in the schema.

This DSL must generate also some base set of classes that you can extend with custom behaviors. This also provides a introspection interface that can be used to generate HTML forms and validation profiles.

Its seems a lot of work, and it is. But its also an incremental process going back some years, and I don't mind the wait.