Why using stored procedures is not recommended for modern applications

The list of cons of keeping business logic in database

I have worked on several large-scale projects that relied heavily on databases to store critical business logic, as well as on several smaller projects. One of these projects was for a major logistics company that used Oracle, while the other was for a pharmaceutical company that conducted studies and certifications on medicine, which used MS SQL Server 2003.The version of MS SQL Server can give you an idea of how long ago this experience was. Both projects had hundreds of stored procedures, with CI/CD and built processes for rolling out changes to production. We had to follow a strict script writing process to ensure that we could concatenate scripts, write upgrade schema and data patch scripts separately, and test everything on restored backups and testing environments. We even used tools like SQL Delta or Redgate SQL Compare to produce a migration script.

However, working with databases always presented challenges. I have reflected on these challenges and compiled a list of issues that stored procedures and logic in databases may cause for modern applications.

Disadvantages of Utilizing Stored Procedures

1. The deployment process becomes more complex as it involves multiple components, such as the application itself and the database. When the database is not under the control of the development team, coordinating changes and updates can become difficult.
2. Moving the business logic to stored procedures can make testing and verifying the code difficult. Writing unit tests becomes cumbersome, and the procedures tend to be tightly coupled to the database environment, making them less portable and adaptable.
3. The way the business logic is organized is spread out over different layers of the system, such as the data and application layers. As a result, it’s more difficult to keep track of, implement, and understand the overall system architecture.
4. Stored procedures are recognized for being more challenging to maintain and troubleshoot. Rectifying problems and altering the logic within procedures can consume time and is prone to errors.
5. If you rely too much on stored procedures, it can make your database schema more complex. This can make modifying the schema more difficult, which could cause problems and inconsistencies.
6. Stored procedures can cause database bottlenecks, which can limit the application’s scalability. As the stored procedures become more complex and increase in volume, they can negatively affect the database’s performance, which in turn impacts the application’s overall scalability and responsiveness.
7. Using stored procedures can lead to a lock-in effect, as they tightly bind the application to one particular database. This can make switching to a different database more challenging and may necessitate significant modifications and rewrites.
8. Versioning and deploying stored procedures can be difficult. It is important to manage different versions and handle conflicts during deployment to avoid errors and inconsistencies in the system.
9. Adding stored procedures to a continuous integration/continuous deployment (CI/CD) pipeline makes the overall process more complex. Making sure stored procedures integrate and deploy smoothly with the application code requires extra thought and practices.

In summary, although stored procedures can be beneficial in certain situations, using them in modern applications often poses challenges related to deployment, testing, maintenance, scalability, platform lock-in, versioning, and CI/CD processes.