Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Tuesday June 03, 2008
04:28 AM

Are Object-Relational Mappers Worth It?

[ #36575 ]

I know this has been a long-standing area of debate and has been discussed to death, but I just have to get this out of my system since it's bugging the heck out of me.

We use DBIx::Class on our project. It's a great ORM. For my last job, I chose Rose::DB because I needed the performance. I've recently heard that they're ripping it out and replacing it with DBIx::Class -- for performance reasons. Allegedly all of the hacking I had to do to get classes to share database handles (it doesn't by default), support transactions and force all attributes to be private by default slowed it down to the point where it offered no speed advantage. I never saw the benchmarks, but I put a lot of work into ensuring that Rose fit our company standards and now other programmers are putting a lot of work into replacing it. So that's lots of work on things which are only indirectly productive.

I should add that while I have been generally positive about ORMs in the past, I keep seeing the difference between theory and reality. Most projects I've worked on which have heavy ORM usage keep fighting against the ORM, but few question whether or not it's the ORM itself which is the issue. When thinking about ORMs, the end goals we should focus on are:

  1. Improving maintainability.
  2. Improving performance.

For many projects, I think the ORM inhibits achieving either of those goals.

Problems:

  • The object-relational impedance mismatch is a well-known problem. Regardless of what hoops we jump through, this mismatch will continue to exist.
  • Objects, as agents of responsibility, should be correct, complete, but minimal (see this Dr. Dobbs article. It has some issues, but highlights the problem). However, ORMs typically try to do more than just provide an OO wrapper for the database, they also try to be a domain layer, thus being far from minimal and raising complexity. This also results in a lot of duplication of responsibilities (oftimes, the same business constraints are in the database, the ORM layer and in the calling code. Sheesh.)
  • On Web sites, how many times have you seen a page with relatively simple data needs still issue quite a number of queries to the database because of how ORMs work? We've a reporting system where one simple table generates over 300 separate database queries. In looking at the code (which I confess to writing), there are extremely complicated if/else bits of logic that are there because the domain knowledge is split between the application and the database. Other programmers looking at this code haven't figured a clean way out, but this not uncommon for ORMs.
  • Typically, for complex queries, I see developers write the SQL and laboriously translate it to the ORM code. Regrettably, this translation often involves breaking out multiple queries because while what we often want back is data for reporting, the ORM frequently wants us to assemble a bunch of objects and then extract the data we need -- a useless and expensive intermediate step.

The problem, of course, is how to deal with these issues. One common strategy is to push the domain logic into the database. This is very powerful because it leverages the database's strength of data management and makes it hard for a developer to forget about business rules. Unfortunately, MySQL severely limits our ability to do that. For example, we cannot define custom types, check constraints are not available and triggers are extremely limited.

Another strategy is to develop a solid domain layer which truly represents our business processes. Underneath can be a variety of strategies such as SQL dictionaries, limited ORMs with direct database calls or anything else we think of. The key is to have that domain layer which insulates us from the implementation and gives us the freedom to focus on business needs rather than picayune details of how to write a left outer join with a subselect in our ORM of choice.

The question, at its core, is whether or not ORMs offer the maximum return on investment. While the answer isn't the same for everyone, I get tired of watching projects scale upwards only to discover programmers constantly working around ORM issues.

A large part of the problem, however, may not directly be the ORMs. It may be the affordances they provide. For example, Mason is a great template system. It's very rich. It's very powerful. Every company I've worked with which uses it pushes far too much business logic into their templates. This is because it's a simple and natural thing to do with Mason. Template Toolkit, on the other hand, starts with Perl and passes data to the templates. While you can push too much logic to the template layer, it's less natural and, from what I've seen, less common.

ORMs may be the same. While extremely powerful, they may make it so easy for developers to operate in a certain way that they fail to consider alternatives.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • I'm glad to see smart people coming around to these realisations. The simple fact of the matter is that DBI makes ORMs in perl not as valuable as they are in other languages. It's a very feature rich database layer that is very capable of providing you with results in an ORM-like fashion.

    Now if hiding SQL is your thing, then fair enough - but I would suggest you (readers of this blog, not Ovid) learn SQL and embrace it, much like you learned regexps and embraced them too. And use good practices (e.g. MVC) t
    • If I ever got mod points here (and I haven't once since signing up) you'd definitely get an Insightful for that comment.
  • I think you've summed up the issues perfectly. ORMs are extremely useful, powerful tools in the right hands. Unfortunately, not many (myself included) have worked out how to use them effectively.

    A typical use of DBIx::Class is as the Model in a Catalyst app. I've begun to think - as you point out - that it's too low level to be the model. The model is business logic, not data access. But I'm not sure DBIx::Class is designed with this in mind, and I don't think I could write that layer myself (and do a good
    • A typical use of DBIx::Class is as the Model in a Catalyst app. I've begun to think - as you point out - that it's too low level to be the model.

      This is a major problem with many implementations of MVC. It's not specific to DBIx::Class.

      • This is a major problem with many implementations of MVC. It's not specific to DBIx::Class.

        I wasn't trying to imply there is anything wrong with DBIx::Class specifically. I use it frequently, and am deeply grateful to those who contribute to it.

        And yes, the problem lies partly with the MVC frameworks, but also with the ORMs. And I don't pretend to have any idea how to solve it (although I'd be interested to know whether any other languages are doing a better job).

    • A typical use of DBIx::Class is as the Model in a Catalyst app. I've begun to think - as you point out - that it's too low level to be the model.

      i usually have Controller that calls the model, then calls the data store which is the DBIC part. the business logic lies in the model and i can swap the data store with other ORMs without touching anywhere else. i am pretty happy with this approach. there is a MVC discussion on DBIC mailing list that sums this up. ways to do stuff and why [gossamer-threads.com]

  • Thanks for the excellent post, Ovid.

    As a professional Perl programmer of 10 years, I have evaluated several ORMs but not adopted one on the grounds that the complexity they add is not worth the benefit.

    Instead, I have the learned the DBI API very well, and use it combination with a light-weight SQL generation tool, SQL::Interp. The combination has proven to be efficient to develop and debug, from small scripts to large applications.

    Perhaps there are times when an ORM would save our team some moments, but I
    • I find DBI a bit painful - its good and all, but leaves me diving into the manual too often. DBIx::Simple and SQL::Interp together hit the sweet spot for me. Dead simple query generation and result handling, but the DBI connection handle is just a function call away.
      --
      Every token is like an unnecessary stain on whitespace and idleness.
      • Agreed. DBIx::Simple should always be the first thing shown to people who've never done anything with a database in Perl before. In my case, I've never needed anything else.
  • The amount of repetitive, error-prone code saved by an ORM is well worth the cognitive and performance overhead, IME. But like any abstraction, ORMs have their limits, in both directions. Developers have to know when to put down the hammer and pick up a different tool because not everything is a nail. In the case of ORMs, that fact that everything looks so regular and friendly lulls developers into forgetting the real (and usually ugly) database lurking under the covers.

    Experience teaches you when "bac

  • Because writing code that generates code sucks. A good ORM will give you an API to compose queries. It’s the difference between eval and using closures, roughly.

    I use DBIx::Simple and SQL::Interp to make SQL generation suck less, but it fundamentally can’t be made to not suck at all. And you still need to some sort of OOD for the layer that encapsulates your model, unless you’re content with a random collection of 300 SQL query wrapper methods in a single class.

    There are two major wins

  • I and my colleagues have encountered all of the issues and some more with O/R-mapping and tools as you've described above.

    We still haven't found the sweet spot.

    Sometimes O/R-mapping is a big winner and a really productivity booster as well as a good abstraction. At other times it just blows up in your face, and a few well chosen SQL-statements with suitable interpolation does it all. Sometimes integrating the O/R-tool with the domain objects works better, at other times it works better when we treat it

  • Are you developing a fairly straightforward CRUD application? If so then an ORM does what you need it to do in a clean way and lets you get on with your life. What's not to like?

    Are you trying to abstract away your database? Stop right there. I'd lay odds that you're making a mistake of some sort.

    Are you trying to do complex reporting? If so then an ORM is the wrong tool. If you're doing complex reporting, you want the full power of SQL. More than that, you want to have access to all of those special
  • The subject was discussed in 2006 on the Catalyst list : you may find interesting arguments there :
    http://lists.scsys.co.uk/pipermail/catalyst/2006-June/008059.html [scsys.co.uk]