DB Queries in code or use Stored Procedures

Question

Some of my colleagues tells me that using stored procedures in the database adds to much business logic in the database, and that you should keep the data separate from logic. Other colleagues tells me that adding DB Queries in source code as strings is not only hard to change, but also poses a security risk.

I'm leaning towards to put stored procedures in the database, as long as you only use them for simple tasks like getters and setters and avoid business logic in the database.

I know its possible to add an ORM layer in between the database and the logic using Entity Framework or similar, which may solve both problems. But I'm still not sure which way is the best in this scenario.

Answer 1

There's a few things to consider here, which I'll try to explain in enough detail to help you understand the problem space. Personally, for RDMS', I prefer using stored procedures, reasons for which I'll go into in a moment.

---

In any discrete application layer, there is a need to ensure that the boundary of that layer has sufficient controls in place to prevent dirty data from entering the layer, which could then cause corruption. This is the case regardless of whether the layer is an API exposed publicly, an internal API (e.g. front controller negotiating with services), or calls to an external layer (e.g. communicating with the data store). In DDD this is called the Anti-Corruption Layer, in OOP it goes by a few names (Design By Contract, Assertions, Pre-/Post-Conditions), and it can take as simple a form as strongly typing input and output variables. In any case, the purpose of this layer is to ensure that the layer can consistently and predictably enforce communication standards for input and output operations.

What does this have to do with stored procedures vs. ad hoc SQL?

Allowing ad hoc SQL to be executed against the database is a form of cowboy coding, in that the SQL query itself is not governed, the only measure of it's validity is whether or not it executes successfully, and the only safety net in place is wisdom (of the application) by the developer and/or code reviewer. Combine this with a latent notion that no business logic should go into the database (i.e. no constraints or triggers to ensure data structure remain intact), and you begin introducing risks to data integrity. An example of this is an object being saved to database that needs to be updated/inserted in multiple tables - if you miss an insert statement or two, you could end up with invalid, meaningless, or orphaned data. If you used stored procedures, you could ensure that all operations of that type adhere to the requirement to insert/update all relevant rows.

The bone I have to pick with the notion that business logic does not belong in the database is twofold:

1. A database is tightly coupled with the application utilising it as it's data store. To consider the database and the application as separable is not possible - one may not meaningfully exist without the other.
2. Business logic is comprised of two main ideas: business rules, and data logic. Business rules belong in the application, data logic belongs in the data store.

My first point should be reasonably obvious and require no further explanation. My second point requires some expansion though...

With the advent of NOSQL and DDD, it is clear that modern data and data usage is increasing in complexity, and in many cases data is being thought about in terms of rich entities that have their own internal structure and governing rules. The rules around what makes a rich object valid are it's business rules. How the data is stored is the data logic. Data logic, or how the object hangs together in storage, is entirely different to whether that form is sensible in the context of business rules. Your data architect may think that a certain object should be stored in the database as a set of rows across multiple tables, or one row as a JSON string - either way the object remains valid in the context of business rules. Data storage concerns are typically different to business rules concerns, and revolve around things like:

• How fast will a CRUD operation be, and will it run risks of locking or delays?
• How efficient are SELECT operations on the data?
• How many indexes, statistics tables, and other optimisations will be required, and what impact will they have on write operations?
• Is the data uniquely identifiable?
• Is the data stored efficiently given the data types available?

Answers to these questions may change at a different rate or at different times to enhancements in the application. By separating out data logic from business rules, you empower the data administrator to choose the data schema and functionality that is best fit for purpose. By exposing data interactions (both get and set) only via stored procedures, you can retain a consistent interface for the parent application whilst allowing the data administrator the freedom to make changes to the underlying data structure as they see fit.

What needs to be considered though is how much capacity does the business (read: IT Dept) have to support ideological design decisions. It may be the case that you are a small company, where one person does the front end, back end, and database work. You may be in a large organisation where there are teams assigned to each application layer. The less people working on the code, the more broad knowledge they have (q.v. Information Silo), which can lead to a lack of assertions between layers, as the developer knows everything about the process and neglects to include logical assertions. But, (whether large or small) the more indirection and assertions you implement in your code, the longer it takes to write and maintain. You have to weigh up the theory with the practice, idealism and pragmatism. On top of this, you need to consider whether the application has a limited lifetime, what skill sets staff have, how they are managed, how tightly the SDLC is governed, and above all people's personalities and coding habits. This is the horrible and scary world of Technical Leads and Senior Management.

---

While think about it, here is an example that demonstrates where business rules can validly fit into the database, and rightly sit there. This isn't to be cantankerous, but to demonstrate that aphorisms such as "Business logic does not belong in the database" have exceptions, no matter how noble their idea is:

Let us suppose we have a client management system where people's details are stored, and changes to those details are stored in an audit log table. We want a new function in the application where a moderator can flag a client as having not provided tax details for this FY, which has the flow-on effect of taxing all transactions at the top rate. The developer codes up this new functionality but neglects to include an insert into the audit table. They then pass it to a senior for peer review, and for one reason or another it passes peer review and enters production. A few months later a client complains that they are being erroneously charged the maximum taxation rate. The root cause of the problem is the developer - but there was a business user who performed the update incorrectly, which is an indication of a gap in knowledge or training. This cannot be addressed as there is no record in the audit log table stating which user performed the update and when. The underlying code issue can be solved, but only time will tell when the operational issue can/will be addressed.

This problem was a result of ad hoc SQL that performed it's own CRUD operation on a database without considering the company policy that "All changes to data must be recorded for auditing purposes". The database did not complain, because an UPDATE statement that sets the invalildTaxFileDetails column to 1 is entrirely valid in the context of the BIT data type. Yet, the data has now lost it's integrity, which could result in financial and/or reputational risk to the company. In some cases, this may go entirely unnoticed, and clients may be overcharged for their entire engagement period, and then take their business elsewhere due to competitive costing, without realising that they are even entitled to a refund.

Answer 2

The important thing is to have an abstraction layer between the raw SQL and the business logic.

In the old days this would be a stored proc. All the apps would connect directly to the db and call the sproc rather than running random SQL.

When the database changed, you could update the sproc and the apps would carry on working.

When you had new sql to run the creating a sproc process would ensure that you considered existing sproc reuse and performance of the db.

However, these days the abstraction layer is more likely to be a repository class or an api wrapping a repository.

This gives you the same separation benefits and allows you access to more powerful functions and the scalability of code running off the database box.

Here keeping the SQL with the code can ease development, allowing you to have multiple api versions deployed on a single db, etc.

Arguments based around it being 'easier' to change sprocs are a fallacy. What people mean is they are not applying the same change controls on their database as you are on your code base.

I shouldn't be able to 'just change' a sproc. I should have the schema in source control, migration scripts, staging servers, unit testing etc etc

On balance I go with parameterised SQL in code. Its just as fast and keeps database changes, which I find more problematic, to a minimum.

While its true that database engineers are often tempted into writing more and more complex sprocs which become a nightmare to debug and slow the db down. Its not inherent to sprocs, its just a 'when you have a hammer everything looks like a nail' problem

Answer 3

See Databases: Where should the application logic run?

It depends on the amount of data it concerns and how complicated the request is. Security and privacy are other aspects. Think of applicable laws like the European General Data Protection Regulation (GDPR) as well. If your database is compliant and some applications not you need to make the latter compliant as well. Here is a good reason for stored procedures.