If your application is naturally OO, and is not self-contained, then you need to persist your objects between runs, then Object-relational mapping makes a lot of sense.

If your application processes bulk data, manipulated en-mass using complex queries and joins, OO-relational mappings tend to get in the way more than help.

DBIx::Class (DBIC) is generally preferred [citation needed] over Class::DBI (CDBI) for the last few years; speed and flexibility the main reasons.

You can search the monastery for quite a few nodes discussing these kits and several others worth considering; Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object); google:site:perlmonks.org dbic cdbi.

With DBIC there is not really an impedance mismatch because it has the meta-considerations of records and record sets (Results/ResultSets in DBIC parlance) split nicely and each encapsulates the proper layers like engine and SQL generation. These things have steep learning curves but once you get the hang of them they make dealing with data and databases trivial, testable, flexible, perly, and largely self-documenting [citation needed].

You also asked for examples of how it can make things easier. Here are some code nodes with DBIC.

• Re: RFC: Databases made easy (and hard database tasks made possible)
• Re^3: Maybe database tables aren't such great "objects," after all ...

One of the punchlines kind of buried in there is this shopping cart method–

sub checkout_total {
   +shift->items->get_column("cost")->sum;
}
[download]

DBIC starts to get extremely powerful in a short space and if treated correctly can be highly semantic and self-documenting. Chained ResultSets are another part of this but aren’t covered in my examples. They are in the Master Class XUL, linked in one of those, which is definitely worth a look.

Chained result sets are immensely useful. Certainly I could write the necessary SQL by hand, but chaining result sets mean that I don't have to!

"what benefit there is to replacing the conciseness of a SQL statement (which can be wrapped up into a method anyways, right?)"

Indeed - my take on it is that these ORMs are (at one level) just the generalization of those methods written (and tested) for you already.

You also end up with some nice tricks that other people found useful like DBIx::Class's ResultSets which are nice for building up a query over different chunks of code:

my $store = $schema->resultset('Items');
$store = restrict_to_department( $store, $department ) if $department;
$store = restrict_to_seller( $store, $seller )         if $seller;
print format_top_ten_items( $store ); # doesn't care whether $store is
+ restricted

sub restrict_to_department {
  $_[0]->search({ 'department.name' => $_[1] }, { join => "departments
+" });
}
[download]

I doubt hand-rolled code could be so concise (without re-implementing DBIx::Class). the restrict_to* subs are completely independent (don't even care whether the other feature exists). The database won't be hit until $store is iterated over within the call to format_top_ten_items. Of course - you do have the investment in learning the ORM (which is non-trivial) and, as always, the benefit to your application may vary. I was once a skeptic, but have decided that for most code (simple imports/exports being the most obvious exception), an ORM (DBIx::Class in particular) is worth the investment to learn and use.

One advantage immediately comes to (my) mind: You don't neccesarily need to know SQL

As I can see, Class::DBI mentions two further advantages in its Introduction section

One advantage immediately comes to (my) mind: You don't neccesarily need to know SQL

Hmmm. There's no reason not knowing SQL means you have to use an OO mapper. Furthermore, taking Class::DBI as an example, it only generates basic SQL for you. Anything a little bit non-trivial, you need to come up with yourself.

Class::DBI mentions two further advantages in its Introduction section

Eh, the "let's reinvent the wheel" section when it comes to triggers and such? You call that an advantage?

I find that Class::DBI doesn't abstract. Quite the opposite. By mapping tables to classes, it exposes the structure of your database to your application.

There's no reason not knowing SQL means you have to use an OO mapper.

No, there is no reason to use OO if you want to abstract, but it is a possibility. And the original question was why should someone substitute SQL with OO.

Furthermore, taking Class::DBI as an example, it only generates basic SQL for you. Anything a little bit non-trivial, you need to come up with yourself.

If someone doesn't know SQL what are the chances that he will generate non-trivial SQL?

I find that Class::DBI doesn't abstract. Quite the opposite. By mapping tables to classes, it exposes the structure of your database to your application.

I don't have a problem with calling the abstraction a mapping instead. Note that the author himself says that Class::DBI "provides a *simple* database to object *mapping* layer" (emphasis added). The two advantages he mentions (higher oder functions usable without database having a say in it and abstraction of database implementation) still seem to hold even though they might not be implemented to everyones satisfaction

I find them to be generally useful, albeit with one very important (to me...) caveat:   they are not the basis of “my program’s objects.”

For program-construction purposes, I think of “objects” as “Things.™”   Those are the Things™ which pop up in ordinary business context discussions about what the program is supposed to be doing for the business that is paying for it.   It is inevitably true that those Things™ will be instantiated using information from one or more database tables, but that is an opaque characteristic of the Thing™ from a business point-of-view ... therefore, the object’s implementation conceals it.   OO-wrappers may well be used in the employed in the implementation of the CRUD methods of the Thing™, but, in my designs, none of these subordinate objects are “the Thing™ itself.”

Actually, I do find that OO-wrappers are actually quite handy, because when you start using them in earnest, you really do discover just how much of your time you’ve been spending all these years, dicking around with the construction of SQL strings . . .

Class::DBI::Lite FTW!!!

Seriously though - to answer your question - "Why?" - it comes down to a matter of preference. I prefer to deal with objects when possible - but not at any cost.

If you simply want to loop over the items in your table and do a string substitution on one of their fields, you can setup a "select" statement handle and an "update" statement handle, iterate over the results of your "select" and execute the "update" handle once for each item. Or....

foreach my $widget ( My::db::widget->search_where({foo => 'bar'}) ) {
  (my $value = $widget->frob) =~ s{blech}{blargh}sig;
  $widget->frob($value);
  $widget->update;
}
[download]

Easier to write and easier to follow 4 lines of code than a dozen or more...