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.