I said goodbye to Stored Procedures

In this article I will explain why I do not need them that much

Photo by Megafilm on Freepik

Back in the days many business applications that were using SQL databases, tended to have part of the logic in stored procedures. On the one hand, this may increase the performance (or naturally we tend to believe so), but on the other hand, it can make maintenance (versioning and testing) or migration to a different vendor much more difficult. In the past, I also built applications (mostly desktop) that used loads of stored procedures. It worked and for some time it seemed to be just OK for what I needed.

Generally, I am in favour of splitting the logic layer from the database (data tier), so the database acts as a raw data container. In such scenarios, I build multi-tier applications which should be a much more flexible solution.

The bottom line is to have infrastructure logic separated from business logic.

In this article, I will explain the reasons for saying goodbye to stored procedures. First, however, I will present some basics.

Application logic

Application logic — a broader term, is the logic that allows an application to work. It can be split into:

Infrastructure logic — part of the code that allows the program to perform tasks, execute actions, connect to a database(s), etc., an example being:

If a user hovers the mouse pointer over the button, then change the button colour.

Business logic — a part of application code that usually makes sense for the user in the context of the business. Example being:

If the customer buys two products or more, apply a discount. If not, don’t apply for the discount.

Both logic types should never be mixed.

Note: business logic is sometimes referred to as domain logic, and thus the term domain knowledge was also coined. Domain knowledge is simply knowledge about a business (i.e. a rule stating that the outgoing payment should always be approved by two senior managers).

Usually, so-called business applications follow client-server architecture (also known as multi-tier architecture or n-tier architecture), where business logic is separated from infrastructure logic.

Common layers in n-tier architecture

• Presentation layer (UI/view layer).
• Application layer (service layer or GRASP).
• Business layer (business logic, domain layer).
• Data access layer (persistence layer).

The most common n-tier is 3-tier

• Presentation tier (displays information).
• Application tier (business logic, logic tier, or middle tier).
• Data-tier (database, file shares, etc.).

In web applications, we often have:

• A front-end web server serving content rendered by the browser. The content may be static or generated dynamically.
• A middle dynamic content processing and generation level application server (NET Core, Node.js, etc.).
• A back-end database comprising both data sets and the database management system software that manages and provides access to the data.

Business logic can be placed anywhere in a program, and many times may be found in different layers. As mentioned before, for a very long time, it was a popular decision to put a large part of the logic in SQL stored procedures.

This effectively means that the data layer contains a portion of the business layer, and in a 3-tier application, it can be viewed as a misuse as the application tier should contain business logic next to logic tier, or middle tier, etc.

Stored Procedures — the issues

Placing business logic in SQL databases as stored procedures have some serious consequences:

• Stored procedures are always written in some SQL dialect, which will make potential migration to another database difficult (or impossible), and therefore it ties an application to a specific vendor.
• It makes testing much more difficult.
• It also makes it more difficult to maintain the code, because SQL tends to be less readable as it gets more complex.
• Versioning may be a problem, even if the script is stored in the Visual Studio project using GIT, anyone can alter the procedure at any time, and no one may notice.

That apart, today many applications use Backend-As-A-Service (BaaS) infrastructure, this means that an application is not tightly connected with SQL server, and therefore application does not care about the data layer at all. It often uses REST for communication with cloud service; thus only cloud service is responsible for handling databases, data storage, etc. An additional advantage is that it simplifies database management and connections and allows applications to be built with any technology (mobile, desktop or web, any languages, and can be changed at any time, without affecting the database).

In such scenarios, the business logic tends to be in cloud service (i.e. build with NET Core / EF Core). Using ORM (with powerful LINQ) makes things easier for a programmer, however, there may be use-case when SQL has to be written, then the question arises, should we prepare SQL string and pass to a database for execution, or prepare stored procedure and call it (with optional arguments)? One would argue that here we should prefer stored procedures (because we should have better performance), this depends still on the use-case, however, a word from James McKay (.NET developer):

Stored procedures offer no performance advantage whatsoever. The (tiny) gains you get from passing only the name of the stored procedure over the wire as opposed to a SQL string are easily offset by the fact that you are all too likely to end up calling the same procedure two or three times with the same parameters in the same request, whereas an ORM would look in its Identity Map and say, “Hey, I’ve already retrieved that one, no need to do another round trip at all”. Furthermore, the claim that stored procedures are cached on the server, whereas ad-hoc SQL is not, is a myth that was busted by Frans Bouma in his blog post, Stored Procedures are bad.

Below I present some key points questioning stored procedures I also experienced myself:

• They may often require you to reinvent wheels. If you need to use design patterns like the ones described in Patterns of Enterprise Application Architecture or optimistic concurrency, or other complex solution(s), then you need to build such functionality yourself from scratch because SQL will most likely not give you any of it; while an ORM (with LINQ) can often provide such functionalities either out of the box or allow you to introduce it with ease. Furthermore, many times you may find yourself in a situation where you are reinventing these wheels using code that is copied and pasted, this is bad practice.
• They can be easily manipulated by anyone, including DBAs (but they are supposed to be able to change whatever they want/need, right?). If someone makes a change, then a version of your code that goes into production will not correspond to a single specific revision in source control, and what is worse, may not even correspond to any revision in source control at all. In other words: having SQL stored procedures and main code separated in different environments (data tier, logic tier), will always increase the risk of getting out of sync with your main codebase in one or more environments. Versioning is the real issue.
• They will most likely make you violating the DRY principle (which is bad development practice). The reason is, you have to type out the list of fields in your database table many times. This is a real problem if you need to add a single column to your database table. It is not possible to pass an object as a parameter to a stored procedure, you can pass only primitive types and date and time, making it virtually impossible to avoid large parameter lists.
• They require much more work to develop and maintain. You have to set out individual stored procedures for CRUD operations, and also a separate stored procedure for each different query that you need (stored procedures per database and triggers each table). In addition to that, once having stored procedures in place, you have to write your method(s) just to call into each stored procedure (most likely passing some arguments). But with most ORM, all you need is to write your class definitions, your database table, and a mapping file. In EF Core, you only need to generate classes for each table and one class for database context (scaffolding).
• They are not flexible, because If you want to query your data in several different ways, say paging, sorting, lazy loading (load when required) or eager loading (load when asked), etc., you will need to write loads of separate stored procedures for all the different use-cases. On the contrary, using ORM with LINQ allows you to be much more flexible.

I build business applications using .NET Core and Azure Cloud Services and in my experience so far, for CRUD operations, ORM is often sufficient enough, plus when necessary, we can send custom SQL for execution.

SQL Injection

It is said that stored procedures offer natural prevention from SQL injection because it separates data from instructions. This is true, and it is a certain advantage as long as a developer will not use dynamic SQL within stored procedure where a raw string is passed via the argument that replaces placeholder. As for ORM, we should always use parameterization for raw SQL queries. Nevertheless, regardless of the choice, one must always pay attention.

More details about this important topic can be found in my second article called simply “SQL Injection”.

Conclusion

In multi-tier applications, it may seem much more reasonable to rely on ORM (EF Core, Dapper or RepoDB, etc.) and LINQ than on logic written in SQL and placed in stored procedures.

In some edge cases where code generated by the ORM is insufficient (or the ORM lacks specific functionality), we may be better off with custom SQL (implicit or explicit with proper error handling) that can be also easily secured from SQL injection than stored procedure.

Thank you for reading this article! Please leave a comment should you have any questions or have a different experience.