The Trouble With ORMs

Object-relational mappers (hereafter called ORMs) are used to provide a way for one to map an object in an object-oriented language to a table or set of tables in a (usually) relational database. On the Microsoft stack, they’ve become so popular as to be nearly ubiquitous. They do have their detractors, including people that have developed their own, such as Rob Conery, who developed TWO ORMs (Massive and Subsonic). That said, they do often make the earlier phases of a project much easier, by allowing developers to abstract away the pain of relational data access. This is frequently helpful in the early phases of a project because developers are moving quickly and breaking things while trying to quickly iterate over a design. That said, I find that the way ORMs are frequently used is often rather unhelpful as a project matures (and possibly rather unhelpful in the early phases as well).

ORMs certainly have an important place in development. I’ve used Entity Framework, NHibernate, Subsonic, LLBLGen, and NetTiers. They all are a substantial improvement over the underlying ADO.NET data access layer, which is verbose and annoying to use (and showing its age very ungracefully). When you are doing robust object designs where the objects in question don’t readily map to database tables, they can be a godsend by hiding all the work required to load a complex object (and any objects contained therein) into something that can be consumed in your development language of choice. That said, like all abstractions, this one leaks, sometimes in a rather atrocious manner. It’s far easier to end up with nasty performance problems (SELECT N+1), which often don’t show themselves until the application is released to production and is under load. It’s also more difficult to figure out exactly what is going on when one of these problems occurs, both because the abstraction hides what is actually going on (which means you need a profiling tool in many cases) and because you didn’t hand-craft the operation in question. This is possible to handle if you have an experienced team who is skilled with the data access framework; if you are a start-up with more junior developers and a tight timeline, it can derail a project into failure.

On the other hand, not abstracting the database in some manner can often lead to its own set of problems. For one, the old-school ADO.NET syntax is horribly verbose, frequently requiring the creation of several objects in order to simply return a result set. In addition, while developers are trying to save time, it’s very easy to introduce major security issues (SQL injection comes to mind) while trying to simply get work done. This is an especially severe problem in early stage startups that are trying to launch a product under a tight deadline with limited funds. In such situations, maintenance concerns often get short-shrift, as there will be no maintenance required if the product fails.

My opinion on this whole thing used to be “use an ORM, dangit, what’s wrong with you?”, but lately it has become more nuanced. For established companies going forward with a solid, experienced team, the ORM can be an asset in allowing them to quickly surface functionality (it can also be a fever swamp that destroys otherwise functional projects with lots of obtuse errors and hacks). That said, with early stage startups, the temptation to use an ORM is, in my mind, sometimes misplaced. If their team is good, it might not be a problem, but random issues with the ORM really add a lot of volatility to project timelines and I’m increasingly leaning away from using them as a result because of the risk they introduce (especially while the application is in flux, possibly with the end result being drastically different than the initial idea). That said, I do dislike the verbose syntax and problems created by using vanilla ADO.NET. I’ll get to how I get around this in a moment, but what I’ve done seems to largely be working well.

I also firmly believe that many of the problems we experience with our use of databases in this industry is actually a problem with communication among personnel. That is, not everyone views the database in the same way. Developers who are very comfortable with ORMs or Active Record systems practically treat the database like a file in many cases. It doesn’t do logic; it just stores data (often with referential integrity not even considered at the database level and ONLY handled at the application level). At the other end of the spectrum are developers who put nearly all of their business logic in the database, with their application being a very thin layer over the top (you’ll see this in manufacturing applications a lot). Most folks fall right in the middle, using the database as storage, but designing constraints to make it harder to screw up. There are two problems here, the first being that teams frequently don’t agree on where certain parts of the code should be implemented (as they have people from different parts of the spectrum making independent decisions). The second issue here is that folks in the middle and developers who put all their logic in the database seldom need a full-blown ORM. Indeed, the ORM can frequently complicate things for them, which is why you see so many companies using something like entity framework, but having every object in their business layer map directly to a table. In this case, the ORM is often really getting in the way, and adding little beyond a strongly-typed wrapper around the database. Such structure tends to couple the business layer (and occasionally the UI layer) of the application to the database layer very tightly, leading to difficulties when the database needs to be changed, or when other applications start using the database in unexpected ways.

My position is that at no time should a team consider their database to be part of their application, unless the database is purely local. If it is going to live on another server, it isn’t part of your application. It’s another service sitting out there, with all the usual boundary concerns that such a service involves. These include, but are not limited to the following:

• Abstraction that keeps the implementation of your business logic separated from the storage mechanism in the database in a way that allows both to be changed independently from the other, with appropriate optimizations on each side. This may include caching on the application side, and de-normalization on the database side for certain performance scenarios.
• Error handling code, both to handle unexpected circumstances that occur while executing a query, as well as code that handles database failures cleanly so that a database error creates as little disruption as possible to the running application.
• An understanding that a database call is not the same as an in-application call, with appropriate treatment for the call in regards to dispatching it and getting results in an asynchronous manner.
• An understanding that the database (typically) lives on another server, with a network connection between the application and the database. This means batching of calls to make operations chunky instead of chatty in order to cut back on the amount of bandwidth required to process a call.

Appropriate security measures taken across the service boundary. Your application should have access to the things it needs and nothing more. The same as every other application that is utilizing the database. It’s a service external to your application, remember? Act like it. Depending on the application, you may want to have separate schemas for each application accessing the database, with no direct access to the tables themselves. I don’t usually go that far, but it’s an option and can be important when you have serious security concerns (medical and financial data) and strong logging requirements for regulatory reasons.
Therefore, my recent way of handling data access in some applications that I’m helping to construct does NOT use a real ORM (I will point out again that I do a lot for early stage startups, so your mileage may vary and the below does NOT necessarily apply to more established businesses. In fact, it doesn’t apply to all startups either, just those that are making light use of an ORM). What I’ve done that I’ve had success with is to create a lightweight wrapper around the database using a T4 template (or a set thereof, for easier maintenance). The template is typically smart enough to find all the stored procedures in the database and wrap them so that any breaking change from the database will result in a compilation error (instead of a runtime error) in the application code. In addition, I typically create simple DTOs from the various tables and views that are available, if any, with some very simple (and easily implemented in other types) code allowing them to be deserialized from a DataReader or DataTable (but not serialized back to the same). It would be trivial to implement such functionality, but I do not, as I tend more towards keeping commands and queries separated. I typically do not save objects, but rather process messages. I then wrap these with service objects that are consumed by the rest of the client application. The service objects are lightweight and simply provide an interface to program against (and one that can be mocked for unit testing). So I might have something like the following in an MVC controller:

[HttpPost]
public async Task<ActionResult> RegisterUser(UserModel newUser){

   UserRegistrationMessage userRegistrationMessage = newuser.ToUserRegistrationMessage(); //Just maps fields. Shouldn't throw.
   try
   {
        //validation happens in here
       UserRegistrationMessageResult result = await this.UserManagementService.HandleMessage(userRegistrationMessage);
       return Json(result);
   }
   catch(ValidationException valEx)
   {
       //Validation failure. Render the view again with the validation issues
       //Code here to map the errors out of the message back into the viewmodel object
       return Json(new UserRegistrationMessageResult(userRegistrationMessage){Failed = true, FailureReason = valEx.Messages});
   }
   catch(Exception ex)
   {
       //Something else happened
       throw; //Really, you would redirect to an error page, log, etc., but it's not relevant right now.
   }
}

Other than the wad of code around error handling and validation, the controller code is rather thin, delegating to a service (in practice, further abstractions shrink this a lot more, but I didn’t want to get into that here because it’s not germane to the discussion). It’s also important to note here that security concerns (like scrubbing the password out of the returned object) are handled by the service. The web layer is a thin, fast wrapper over the service layer. Now, the service layer might look something like this.

public async Task<UserRegistrationMessageResult> HandleMessage(UserRegistrationMessage msg){

   if(!msg.Validate()) //Check around validation parameter values, no call to database.
   {
      throw msg.BuildValidationException();
   }
   
   UserRegistrationMessageResult returnValue = new UserRegistrationMessageResult();
   DB.ExecuteTransaction((conn, tran)=>
   {
      bool userNameAvailable = await DB.usp_UserNameAvailable.ExecuteScalarAsync<bool>(new usp_UserNameAvailableParams()
      {
         UserName = msg.UserName
      }, conn, tran);
      if(!userNameAvailable)
      {
         throw new ValidationException("User Name taken");
      }
      UserDTO userRecord = await DB.usp_CreateUser.ExecuteSingleObject<UserDTO>(new uspCreateUserParams()
      {
         UserName = msg.UserName,
         Password = msg.Password,
         Email = msg.Email
      }, conn, tran);
      EmailQueueDTO emailRecord = DB.usp_ScheduleEmail.ExecuteSingleObject<EmailQueueDTO>(new usp_ScheduleEmailParams()
      {
         Email = msg.Email,
         Message = EmailTemplateService.CreateUserEmail(userRecord.ToBag()),
         DeliveryDate = DateTime.Now(),
         Subject = "Your new account is ready!"
      }, conn, tran);
      
      returnValue.userID = userRecord.UserID;
      returnValue.confirmationEmailID = userRecord.confirmationEmailID;
      returnValue.Success = true;
      return returnValue;
   }
}

We’ll unpack this a little. Several things have to happen when a user tries to register. First, we need to make sure password complexity requirements are met, the username is valid, etc. Next, we need to do a series of things in one atomic transaction, so that the entire thing fails or succeeds all at once. My generated code creates a static DB object that allows you to pass a callback and it will create a database connection and transaction under the hood, and then invoke your callback using them. If the transaction fails, it rolls back, otherwise it is committed. We can also make that happen within, but this case is simple enough not to need it. Once we are inside the delegate, with the transaction and connection in scope, we do a quick call to make sure the username isn’t already in use. If it is, we throw an exception, the transaction is rolled back, and the caller handles it (we could also return something here, but this is for example purposes). If it succeeds, we call two stored procedures in quick succession to create the user record and the record to tell our mail processor to send an email message (start-ups use a lot of database tables as makeshift database queues). For the sake of the example, we’re assuming the EmailTemplateService is constructor-injected into the UserManagementService.

The above example makes it very easy to figure out which procedures are being called, in what order, and with which parameters. You can even step through on the debugger. The main downside is that you have to a) write a custom T4 template to put all this stuff together and b) have to write the stored procedures. The procedures are actually fairly easy if you are comfortable with SQL. If you are not, you should probably get that way before building an application that relies on a database, even if (perhaps especially if) you are using an ORM. The T4 template is…trickier. While the ins and outs of proper template design are outside the scope of this article, the main pain points revolve around getting appropriate metadata from the database itself. The rest is fairly simple, as T4 is remarkably like building a classic ASP page or using razor templates. I can’t give out what I’ve built for clients, however, here are some bits of SQL to handle common tasks. You’ll want to execute these queries to get the appropriate metadata, then loop through it to build your data access layer. If you build it properly, you really only have to do this once and can mostly reuse it in other projects.

--Get all tables/Views in the database
SELECT * FROM INFORMATION_SCHEMA.TABLES

--Get all columns in the database with table/view names
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

--Get all stored procedures/functions in the database
SELECT * FROM INFORMATION_SCHEMA.ROUTINES

--Get all stored procedure parameters in the database
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS

--Get all schemas
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

I know a lot of people really dislike generated code, but this approach cuts a lot of crud out and lets you focus on what you are actually trying to do. Properly constructed, bugs can be fixed in the template and it can be re-run as needed. You would re-run it after any significant database change. Initially, it might just be a thin set of wrappers over the stored procedures in the database, but over time you can add a lot of cross-cutting functionality. For one of my projects, the system has a caching layer, for instance, as well as transparent logging of errors. In the example above, methods like ExecuteSingleObject actually call “Fill” on a SqlDataAdapter under the hood (you have to do some trickery to make that async), with the object itself being able to fill itself in from the resultant data table. The object is also generated code, based off of the definition of a particular table in the database. Many of the classes I build are partials with a common base class, that can be further extended by overriding particular methods. This is important when generating code, as you don’t want to get into the practice of regularly editing a template to add special cases.

This system makes it very clear exactly which procedures you are calling, with no guesswork. If there are performance problems, it’s fairly easy to make an alteration to the generated code to emit timestamps into the debug window before and after each stored procedure call. With the slow procedure identified, you can then proceed directly to SQL Server and figure out what is going on, using the tools there, no third-party profiler required. It’s fairly straightforward to adapt the code to handle oddball datatypes that most ORMs don’t handle well (hierarchyids and various user-defined types come to mind). It also assists in the refactoring of database code, as if you change any procedure’s name, a compile error will result, which is far better than using magic strings. Finally, it can be quite fast, as you can design your database logic based around the easiest way to get the data into the system, rather than being stuck with the constraints placed on you by an ORM. You’ll note that as the product matures, you could still swap out the generated code for an entity framework model-first design if you wanted to do so.

I find that this approach allows a lot more transparency around data access early on with small teams of non-senior developers (typically just two or three), although you’ll probably need at least one senior or mid-level developer to deal with the template code. Given that this does require a bit of pain up front, it may not look very easy. However, a fairly functional template can be put together in a day or so by an experienced developer and save a lot of time as the project goes on.