Why Not to write SQL Stored Procedures – Part 2

Previous part talked about why using Stored Procedures for writing business logic may be a bad idea. In this part we continue on this subject, but are going to look at the problem from architecture angle.

Typical Stored Procedures based application

Typical application which heavily uses SPs for business logic may look like this.

Most of the business logic is implemented by SPs, therefore database takes most of responsibilities. Sometimes applications like this also have heavy UI which takes a lot of responsibilities too and may duplicate business logic implemented in SPs. Service layer can be very thin and serve as a connector between UI and database. In this case it plays a role of path through layer. However if UI layer is capable of connecting to database directly then service layer may not even exist. So three-tier application becomes two-tier.

What is wrong with design like that?

How to integrate

Applications typically do not live in isolation. They need to talk to each other in order to performs tasks. For example, an eCommerce website may have an orders system that talks to warehouse system to check inventory and tax system to calculate sale tax for an order.

If we have all business logic written in SPs, we have several options to make systems talk to each other.

Database to Database

Multiple RDBMS support a notion of schema. Schema can be thought as a namespace within a database or a way of grouping related Database objects. This approach describes Database to Database or DB schema to DB schema communications.

Applications can access each other databases (or schemas) directly by calling SPs. This approach has several significant problems:

  • Coupling on database level. In case one application needs to have a change in a Stored Procedure interface, the other application’s database may need to be change as well. Because Database is the lowest level, the change may ripple through both systems which is very costly and tedious.
  • The dependencies between different databases or schemas may not be so obvious to see, unless there are tools to help with identifying dependencies.
  • Exposure of Database tables. If permissions are not setup carefully then one application may start directly accessing other application’s tables. It is very tempting and easy to take a shortcut and just join a table from another database or schema even though it doesn’t belong to the application. And now we have even more tightly coupled applications.

Shared Database

This design inherits all disadvantages of database to database integration and adds more to it due to lack of clear boundaries between applications.

Shared database design may happen in a monolith application or poorly designed microservice architectures. With no boundaries between applications even a simple change in any part may cause changes in other parts or if not careful even break other applications. This increases cost of changes and maintenance dramatically.

UI to UI

UI to UI integration is also possible by sharing libraries, packages or components between applications. While sharing libraries and packages with basic UI building blocks (buttons, drop-downs, menu items and etc.) brings certain advantages if used wisely. For example, styles and basic component designs can be shared among verity of applications creating unified appearance for entire system.

However integrating on UI layer while having business logic implemented in Stored Procedures has it’s own problems.

Imagine we have two applications which share a UI form. The form has 2 variations, one application needs 7 fields in the form but another application needs only 5. We have implemented all business logic behind the form in a few Stored Procedures. What issue we can encounter down the road?

  • The 7 vs 5 fields variation makes it’s footprint in every layer of the shared form (UI, Service and Database) because all business logic (validation, persistence and etc.) resides in SPs. Store Procedure which saves the form field may need to have 7 parameters (one per field) where 2 will be optional to accommodate 5 fields scenario. Same for service layer.
  • Increased complexity in all layers with more variations. What if we need to add another variation of the same form with 8 fields? Now service layer and the SPs need to be changed to have 8 parameters instead of 7.
  • More duplicated code. What if we decide to split and have two forms with 7 and 5 fields instead of adding new variation? Because business logic resides in Stored Procedures we will have to have 2 service layer endpoints and 2 SPs, one for each form.

How to scale

Having most of business logic implemented in Stored Procedures limits options to scale. Unless RDBMS supports horizontal scaling we would not be able to increase performance and robustness by scaling horizontally (multiplying instances of running database). The only option we would have is to scale vertically or increase “size” of a machine where the database runs, e.g. add more memory, faster CPU(s), faster disk. However with this method we will have a limit of how far we can go. The larger machine is the more expensive it becomes and the cost rises faster than the size.

How to make it right

Let try to improve our design and try to stop writing SPs with business logic. Let’s move all business logic from database SPs to service layer. Here is how our application looks like now.

The changes are pretty significant.

  • Now we have Core service. It is an integration point or our application. It implements API other applications can integrate with. Core service implements all business logic required. The interface of Core service can be fine-grained allowing more granular interactions with our system.
  • We have UI Service. The API is course-grained with primary focus to serve User Interface. Out Core service fine-grained API might not be perfect for our UI, because in some cases we may need to make multiple calls to Core service for a single UI action. For example, we have a UI dashboard which needs multiple collections of data. To load all data for the dashboard we can implement a single request in UI service which will aggregate data from multiple sources, including Core service and return it as a single response.
  • We have a database as storage. Yes, we think about database as storage only. It’s primary responsibilities are to store data, ensure data integrity (using constraints) and performance of operations with data. No more business logic in database Stored Procedures.
  • We have UI which can be anything (Web, Mobile or Desktop). Responsibility of UI is obviously to interact with user. UI can also implement simple validation of data entry (e.g. non-blank form fields, formats of emails or phone numbers) and may be minimum of business logic if it’s absolutely needed.

It is fair to say that Core service and UI service can be merged if User Interface can handle fine-grained API of Core service and does not need any “back-end” help.

How to integrate

Integration with other systems is a breeze as we have a dedicated Core service for that. We can use other application’s API services so as other applications can use our Core service.

How to scale

Scaling is also a piece of cake. It’s built into our design. If we need to increase performance of our system we can

  • Add more instances of Core and UI services. If we followed The Twelve-Factor App recommendations or just have stateless services then horizontal scaling is our friend.
  • If database becomes a bottleneck of our application we can add a layer of caching with Redis or other caching technology.
  • If we have Web UI then some of the static content can be delivered though CDN.

Summary

Summary can be as short as “Do not wring business logic in Stored Procedures”. Having business logic in SPs may work for some time but it will likely limit your options in the future when you need to integrate with other applications or scale your application horizontally. Scaling relational database is not as easy as scaling stateless service. Integrating applications in database level can lead to chaos and code difficult to work with.

As always, alternative opinions are welcome!

Posts created 20

One thought on “Why Not to write SQL Stored Procedures – Part 2

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

Begin typing your search term above and press enter to search. Press ESC to cancel.

Back To Top