This week has been a bit of a surprise. First I got some questions about NHibernate, which I realized I have been blessed from not using since around 2011, then today I got into a small discussion about stored procedures and that it’s viable to let it contain logic. I’m well aware that this is a religious question and that you probably find your self in on camp or the other. Me, I’m more in the camp of no business logic in SPs, but I can see an use-case for it as well. But then again, I’m flexible and try to look at each situation objectively.
Years ago I was in the situation of more or less being responsible for setting the architecture of two projects that both had really tight deadlines. Focus was on pipe-lining data with some cleaning and some aggregation of data. An ETL process. It was two batch oriented systems, where data was processed at given times during each month. Both projects where delivered in time and was providing business value from day one. Technologies where selected with regards taken to staffing and competence in the two projects. The first one was using in SSIS and the second one was a mix between C# and SPs, where all data processing naturally lied in SPs. There was a external party in the organization, wanting us to have used NHibernate instead.
I left the organization and bumped into a friend about a year later. I asked about the health of the systems:
- Friend: “Well there were performance issues.”
- Me: “What?!?!? Where? Why? How?”
- Friend: “Well, not with our stuff, but the peer projects that used NHibernate.”
Now, this is not about picking on NHibernate, this is about for the context, selecting a proper solution. So with this said, I can understand logic in DBs if it’s part of an ETL process piping lots of data. But when it comes to “one-and-one” business transactions where logic is being kept in the database, that is for me just wrong. Especially if you plan to use that as some sort of shared logic between bounded contexts since this gives you coupling in logic between to contexts that might force one or the other to change. In code this can be handled a lot differently and more importantly be versioned and have a more fine grained testing. When it comes to testing logic in databases, we did this in the above mentioned projects. We had integration and regression tests against our SPs but this merely ensures that the process works. The tests becomes to abstract/high level making them harder to understand and especially when they fail.
Summary
It’s been a couple of years ago now, and I’m not using that stack anymore and my general opinion is to not have logic in SPs. I would look at them as a map-reduce projecting and aggregating data, perhaps having simple classification of data as well. I would most certainly never use it as a tool for sharing logic and definitely not as an integration hub.
//Daniel