A r t i c l e s

Note: This Wiki is
outdated, personal views
may have changed.
L505 A.I. bot is dead
long live THX 1138

M a i n P a g e

D i r e c t o r y

Object Relational Mapping Is Double Dipping

Object Relational mapping works.. you can still get stuff done with it, and no offense to people who use Active Record in Ruby (I use it because existing projects I work on use it), but I find it doesn't magically increase productivity or solve all the world's problems because the bottom line is that all our Database tools need to be fed SQL, and we think in SQL, and we can reuse SQL everywhere.. so... the ORM solution isn't really solving the problem we need to solve.

Working in Ruby for some of the projects I do for companies has taught me a lot about object relational mapping. I've been working with Active Record.

Since I haven't been working with Active Record for several years yet, I will maybe come back to this article and add more information to it with time. However, here are some thoughts so far.

First, I want to offer a quote:

"Personally, I think the only workable solution to the ORM problem is to pick one or the other: either abandon relational databases, or abandon objects. If you take the O or the R out of the equation, you no longer have a mapping problem. It may seem crazy to abandon the traditional Customer object-- or to abandon the traditional Customer table-- but picking one or the other is a totally sane alternative to the complex quagmire of classes, objects, code generation, SQL, and stored procedures that an ORM "solution" typically leaves us with. Both approaches are certainly valid. I tend to err on the side of the database-as-model camp, because I think objects are overrated."
--Jeff Atwood, codinghorror.com

Object relational mapping has created twice the work for developers - instead of reusing all our existing SQL knowledge, we now have to relearn sql in yet another abstracted format that doesn't solve any problem.. and the saddest part about it is that object relational mapping makes little sense since a database isn't an object, an object doesn't have rows and columns, and a database should not be an object since a database is a relational database. Oh yes, did I mention that databases are not objects?

Tim Bray says:

"Lots of architects have learned, painfully, that you usually canít magick relational rows away behind object/class abstractions. The right way to think about a database is as a set of normalized tables that are designed to be addressed with SQL strings."
By the way, the picture of the colorful bike you see above, is a picture of some object relational friends of mine who have been smoking crack pipes too much. Again, take this with a grain of salt and all.. ORM isn't so bad that it doesn't work.. it does.. it just doesn't work better. That's the issue.

Now, I know there are quite a few object oriented nazis and delphi/fpc zealots who read my wiki, and I know some of you are all fond of this object relational mapping shit, and I know some of you have even gone as far as building ORM libraries yourselves, and I hate to hurt your feelings here, but I think that maybe some of you need to lay off the crack pipe and critically analyze the problem you are trying to solve.

Running a ruby script using active record, I saw a whole bunch of SQL statements, that were automatically generated, flowing across my screen. So, wait a minute now, I have to learn SQL anyway.. since debugging my object relational nightmare code requires I know SQL anyway... so tell me, why should I learn TWO versions of SQL:

  1. Object relational SQL
  2. SQL
When instead I could just learn one? What wheel are we trying to reinvent here? We aren't even reinventing a wheel - we are designing two wheels.. one SQUARE wheel and one partly round wheel - and instead of being given a choice to just use one of those wheels, we are forced to use both!

I've had it. I'm quitting programming, and going into the business of building and selling SQUARE training wheels for ADULT bicycles.

If they can build object relational mappings and have adult programmers actually use them and see advantages in using them, then surely I can build square bicycle training wheels for adults which will help adults learn to ride bikes a lot easier, and surely the square training wheels will increase the productivity of the bicycle two fold.

I will not sell the square wheels to children, because children will need to learn how to ride bicycles properly. Only the children who have already mastered bicycle riding, and only the children who have become full grown adults, will I sell the square wheels to. Magically, these square training wheels will make adults much much better bicycle riders (and these adults already have plenty of bicycle riding experience, even some have ridden up mountains without training wheels).

In fact the square training wheels will also make the adults much better bicycle architects also - and the square wheels will also teach them everything about how to repair their bicycle. And when the chain falls off or gets twisted in a knot, these square bicycle training wheels will help them significantly when it comes to putting the chain back on too - the square training wheels have these arms that pop out electronically and grab the chain and throw it back for them. No grease is even needed for the chains because the square training wheels also have this sprayer system that automatically maps the oil and sprayer to the chain!

What the Hell is this guy talking about

In case you don't understand my analogy, I'm talking about the ORM being a training wheel or a crutch. If the SQL database goes bad one day, you need to know SQL, and you need to know what SQL has been running in your programs. The exact SQL, not some abstracted SQL that was done behind your back. If you had written the SQL, you would know what SQL was being run.

ORM hides the SQL and you have all this automatic stuff going on behind your back. ORM libraries could even be buggy ORM libraries. Even if the ORM libraries aren't buggy, your SQL database can still go bad, or can need tweaking, or it can need tuning.. and when you use ORM, you won't know what to do when this situation arises. Or, if you do know what to do, you will be battling rather than working.

Not only that.. when someone pulls your training wheels off your little bike and you try and ride, you won't be able to do it. When you try to use someone else's bike that doesn't have training wheels, you won't be able to reuse that bike.. since standard bikes don't have training wheels on them. All standard database tools use SQL, and you need to have copies and snippets of SQL in your code and in your DEBUGLN calls out to your log files or the web browser.. because if things go wrong, you need to analyze the SQL and fix the DB using SQL. No amount of ORM is going to help you when things go wrong or when the database needs to be tuned.

Eventually you are going to have to use SQL (the real thing) to repair, check upon, and fix your database.. and without the SQL being in your code, you won't know WHAT YOUR CODE is doing or what it was doing. You won't have snippets of SQL that you can copy into your clipboard and then test in an SQL query window or a shell prompt.

SQL is also portable.. and ORM is not. Each language (ruby, php, delphi, etc) has its own little implementation of ORM, and the programmer has to relearn ORM each time he moves from one tool to another. Not all bikes have training wheels, but all programming languages can send SQL queries, and all SQL tools understand SQL! SQL tools do not understand ORM or other objectalinstantiationalism shit.

So what's the solution, smart ass?

Use SQL! That's what SQL is for. Portable SQL language designed to make talking to the database easy.. and when a problem arises you can copy/paste the SQL into a query window and test the issue, test performance, tweak the query, etc.

Yes, I know hardcoding SQL into your code isn't pretty - the concatenations make it obfuscated. There is a solution to that. Parameterized queries (prepared statements). If your crappy database doesn't support parameterized queries than you can make your own parameterized queries using templates with escaped $macrovars in them.

  • Keep SQL strings in separate little sub procedures that are easy to manage. Make good wrappers.. don't be lazy and hardcode messy SQL everywhere throughout.

  • Make a format() function so you can insert {$macrovars} into the SQL statement, reducing the amount of + plus + signs + (or whatever your concatenate operator is in your language)

  • keep the SQL in separate source files so they are easy to manage and modify

  • bind your records/structs/classes to your sql strings.. ORM does it for you in a bad hidden way. Don't rely on crutches, you will be very sorry when you need to copy that SQL over to another SQL tool, and you can't do it, because that SQL tool doesn't have ORM training wheels bolted on to it.. it doesn't understand ORM.. it understands SQL!

  • the bottom line is that you need the SQL, and SQL is SQL is SQL!

  • if we had better relational support built in to our language the problem would be solved.. maybe not ideally but.. for now SQL is the language that is out there that works in the "products".

Instantiate an SQL template rather than an Object

If your database is so poor that it doesn't support parameterized queries, you may have to build your own Format() style function that replaces and escapes the parameters.
--begin template--

SELECT {%col%} FROM {%tblname%} WHERE age < {%age%}

--end template--
If your database does support parameterized queries (a.k.a. binding parameters into the query, prepared sql statements, and a million other similar phrases) then you can do something like this:
SELECT * FROM table WHERE age = ?
See for example: Parameterized-Queries-in-MySQL

Consider SQL queries like html templates. All you are doing is instantiating the SQL string and outputting SQL snippets to the SQL db. You can reuse all your SQL from one application to another... just like templates can be reused. The other option of course is to hard code the SQL into your code.. which is essentially a poor-man's sql template system. Use a Format function for those situations.. not always is a full blown sql template needed. I'm just trying to get the point across that not everything needs to be an object.. sql strings can be templates, if you want to think of them that way (rather than hard coded in strings with concatenations).

When you need to debug your database, having the real thing (SQL snippets rather than ORM objects) handy is essential.. ORM hides the real thing, which is very bad. A template or SQL string in your code (or in a separate template file) makes the SQL available to you! This makes all the difference in the world for debugging, because your code is running real SQL that you have in some string or template, which you can modify and debug.. not some hidden ORM binding that you cannot hack. ORM is un-hackable and is a hidden blackbox.

A simple debugln(CurrentSqlBeingRun) call will output the current SQL you are experiencing difficulties with.. if you keep your SQL statements in SQL strings! But in ORM abstractions you never know what SQL is really being run (you can see what sql is being run if you turn on some Log settings to stderr or stdout, but it isn't SQL that you wrote, it is some unfamiliar automated ORM stuff that you cannot tweak.. it is just beating around the bush).

With a debugln() call and an SQL string handy that YOU WROTE in your application, you can copy and paste the string into all your other SQL tools.. you can then debug the SQL and tweak the SQL.. and modify the SQL to suit the problem/issue that arises, and then paste that fixed SQL back into your code. With ORM, you can't do this, you have to beat around the bush and play guessing games, or just limit yourself to what the ORM can do.. you can never actually use SQL. The whole point of an SQL database is to be able to use SQL! You can modify/copy/reuse the SQL between your different tools, and even in your shell session you can send SQL over a network. ORM locks the SQL into some blackbox hidden away.

Don't hide it. Don't beat around the bush. SQL is portable and useful. ORM locks it into a blackbox or a graybox. Programmers need control! ORM causes loss of all control and loss of all sane SQL debugging/tuning.

"Your programmers (who are apparently stupid) don't need to know anything about the underlying data model!" --Shawn C

"The interesting thing is that most of these guys like it in concept but have never actually had to support it." --Rob Conery

"It has always been my view that programmers should not try to isolate themselves into a black hole, not knowing or understanding how their own data is stored." --Shawn C

"Lots of architects have learned, painfully, that you usually canít magick relational rows away behind object/class abstractions. The right way to think about a database is as a set of normalized tables that are designed to be addressed with SQL strings." --Tim Bray

"Various drugs such as LSD are measured by their potency. Thus, a 'double dipped' dose of LSD would be twice as strong." --Wikipedia

Note: This Wiki is outdated, personal views may have changed.

This wiki contains info on life, health, humans, nature, programming, database, fads, paradigms, poems, principles, theories.

Articles may contain statements which some may find helpful and encouraging, or even discouraging.

Beware, I believe in the Grand Justice system.
_ _ _