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
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
- manage schema versions: you can use your
DBIx::Class::Schemato 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::SQLfor 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).
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.
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
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.