Search this site:

Database abstraction, ORMs and RDBMS-agnostic coding

I came across John Wang’s posting titled Database Abstraction - code vs infrastructure. In it, he talks about the problems many people face when migrating applications tied to a specific RDBMS to another one. He recommends:

One solution of modern programming is to move database abstraction from the code to the infrastructure using a ORM (Object-Relational Mapper) or Data Mapper. A ORM and Data Mapper abstracts the database for you so you no longer have to do tie db abstraction to each app. Not only does it let you code once for multiple databases it lets your users migrate their data from one database to another. This blog runs Typo which is based on Ruby on Rails and ActiveRecord. I've been contemplating migrating Typo from MySQL to PostgreSQL and I've been told that it would be as simple as exporting the data with YAML, updating the database.yml file and importing the data.

Umh… Thing is, you often want to use your DB as way more than a data store - At least, when using Postgres, I do. There are many things that can be done inside the DB (such as guarding the programmer against his own mistakes by keeping sanity checks which often go beyond just referential integrity. Constraints and triggers. Oh, and of course, user-defined functions and views. Of course, most times you can go by without using several of such tools - but when they would come in handy, you can sorely miss them.
On the other hand, John mentions (right after the previous paragraph):<blockquote>ActiveRecord is a data mapper and isn’t as flexible as a full blown ORM but it gets the job done for the most part. For a full-blown ORM, I think of Perl’s DBIx::Class which provides a full OO interface to the RDBMS allowing you to code just once for multiple DBs without limiting you when you want to use some esoteric database-specific SQL</blockquote>Currently, I’m among the many that have jumped on the Rails bandwagon - And so far, I really like it. But yes, ActiveRecord -although great for some tasks- is way under what I’d like for many actions I’ve got used to making.
I might be seen as a backwards guy for this, but until before I started with Rails, I prefered not to use full ORMs (not that I tried too hard anyway - Probably starting a project with DBIx::Class would be all it takes for me to become a convert, from what I’ve read), but to do the SQL myself - Only, of course, keeping well in mind that I should separate front-end from back-end (or using other jargon to say the same, separating cleanly what should be in a Controller and what should be in a Model - Of course, no one in their right mind would put SQL on a template or a View!). It’s not so hard as many people seem to think, although mixing syntaxes for two different languages (and even more if you, as I do, like to keep your code under 80 columns) is sometimes dirt-ugly. Still, on to my last bit of rant:<blockquote>There are PHP frameworks out there like Symfony and Cake but do any of them have stand-alone ORMs? If so, could Drupal move to something like that and solve their maintainership problems once and for all? Drupal is part of the Go PHP5 effort so there should be no issue using PHP 5 OO. Something to think about for the Drupal folks if a PHP ORM is available</blockquote> Umh… Even if there are some great ORMs in PHP… Choosing an ORM is something you do when you start a project. I doubt the Drupal people can now just decide to move over to using a ORM, as they potentially have hundreds or thousands of points where they have DB interaction. And although for most of them moving over to an ORM should even be automatizable, every ORM has some very special features which make it damn hard to craft several kinds of query.