Avoid Mixing O/RM and SQL

time to read 3 min | 478 words

It is common to people that are starting use O/RM to get into a situation that they don't know how to solve using the O/RM (or even aware that such a thing is possible). The easiest example is loading just Id and Description for a certain class without loading all the (possibility expensive) properties.

There are several ways to do this using most O/RMs, but they are usually not obvious to the beginner. This is usualy a pain point since they can easily see how they would do it using plain SQL, but they either don't know how to use the tool appropriately, or they just don't think this is possible using the O/RM. The immediate solution to that is to just use plain SQL (or SP) to solve this one problem.

It is wrong because it pokes big holes in the model you show the rest of the system. If means that the model that most of the system is using doesn't match the model that the rest of the system is using.

This is bad because you lose of of the key benefits of using O/RM, abstracting the database. A good O/RM will always let you work against the objects structure, not the data structure. This is important because it frees you from dealing with a potentially ugly schmea and work with a good model all the time.

In nearly all the cases, you can use the options that the O/RM gives you, and get what you need without dropping to SQL. That is not to say that SQL isn't important. It is very important, because O/RM tend to be leaky abstractions, it is just that you shouldn't reach to the familiar tool when you need to solve a problem without an immediate solution.

What I'm more afraid of is that using SQL to solve the immediate problem will start to spread through the code, since "it is already the way we did it in XYZ". And that is a whole new can of worms. Letting this spread means that you effectively have two data access layers for a single application for the same model. I trust that I don't need to explain why this is bad. Just the syncornization between the two will be a pain, and considerring the way the SQL Access Layer is likely to appear (growing from quick solutions to problems), I am willing to be that it is not going to exhibit good design (at all levels).

If you really need to use both, make sure that you investigate the options to solve the problem using the O/RM first, and then make sure that this solution goes through the front door with all the bells (Design, Tests, Code).