I just read your article. You hit on something I’ve wondered and bitched about for a while.

As a long-time database/SQL programmer I have often wondered why programmers use relational databases as persistent object storage and ignore the powerful and well-tested data manipulation and programming features RDBMSs offer. I’ve seen hundreds of the “wrapper” classes you mention. The idea seems to be to map classes to database tables (or vice-versa), then moving the business/rules and logic into higher-level classes. This leads to clunky and inefficent classes for customer, employee, etc. and code somewhere else that manipulates the objects. Reporting and almost any aggregate functionality gets tedious to program AND slow, since it either generates a lot of discrete database queries, or it uses cursors on a result set.

I guess the idea of separating the database structure and contents from the business logic is a holdover from the three-tier architecture craze. Or maybe it’s caused by an overwhelming desire to do as much as possible in the “cool” OOP language and only do simple SELECT/INSERT/UPDATE transactions on the database. If you think about it for a minute, you realize that placing the data structure and contents in a back-end relational database, and the logic for manipulating/validating/displaying that data somewhere else (in middle-tier classes) is really the antithesis of OOP: data and the operations on it are now separated rather than encapsulated.

Rather than write a bunch of wrapper classes, I let the database engine do what it’s good at: managing data. So my Customer class has methods for OrderVolume(periodstart, periodend) or ChangeAddress(…). The wrapper class does nothing more than get a database connection (either opening a new one or using a pooled open connection) and passing NAMED parameters to a stored procedure. The SQL code in the database knows how to physically manipulate the data.

I recently rewrote some code that had wrappers for every table in the database: you could SELECT, INSERT, UPDATE, DELETE by passing a zillion parameters in a dictionary. The wrappers were really just training wheels to keep the programmer away from SQL. The wrappers constructed the SQL, but had no way to validate it for correct field names or types, so the SQL was tossed at the RDBMS with fingers crossed. Tracking through the code to find references to tables or columns was tricky because of the layers of indirection. My fix was to determine the operations the code actually performed against the database (and there were not very many operations). It never did something like change the city but not the zip code, or display just one detail line from an order. So there was no reason to have wrappers that could be made to do such things. Instead I wrote functions that know how to change a customer address, or retrieve all order detail lines at once. Those functions were mostly a few lines of code that called a stored procedure. Stored procedures are syntax-checked, type-validated, and compiled (I’m using SQL Server), so they are known to work before they are executed, and they are much faster than sending random SQL statements. In the process I was able to move code that does secondary lookups into JOINs in the stored procedure, do some formatting of results, calculate discounts and defaults, etc. The combined code is much smaller, simpler, and easier to follow than the original. Now if I we need to change a discount percentage that happens in one place, in the database code, instead of in hundreds of places that were fetching order detail lines one at a time, looking up the discount factor in the inventory table, doing the calculation, and keeping track of all that.

In short, try using different abstractions for the database. Treat it as a black box that can do specific operations on data or return formatted results, instead of treating it like a high-capacity dBase file.

The arguments I’ve heard against this kind of slicing are mostly spurious:

• It breaks the three-tier or MVC model. I don’t think so, but so what?

• What if we change back-end databases? That will be costly and require extensive code review in any case, and it’s rarely done in real life.

• SQL is old/lame/hard to read/etc. No it’s not, it just takes practice and some study, like anything else. SQL isn’t cool but it will land a job a lot faster than arguing about continuations in Haskell. Young programmers don’t like the old-fashioned syntax of SQL, or the paucity of functionality compared to the Perl, Python, Java, C++ they cut their teeth on. As an old-time Fortran and COBOL programmer I just want to hit hot-shots who complain about those languages even though they never used them. SQL has, I think, been lumped together with COBOL in the geriatric backwater of programming tools. Too bad, they don’t know what they’re missing and giving up — a single line of SQL is frequently more expressive and powerful than hundreds of lines of procedural code.

• My database engine doesn’t support stored procedures (from programmers who use mySQL or Access but have no exposure to Oracle, SQL Server, Postgres, Sybase, DB2, etc.). Use a real database engine, or hide your “stored procedure” in the wrapper class until you can migrate them to the database engine.

• I don’t trust JOINs or subqueries… how can I tell if I’m getting the right results? LEFT JOIN confuses me. From people who believe they have mastered C++ templates or Java checked exceptions this is just so much whining. Relational database operations have consistent, predictable, and testable behavior — you just have to read the C. J. Date book. I think database work separates the wheat from the chaff, because eventually you have to actually know what you’re doing with SQL, whereas you can get a long way by faking it and copy/pasting code snippets with C++, Java, Visual Basic, etc. The necessity of real understanding throws a lot of programmers, just like debugging in assembly language or debugging old C code does.

My anecdotal experience is that too many programmers don’t get relational database theory because it’s one of the (very) few things in the programming world that is based on real math and has provably right and wrong ways to do things. That’s not to say database designers don’t argue about normalization, just that the arguments are usually based more on definable and testable assumptions than arguments comparing, say, Java to C#. I think most programmers don’t get the set orientation of RDBMSs and SQL, and tend to think in terms of records and fields instead of relations; their thinking is one-dimensional, which creates the loads of slow and buggy cursor-oriented code I see all the time. The record-oriented approach introduces locking and consistency problems that the database engine can handle if you let it. It also hobbles the database engine and slows things down to the point that caching results (which any decent database engine does already) starts to seem like a good idea.