I don't like using ORMs or SQL builders like SQL::Abstract. They seem to do fine when the needs are simple, but there's no trick to making simple things simple. Once you need to do a three table join with a subselect, getting the incantation right for the abstraction layer is hard, but I already know how the SQL ought to look. The abstraction layer is demonstrably making my job harder.

(Same problem XML::Simple has; after an afternoon of trying to get the datastructure to come out the way I expect, I realize I would have been done already if I had used a DOM parser.)

Although getting rid of the "boring SQL bits" was one of the points made by the author of the article, I think the second one is the reason why I, personally and professionally, prefer using DBIx::Class over raw DBI queries:

With an ORM, what you get "back" from the database is not a plain data structure made of hashes and arrays, but you get back an OBJECT.

By virtue of having these objects (using DBIx::Class, things like ::ResultSet::Foo and/or ::Schema::Result::Bar) you have for free:

accessors and getters/setters which DWIM - when you need it - updates and updates on related tables

Sure, if your task at hand is something that is best done using raw SQL then do just that! ALTER TABLE, REPLACE INTO, whatever. There are places in which raw SQL is the best tool for the job.

But, IMHO, if you're writing a number of programs, web apps, scripts, you-name-it - all of which use the same databases/schemas, using an ORM will definitely aid you in keeping the business logic consistent throughout all of them: write it once (in the schema), use it everywhere.

Or, am I missing your point somehow? I'd love to know how the abstraction is making your job harder - and if I can provide some insights on how DBIx::Class could actually help.

And so on (specifying the fields out rather than using SELECT *, of course). You can think of these as database procedures that are implemented around the database rather than inside it.

If you need to support multiple RDBMS backends, then you can structure things as:

MyApp::DB
MyApp::DB::MySQL
MyApp::DB::Pg
MyApp::DB::Oracle

And so on, with each vender class being a subclass of MyApp::DB. The superclass can directly implement the methods that can be done with generic SQL, while subclasses take care of all vender-specific stuff as needed. MyApp::DB constructor can take a vender argument and return the right subclass (factory pattern, more or less).

You can stick an SQL generator like SQL::Abstract in there if you want, but I don't care for them.

This does tend to create a God Object anti-pattern, which is why I'm still not happy with this method. I just prefer it to most of the alternatives in complex database applications.

Not really no, I want stuff out of it, some of it will be attributes some of it will be methods that provide something else or do something else. If I wanted a simple data structure, in DBIC for instance I could specify I wanted one and that's very rare.

Object methods are for performing complex behavior underneath logical calls. When you spew accessors/mutators all over them, that complex behavior is allowed to leak into the rest of the application.

(Consolidating the separate conversation threads below.)

I've worked with codebases that were done your way, and that was my experience.

And I've worked on codebases done all the other ways, and this was my experience. For the record, of all these ways:

Ad hoc hand written

Hand written wrapped in a God Object

ORM

Abstract SQL builder

Database procedures for everything

I consider all of them terrible. The first is obviously the worst once you get larger than a single script, and the rest are used because we don't have a better idea yet. The last one means the vender turned SQL into a full programming language with procedure calls, and that extended language is invariably worse than whatever real programming language you're using on top. It also tends to need more careful communication between your DBAs and Programmers.

(If a DBA were to reply here, they'd probably say that Programmers don't know how to write SQL, be it from an ORM or by hand. If we let them send arbitrary SQL to the database, they're bound to screw it up. Therefore, you'll need good communication between DBAs and Programmers, anyway. I tend to disagree; Programmers can write simple SQL just fine, but forcing them to go through a procedure call means that the easy jobs are no longer easy. It's only the harder jobs where we need the DBAs, e.g. for making sure the indexes are being hit correctly.)

So that leaves the middle three, and I think all of them suck (and yes, I have dealt with all of them to some degree or another). I just think "hand written wrapped in a God Object" is the least suck.

. . . I'm countering it based on pretty solid commercial experience on some large projects with some household names.

If you want to get all Appeal to My Authority, then I've worked under a Fortune 500 for over 7 years, doing ecommerce sites that cover several brands in several countries, plus a few years of other professional Perl before that.

So what? The fact that different people with the same level of experience can come to different conclusions is why Appeal to Authority, including your own Authority, is a fallacy.

If I was going to use an ORM, it'd be DBIx::Class; it's a clear improvement over Class::DBI. I just have some fundamental issues with the entire idea of ORMs, and I don't find that they actually make the code shorter or more understandable, either. When I need carefully optimized SQL (the kind where I go ask a DBA for advice, because that's what they're for), then they tend to get in the way. When I need to access a lot of database procedures rather than standard SQL (because that's what the DBAs insisted on, despite my protests), then they tend to get in the way. When I want to think about my database access in terms of logical operations (which is nearly always), then they provide little to nothing over hand-written SQL.

If we were talking about an ORM that had a sophistication on the level of a good optimizing C compiler, then that might really be something. I doubt there's an ORM anywhere for any language at that level. I suspect DBAs would be mostly out of a job if there ever were, just like there's only a limited demand for programmers who can hand-optimize ASM. I also suspect its interface would have a much more sophisticated abstraction to match, on the order of the difference between C and raw ASM.

I wasn't attacking the source, I was calling you out for claiming something was objective when it was subjective - a couple of authorities think of accessors as a "code smell" - that's not a real metric like say "cyclic complexity" that McCabe proved affected code quality and maintainability, more importantly "code smell" is just an indicator that there "may" be a problem - after all the level of test and documentation coverage in DBIx::Class is very high - I'd trust a history of tests and rapid fixes over what is essentially a subjective hunch, even if it was Damien Conway or MJD, so that appeal to authority doesn't impress me much ;)

From what I can make out from your experience - your initial claim of "I can do better by hand" turns out to be "ORMs can be the least worst option" - I don't think you're going to get a lot of disagreement on that score LOL.

However your claims about and ORM like DBIx::Class "not making code clearer or shorter" show that you either don't know enough about the tool or haven't used it sufficiently, as does your claim they get in the way - you just need to RTFM, and learn the damn tool - maybe if you stopped grumbling about how "fundamentally wrong" they were you'd find that your apprehension is misplaced.

I wasn't "Appealing to my authority" I was stating your claims didn't match with my experience, so you needed to provide something more to back them up, which you haven't (aside from invoking some authorities on accessors being a code smell) - if I wanted to appeal to my authority I'd mention that my name is in the index of Simon Cozens' Advanced Perl vol 2 book and he gave me one of his promo copies of it, and not then admit that it was for a perl module that was already obselete and abandoned before his book was printed.

Also I've had similar arguments with a fellow I work with who you might have heard of - a Mr Piers Cawley (and yes we've heard him sing in the office, he's pretty cool to work with), he knows way more about comp sci, object orientation and design patterns, etc than I do - but he argues the opposite to your position about ORMs - he reckons it's not objecty enough, and too close to the SQL - but importantly, unlike you he admits he doesn't know them well enough about them to really write them off.

The reason I'm banging on about this is your claims about ORMs making it hard to do anything you can do with raw SQL are long outdated, I can do pretty much any SQL I need either through DBIC, it's extensions or relatively simple code of my own, or by using one of the several options available to specify raw sql, and I'm not an expert or claiming to be an expert - I mean I've been using ORMs very succesfully for a while but speaking to people like mst on irc it's clear that my expertise is quite limited, all the more reason that your claims that "it's hard to do X" or "you can't do Y" stick in my craw - when my experience shows you can and you don't need to be an expert, and does a great diservice to the developer and community of DBIx::Class that provide working code and documentation of a high quality that is amazingly useful, and provide very good and patient support on irc, mailing lists and free workshops at perl conferences and other events.

I just don't find that the ORM does that in practice. SQL is already an abstraction layer over whatever the database does internally. Often, I already know what the SQL should look like, or can easily figure it out from the vender documentation. With an additional abstraction layer, I now have to think about hitting the right settings to generate the SQL that I already knew how to do myself. The extra abstraction is nothing more than an additional layer of indirection.

Plus, a lot of the SQL that ORMs generate is really poorly optimized. Like running two separate statements when one statement with a join would have been better. In theory, they could do better, but that starts to look like the Sufficiently Optimized Compiler Fallacy.

Crazy idea, try actually using a modern perl ORM like DBIx::Class, you'll write less code and the code you do write will be clearer and more standardised and thus more maintainable and easier to document for somebody else.

A decade or so ago your claim about ORMs generating inefficient SQL may have been true in some cases, but these days they are very efficient if you use them at all sensibly (to the point of some benchmarks showing DBIx::Class with hashref inflated results beating raw DBI) - if your ORM is producing slow results these days it's because you didn't RTFM.

SQL is not that hard to write correctly. Whatever example I tried to come up to compare hand written SQL to an ORM or an SQL builder, the hand written version ended up being no more syntactically difficult than the alternatives, and is sometimes easier.

yes, depending on the database and the joins you want it can be a lot better than specifying every specific join every time, as the ORM will know what kind of join and what what constraint you're joining on, the joins can be named clearly and make for a very easy to write and maintain codebase.

While you're busy futzing around writing SQL by hand and reinventing the sql generation wheel badly with no unit tests or documentation I'll have my ORM and be implementing new features and putting higher level caching in front so it'll do more and be faster while you're debugging and tuning you hand-rolled version

While you're busy futzing around writing SQL by hand and reinventing the sql generation wheel badly with no unit tests or documentation . . .

You assume too much. When I write unit tests against the interface, I'm testing the logical consistency of the function with the SQL embedded inside. You don't test for logical consistency via a bunch of accessors and mutators.

I'm not assuming anything - I've worked with codebases that were done your way, and that was my experience.

I've used and understood ORMs for almost a decade, so I'm in a pretty good position to compare what I've seen in codebases using them or not. Your experience may differ but you've made a lot of bold claims about ORMs you clearly have little or no experience or direct knowledge of, and I'm countering it based on pretty solid commercial experience on some large projects with some household names.

For simple plain SQL it might be easier for you to just use strings and plain DBI. But as soon as the query becomes complicated and dynamic, you're basically hiding the nice SQL behind lots of logic and string-handling. I find the DBIC interface much cleaner and future-proof for such things.

That and the fact that DBIC gives me the right places to put the methods that deal with the data is enough for me.

I have a separate set of issues with those. The vender will have turned SQL into some bastardized programming language. It's rarely pretty, and why bother when you have a proper programming language on top of the database?

They have a place for specific things (like carefully controlling transactions and locks to avoid race conditions), but I don't like architecting the whole system around them.

The guy who wrote that article is a self promotional blow-hard. He bullied a woman online who wrote a Perl book, trying to sabotage her credibility. I think because he wanted his book to sell more, and because he's a prick.