Moving past stored procedures

On Chad’s recent SQL-assembly comparison post, a few interesting comments caught my eye proclaiming the glory of stored procedures.  From tom (no link):

[Stored procedures] are not only useful for speed but also for ACID and to keep business logic in a central place. (Business logic in clients is an idea as good as storing logic in js on webpages IMHO).

And Lars Pohlmann:

@tom: full ACK to your thoughts about stored-procedures.

That goes especially for environments, where you have different clients and architectures accessing data from and writing data to the database. You’ll need the logic at a central point to avoid code-duplication, and stored-procedures are the best way to do that, even if they are not that beautiful.

I thought that the world had moved past putting domain logic in stored procedures.  At least I had hoped so, as systems with domain behavior in the infrastructure (data) tier can be the worst systems to maintain.

Years ago, stored procedures were the de facto standard for data access tiers.  I remember most examples of data access coming out of Redmond using stored procedures.  Conventional wisdom has bucked this trend, but why?  Stored procedures seemed like a great way to encapsulate data access, so why are so many folks, both inside and outside of Redmond, putting so much energy in to ORM technologies?  Weren’t stored procedures supposed to solve this problem?

Way back when

I can’t speak of all developers, but my love of stored procedures started back in ASP 3.0 and SQL 7.0/2000 days.  Since VBScript was not an OO language, all domain behavior was expressed procedurally.  I would use the transaction script pattern for all of my application needs, with everything to perform an operation like AddUser or SubmitOrder in one long method.

Developing in VBScript can be quite a pain, so why not put all of my logic in a stored procedure?  It made sense from the application side, as it was very difficult to maintain ASP files with a bunch of VBScript everywhere.  VBScript just wasn’t expressive enough for the model we had.

With all of the domain logic in the stored procedure, we could work much closer to what we thought represented our system: database tables.  Since we were in an ecosystem whose primary language (VBScript) was not capable of the models in our system, like Customers, Orders, OrderItems, etc., it made perfect sense to put our logic in the system that did capture our models, the database.  We thought of an Order as a row in a table, the OrderTotal as the sum of the OrderItems, and the Customer as the foreign key on the Order table.

When you’re in a database-centric mindset, stored procedures make all the sense in the world.

A new modeling paradigm

With the introduction of .NET came the ability to create fully OO systems in the Microsoft world (I’m not counting C++, those guys are just plain nuts).  No longer would applications need to be driven by large amounts of procedural code, we could now have models expressed in software.  Clients of a system could now interact directly with our model, which provided a much greater expressiveness than SQL ever could.  The problems of stored procedures were stacking up:

  • Difficult to test, therefore hard to maintain
  • Procedural, not OO
  • Impedance mismatch between data model and conceptual model

The last part was key for me moving away from stored procedures.  Conversations with customers did not include a database diagram.  If it ever did, the conversation went very poorly, as customers do not think of Orders and Customers as database tables.  They think of them as entities with rules and behavior.  SQL is not nearly expressive enough to describe the behavior of these entities that would in any way satisfy the customer, or even make sense to them.

Domain behavior (or business logic) could now be kept in a central location, but this location is now the software model, not the data model.  Behavior related to an Order is actually on the Order.  If I want to know the behavior of the system, I just need to look at the unit tests.

Since I can use the full gamut of design patterns and modeling techniques in modern OO software, why not use that system?  Duplication is the scourge of maintainability, so why not use a system that can both model the customer’s domain and effectively eliminate duplication?

Your application is not your data

As Tom and Lars pointed out above, the database can be a point to centrally keep domain logic.  But they are very, very poor at expressing behavior or removing duplication.  An application goes well beyond data, it includes behavior as well.  Since TSQL can’t handle the rigors of OO modeling, where do we keep this logic?  If we have many different clients wanting to interact with our system (not just data), how do we allow this?  I’ll pick one of two ways:

  • Through objects
  • Through services

Both these options allow me to make changes to the data model without affecting clients.  If clients want complex aggregate reporting, reporting databases are perfect for that.  But no one is touching my data model except for systems that include the behavior behind that data.  Since databases are so poor at expressing such behavior, I’ll pick something far more expressive and maintainable.

About Jimmy Bogard

I'm a technical architect with Headspring in Austin, TX. I focus on DDD, distributed systems, and any other acronym-centric design/architecture/methodology. I created AutoMapper and am a co-author of the ASP.NET MVC in Action books.
This entry was posted in DomainDrivenDesign, Rant. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Perhaps the stored procedure conversation has come full circle now that we are talking so much more about “services”. After all, a set of stored procedures is simply a service layer on top of your data. So the question becomes “do you want to program your service layer in SQL, or in “.

  • @Steve

    Then I always fall back to what’s most maintainable. Oren writes:

  • Jeff’s second law of programming: Let the database do the work.

    Databases are way better than we will EVER be at things like sorting and joining. Those things are not business logic. I think that the business layer should drive how the db does those things, so if I want a set of records I tell the stored procedure how to sort them through parameters. This allows the database to do what it does best but still gives me control over what the database actually does in terms of business logic.

  • @Jeff: But how does any of that require a stored procedure?

    You can do sorting and joining without sprocs and get the same performance.

  • @Chad: I agree, it doesn’t require a stored proc, but my point was more that business logic doesn’t belong in sql, however things that databases do well don’t belong in your business domain code.

    As for stored procs themselves, I find that in the particular application that I am currently writing that my preference for managing database interaction is through stored procedures (so far) although I agree with your point about performance. I’ve found this to be true in several instances in the past as well. I’m working on a blog post that goes into a lot more detail about why I feel this way. I promise that I’ll post it this week.

  • To put my comment into perspective:

    I am an oracle-developer. You are talking about microsoft-centric development. I don’t know how SQL-Server handles stored-procs, but I heard, that it’s much worse than oracle.

  • @Lars

    Just curious, is an oracle-developer an Oracle DBA? This is the same approach on any DB I use, whether it’s Oracle, SQL Server, MySQL, etc. I don’t think there’s anything MS-centric about it, is there?

  • @bogardj: No, I’m not a DBA, I’m a pl/sql-Developer. And though I would have chosen another architecture, if I would have been the one to make the decision a few years ago, I can say that Oracle gives you some powerful tools to handle the business-logic within the database. And looking back, I’m glad this decision was made within my company. I wouldn’t try this with MySQL. Can’t say anything about SQL-Server as I don’t have any experience with it.

  • @Lars

    Ah ok, I see now. Our solutions are generally database-agnostic, and we never use DB-specific features. I’ve only needed to “dip the toe in the waters” when very specific scenarios pop up (i.e., hierarchical queries, etc.)

    I’d be very curious to see the differences between Oracle-centric applications and one centered around DDD principles…