I recently had to deal with some code that is filled with (home grown) code that implements every database table in the application as a Perl object. It completely conceals the SQL. Sounds like a great idea, no? Well, maybe the answer really is, “no.”
Since this is a mature application, there are several readily-observable problems in the design that was selected (and as it evolved over a decade):
To me, the concept really doesn’t provide clarity. It actually makes it difficult to see where database calls are taking place, and what those calls consist of. I can’t so easily “just look at” what is going to be given to the database engine. I have to grep to find where the calls are taking place and in what situations. Tracking of transaction-boundaries (and in code this old, there are only a few of those) is rather a source of consternation.
Database-specific tests and decisions have been made all over the application, and although they use these table-accessor objects to get their data, the business logic is scattered hither and yon. For example, if a statement such as if ($account_obj->get_type() eq 'C') ... occurs all over the application, well, it occurs all over the application.
Invariably, the accessors didn’t go quite far enough to do everything, so there is still a lot of SQL string-building in the application anyway. Two ways of doing things, and inevitable interference between the two.
So, am I slamming the idea of database-accessor classes? Not quite. My Meditation is, rather, that “a database table” is an obvious choice – but not, perhaps, the best choice – for what “a high-level object in an application” really ought to be. Has this concept, in this application and as it was applied in this application, really brought a helpful benefit to the table? I’m not persuaded. What would have been better? I listen fondly to the gently tinkling bells in this familiar sanctuary, and Meditate again.
“A database table,” like any and every other persistent data structure, really is just a mechanism to provide persistence. An Accounts table is not “an account,” although it persists some data about “an account,” and does so both by its own columns and by its relationships to other tables. An object that represents “an account” should not merely be a wrapper around an underlying data representation of a table and its columns, but a true, first-class object that is capable of answering questions, e.g. if ( $account->is_checking_account() ) ... (Which logic, of course, would be used primarily by a Account::Checking superclass.)
If Perl classes are used as wrappers around tables (and I am not altogether rejecting that idea, as I have used it myself), the use of those wrappers should be confined to the implementation of high-level objects. They should be low-level objects only. Rigid assumptions about the present data representations – (“what does C mean in the account_type column, and is that column always named account_type and is the letter always C?” “Welll-l-l, now that you mention it ...” Heh.) – are things that very easily become pervasive throughout an application, to very destabilizing effect. Using wrappers merely to get to that “letter C” is not, by itself, an improvement.