Register for a free consultation!

Object Relational Mappers: Why and Why NOT Use Them

8/26/2016 11:25 AM

As developers, we find that the database king; it is the foundation on which all other software is written.  The problem with that is that nobody starts out as a database developer.  In our quest to become the perfect developer, almost all of us neglect the first and most important step, learning about databases.  First, we find ourselves struggling to wrap our minds around set based programming (remember trying to find a quarter sized rock using tweezers and a magnifying glass instead of two boxes, one with holes slightly larger than a quarter and the other with holes slightly smaller?)  Second, we have to pick some type of data access language and understand that whatever it is that we pick probably has memory leaks and definitely has a ton of overhead.  If you live in Microsoft's world you have to remember to close your ADO.NET connection and dispose of your objects when you are done with them.  Third, we have to decide how to model the middle tier objects and what logic goes where.  There is little wonder that most (I've heard as high as 90%) new software begun has never seen production.

So there has to be a better way, right?  Surely someone has written an application that does all this grunt work for you.  Enter the Object Relational Mapper.  Wikipedia states:

"Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language. There are both free and commercial packages available that perform object-relational mapping, although some programmers opt to construct their own ORM tools."

"That sounds great!" you say.

"I want one!" you exclaim.

"Which one do I use?" you ask.

Aye, but there's the rub.  Honestly, if you don't understand what all this automagic stuff is doing, you probably shouldn't use it.  First, I eschew using anything that doesn't allow me to pull back the curtain as see how it works.  Microsoft provides Entity Framework which installs all kinds of visual geegaws to access your data, and all kinds of code with the warning (and I paraphrase) 'You don't know what this does so don't mess with it.'  If that particular code doesn't do what I think it should do, I want to know why and I simply can't.  All of the ORMs I have used and heard of suffer from this problem.

Another problem is that you don't know what kind of set based code is getting run on your sever.  One of the biggest problems with any application is "it's running slowly" and everyone looks at the database.   If you can't see the code running, you certainly can't troubleshoot it. 

Best database design practices are to lock the users out of the table structure and all data access is done through stored procedures, so we can apply logic and security and auditing.  Unless your ORM writes procedures (and it doesn't) you have to write them yourself.  That kind of makes the ORM useless as a code generation tool, since you have to write your own code anyway.  Even worse, all the set based code I've seen, is horrible.  if you venture one little pinky toe outside of simple Create/Read/Update/Delete (CRUD) operations and you will, you have to do searches at a minimum, the set based code passed by these ORM tools gets hairy fast.

We, here at Sentia wrote our own ORM, and we call it the Object Relational Mapper (of course).  We think of it less as an ORM and more of a code generation tool that we use to produce the same code that we would have written by hand anyway.  Sentia's ORM produces all the stored procedures for CRUD and additionally provides a Search capability as well.  More stored procedures are generated to access related data.  If we want all the blue cars we do a search where car.ColorID = 36 (36 is blue and we can search for that as well) but we can also search for many to many relationships.  If we want to get a student's class schedule it might look like this Class class = new Class(student.StudentID, GetClassesByForeignKey.ByStudentID).  That might be the very first line of code we have written in this application, it uses stored procedures so we can be secure, and it is returned by code that a good developer might write, not some black box that has a skull and crossbones warning on it.

There are several things we've had to do over the years to make the code produced by Sentia's ORM more usable.  We put all the generated code in a 'Generated Code' folder so we can tell what was produced and what was hand written.  All the generated code is put into partial classes so that we can extend the functionality of them without having to come up with kludgy new names.  We are working on an application where the user can sign up for the service.  All calls to the database require a username and password that a new user won't have.  We has to extend the Professionals class with a new method that took a new professional object with its properties set called CreateNew that didn't require a User Name and Password.  This is all seamless to the downstream developer of course.  he would expect to see professionals.Add or Professionals.Delete in the intellisense menu, and now s/he can also see CreateNew(Professional).

What is the conclusion?   We decided that Sentia's ORM should write the code that we would write if we were absolutely perfect and did everything that we were supposed to do.  In many ways, generating code like that is the ONLY way to get it.  Otherwise, using nHibernate or the Entity Framework or whatever other half-assed thing you need to get the job done is ok, if you are writing a movie list application for your mother to access her collection online or you are a junior developer who can't get the job done any other way.  If you are a junior developer and you can't get things accomplished any other way, know that you are going to have scalability and security problems.  Even worse, you can't write the code that Sentia's ORM generates.  We can't.  Even we end up cutting corners sometimes, and it takes us weeks or months to do the same work completed in seconds with Sentia's ORM.

What should you do, dear reader?  You should either come to work for us, or give up and have us write your application for you.  Heck, if you sell it for the price you think you can produce it for, call us and we'll do it for half. 

It ain't arrogant if it is true.

Date Written Comment

Add Comment: