Just Say No to Manual CRUD
Comments(18) 
I’ve been working a lot with Castle’s Active Record and Ruby on Rails in the last month and as a result have written significantly fewer basic CRUD operations and database access code. It’s been an addictive experience and has caused me to rethink the proper role of hand-written database code (sprocs) within an application.
Although I feel perfectly comfortable in a set-based world writing SQL, it has traditionally been one of my least favorite areas of coding. Besides being relatively repetitive and tedious, at least when it comes to basic CRUD operations, sprocs are much more difficult to handle when it comes to source control, versioning, debugging, and unit testing.
For example, at my last job we were tasked by auditors to come up with a build and deployment process that included version traceability and rollback capabilities. It was pretty easy to put together an acceptable solution for assemblies, since automatically versioning dll’s in a trusted way is simple via the AssemblyInfo and rolling them back is trivial since everything is contained in a manageable number of dlls that simply need to be copied from one folder to the next. When it came to sprocs, however, the best we could offer auditors was some hackery around adding comments about the version at the top of each sproc definition file along with a big disclaimer that there was no guarantee that files were not modified by DBA’s along the way.
At my current job, database code causes us even more trouble because the database is larger and contains more sensitive data (thus making restores more difficult), there is a heavier reliance on shared code, and only the deltas of database code are currently under source control. It feels like I am constantly tracking down some ghost bug that is caused by my local database schema somehow being out of sync with the codebase.
Despite these misgivings, I’ve dutifully followed the traditional Microsoft recommended best practice of funneling all data access through sprocs up until recently because sprocs were supposedly faster, more secure, and provided a beneficial layer of abstraction.
Although I have heard several arguments against sprocs in the last several years, I recently embarked on a more thorough investigation while trying to convince my team to switch over from using an in-house code generation\sproc-based solution for data access to using NHibernate\ActiveRecord. Here are a few resources I found that present good counter arguments against the conventional sproc wisdom.
- Stored procedures are bad, m’kay?: This classic post that was written by Frans Bouma of LLBLGen fame in 2003 spawned quite the flame war on the topic. Most notably, Frans counters the “Sprocs are faster because they are compiled” argument by quoting passages from the SQL Server BOL documentation that clearly suggest otherwise. He also counters security arguments by pointing out that parameterized queries prevent SQL injection just as much as sprocs and that assigning permissions to views and roles provide just as much protection as assigning execute rights on sprocs.
- Who Needs Stored Procedures, Anyways?: This is also an older post from Jeff Atwood where he nicely summarizes the negative aspects of SQL when compared with a traditional coding languages and came up with the quotable phrase “Stored Procedures should be considered database assembly language: for use in only the most performance critical situations”. I definitely think that some developers are reluctant to embrace ORM’s for the same reasons that many old C++ programmers scoffed at the idea of letting the CLR garbage collector manage memory for them instead of manually doing it themselves with raw pointers.
- Why I do not use Stored Procedures: Jeremy Miller dismisses performance arguments by declaring them instances of premature optimization and elaborates on all the problems caused by sprocs when it comes to maintainability, testability, and architecture. He also points out that the touted benefit of allowing DBA’s to make changes is actually a dangerous practice since it represents a breaking API change from the application’s point of view and thus should go through thorough regression testing before any DBA should be allowed to make changes.
- Foundations of Programming – Part 6 – NHibernate: Besides offering a nice introduction to NHibernate, Karl Sequine provides a nice summary of the historical sproc debate, including a counter point for the increased network traffic argument, which he says is a moot point since most traffic occurs between a app and database servers sitting on the same internal GigE networks where bandwidth is fast, plentiful, and free.
- DotNetRocks ORM Smackdown: For a more balanced debate, listen to this podcast episode (or download the transcripts) where Oren Eini and Ted Neward face off over the value of ORM’s in the software industry. Be sure to check out the commentary on the episode in the comment section of this Ayende post as well as the rebuttal in this post by Ted Neward.
In my opinion, one of the strongest denunciations of traditional sproc dogma comes Redmond itself, which seems to be straying from its original sproc recommendations in favor of a more more dynamic SQL generation world-view with its recent release of LINQ to SQL and the Entity Framework.
If you follow the open source world or program in some language other than .NET, then you’re bound to feel a little smug right now because ORM’s have been around for a long time. In fact, I have a vivid memory from 6 years ago of a co-worker who was fresh from the Java world being dumb-founded that Microsoft didn’t have any ORM solution. He was used to using HIbernate and the thought of manually mapping database tables to domain objects was hard for him to grasp. Even in the .NET open source world, I’ve been reading blog posts that sing the praises of NHibernate and IBatis.NET, two popular .NET ORM ports, for several years.
On one hand, Microsoft’s entrance into the fray is good news for ORM enthusiasts since it means that a larger audience of developers will begin to see the technology as legitimate. On the other hand, Microsoft clearly has some catching up to do in this space, so you might want to think twice about starting off with Microsoft’s offering rather than one of the more proven open source or third party alternatives.
If you are a .NET developer and new to ORM’s, then I recommend starting out with Castle’s Active Record, which you can learn in less than an hour by reading this Getting Started with Active Record tutorial. My co-worker’s were reluctant to try NHibernate because of the perceived learning curve and the plethora of mapping files required, but they quickly agreed to use Active Record after only a short demo.
If you are a POCO purists, which means that you want to keep your domain objects free of any non-business related concerns (such as persistance), then you’ll want to follow the repository pattern using the ActiveRecordMediator class rather than inheriting from ActiveRecordBase like the tutorial shows. Some of the more experienced ORM users seem to see ActiveRecord as more of a gateway drug to NHibernate and ultimately prefer to forgo the conveniences offered by the ActiveRecord layer in favor of the increased flexibility and loosely coupled design offered by dealing directly with NHibernate instead.
Regardless of the approach taken, I definitely no longer believe that sprocs should play any significant role in any application. The current mandate in the software industry is to strive to lower costs by increasing developer productivity and ORM’s clearly help to do this by eliminating the need to write and maintain countless simple CRUD sprocs.
It’s definitely time for all of us .NET developers to abandon our convention sproc wisdom and start playing catch-up with the rest of the industry when it comes to using ORM’s.
Popularity: 31% [?]
[...] Just Say No to Manual CRUD (Russell Ball) [...]
Thank you for this post. It is a great analysis to which I can refer clients next time I need their approval to use NHibernate/Castle Active Record, (which is less often as NHibernate awareness increases).
[...] *** Just Say No to Manual CRUD – Russell Ball ‘ (…) working a lot with Castle’s Active Record and Ruby on Rails in the last month … It’s been an addictive experience and has caused me to rethink the proper role of hand-written database code ‘ [...]
Amen Brother. So many .Net shops are incredibly slow to move this direction. I remember writing a rails-like form to db framework in classic ASP for myself because I was tired of writing CRUD.
The most dreadful experience when joining an existing project is opening the database in SQL Management Studio and waiting 5 seconds for the scroll pane to render all the sproc names. My heart just sinks when that happens.
Troy
[...] on queue, Caffeinated Coder recently a great article on why one should "Just Say No to Manual CRUD" which provide a list of resources which present good counter arguments against the [...]
[...] http://www.caffeinatedcoder.co.....al-crud/ Thanks [...]
If you’re looking for an ORM solution you could do much worse than CoolStorage.
IMHO, CoolStorage is the best ORM I’ve ever used. It’s also free and available on codeplex:
http://www.codeplex.com/CoolStorage
Welcome to the ORM revolution brother!
The next strength in your repository of tools would be a Dependency Injection Container such as Windsor Castle or Structure Map. Windsor has a much smaller learning curve than NHibernate. The only thing that must be understood is grasping the whole concept of IoC/DI and interfaces. From a maintainability standpoint, I can say that you will be glad when you have this in place. Very happy!
Follow that with a logging tool called log4Net and I think you’re well on your way to making development much easier. Super simple to pick up.
Very strong in implementation.
@Robz – I’ve actually got some time set aside today to play with Windsor…:-)
Let me know if you run into any snags. Bitter Coder has some great tutorials on it!
I have yet to jump on the no-stored-proc-gotta-have-ORM bandwagon.
I downloaded nHibernate and played with it for a day. I hooked up a simple table to a simple object and then shrugged my shoulders and never looked back.
I have no great data translation pains and two 3rd-party security audits specified that we use stored procedures. Not sure how we would manage security for inserts, deletes and updates via views anyway.
The pain I do have though is in regards to user interfaces which I hate to write and business objects that I hate to corrupt with CRUD methods when that is completely separate from business methods.
Would appreciate your feedback.
-Joe
CodeHeaven’s Required Reading – June 6, 2008…
These are all blog posts I flagged as being particularly interesting, but ones where I may not have anything…
@Joe – So the question comes down to what is more secure, a Sproc or a parameterized query? Some say they are equivalent. I say a parameterized query is because you manage your permissions at the object level (tables, columns, etc). I will get into that in a moment.
I would almost want to question what standards the 3rd party security auditors were using when they mandated Sprocs for you. As far as permissions go, with MSSQL Server 2005, you can manage security down to column access in tables. That means you could manage security down to the column level should you want to go down that far. My preference is a the table level unless there is a good reason to be more secure. You give up that security the minute you grant exec on sproc (unless you specifically use deny permission to ensure certain things don’t happen).
I do have an extremely good question for your 3rd party auditors. What permissions do you get when you grant exec on a stored proc? Anything you want that Sproc to do. We use naming conventions like usp_name_Select, usp_name_Update, etc, to help understand what the sproc is doing. What guarantee do you have that a particular sproc is doing nothing more than a select and not updating or deleting rows? Nothing once you grant that exec. Just trust.
Recently we had something happen that I am not at liberty to discuss, but if you let your imagination run wild, you can think of some possible scenarios.
With that example, I think you can figure out why I don’t believe sprocs are not a a good security model at all for databases.
Also, the cached query plan is not an argument since MS SQL Server 2000 made the difference in performance negligible (sp?).
Darn double negatives… and an extra “a” to boot. Amending comment…
With that example, I think you can figure out why I don’t believe sprocs are a good security model at all for databases.
@FerventCoder – “So the question comes down to what is more secure?”
No it doesn’t, at least not for me.
Forget I mentioned security. There are thousands of smart developers who can argue both sides of that fence.
Since I am interested in this movement by business application developers away from sprocs, I am going to make an effort to learn more about the security implications. This obviously is not the forum for me to do so.
There seems to always be things like this that come down the pipe and often get treated as a “must” only to fade or be the hallmark of hacky developers – see databinding. I compare this to nUnit. When I first heard about it I downloaded it, wired it up, played with it for a day and when done….told all my developers to get it and switch our testing methodology to be inline with this tool. nHibernate had no such impression. In a year or so I bet I try again to see if anything has changed about the technoogy or me that makes it become a must for me.
-Joe
@Joe – Fair enough.
Just Say No to Manual CRUD…
…
[...] June: SQL Is for the Birds: We finally take the ORM plunge at work and decide to use Castle’s Active Record as a replacement for our custom data access layer. I manage to get a good month of development time in before the project gets temporarily shelved to work on some legacy projects that took a higher priority. It was sad to have to return to writing raw SQL again, but the experience was positive enough to convince me to that ORM’s have progressed too far to still be spending time on doing manual CRUD. [...]