Is SQL a good place for business logic?

November 11, 2015

<TL;DR> No, it isn’t. While SQL is a Turing-complete language and we can use it to encode any business logic we want, placing business (domain) logic into SQL leads to a less maintainable solution comparing to one that uses an OO or functional language. Because of that, I advocate to limit the use of SQL to read-only queries (which can potentially contain business logic, that’s fine) and simple CRUD statements where possible.</TL;DR>

The dispute regarding whether or not one should place business logic in SQL takes place for as long as I can remember. It’s much less active these days, but still exists, so let’s elaborate on the details here.

What is wrong with placing business logic into SQL?

Basically, the SQL itself.

Let’s take an example. Let’s say we have the following model in our application:

SQL business logic: domain model

In other words, there are two entities: Student and Teacher, which share some common data (the Name field) and also have some unique traits (AverageMark and Salary).

Now, let’s say we need to create a method that returns a description for a person. Here’s a table we can create to represent this model:

CREATE TABLE dbo.Person
(
       PersonID int NOT NULL PRIMARY KEY,
       Name nvarchar(200) NOT NULL,
       Type int NOT NULL, -- 1 for students, 2 for teachers
       AverageMark float NULL,
       Salary decimal(18, 2) NULL
)

And here’s how this method can be implemented in SQL:

CREATE PROCEDURE dbo.sp_GetPersonDescription
  @PersonID int
AS
DECLARE @Name nvarchar(200)
DECLARE @Type int
DECLARE @AverageMark float
DECLARE @Salary decimal(18, 2)
 
SELECT @Name = Name,
       @Type = [Type],
       @AverageMark = AverageMark,
       @Salary = Salary
FROM dbo.Person
WHERE PersonID = @PersonID

IF @Type = 1
  SELECT 'Student ' + @Name + ' with average mark ' + CAST (@AverageMark as varchar(10))
ELSE
  SELECT 'Teacher ' + @Name + ' with salary ' + CAST (@Salary as varchar(10))
 
-- Usages:
EXEC sp_GetPersonDescription 1 -- Returns: Student John with average mark 95
EXEC sp_GetPersonDescription 2 -- Returns: Teacher Jack with salary 60000.00

Now, compare it to C#:

public abstract class Person
{
    public string Name { get; set; }
    public abstract string GetDescription();
}
 
public class Student : Person
{
    public double AverageMark { get; set; }
 
    public override string GetDescription() => 
        "Student " + Name + " with average mark " + AverageMark;
}
 
public class Teacher : Person
{
    public decimal Salary { get; set; }
 
    public override string GetDescription() => 
        "Teacher " + Name + " with salary " + Salary;
}

Which one is easier to understand? I bet your answer is not "the first one" :)

And that is just a simple method returning some description, nothing more. Imagine how hard it would be to encode this business rule using SQL: "If the person is a teacher and their class is the best in the school, then increase their salary by 20% and assign 2 other teachers for mentorship".

I’m not saying it’s impossible (it surely is!), but the level of effort required to implement and, more importantly, to support and maintain such a solution is much higher than it is using regular OO (or FP) languages.

The reason for that is simple: SQL is not designed for the work with individual rows. It becomes more apparent if you take into account the fact that the IF statement wasn’t even in the standard initially. It was added later, starting from SQL-99.

OLTP vs OLAP

There are basically two ways to work with data: OLTP and OLAP. OLTP (online transaction processing) stands for processing individual transactions which usually involve little amount of data. This kind of processing aims at high throughput and low latency for each operation.

An example here might be a typical web application. When you perform an action (say, add an item to the shopping cart), the system processes your request by creating a new product line and, perhaps, logging your action somehow.

On the contrary, OLAP (online analytical processing) is an approach for operating relatively large groups of data. A good example here is a background job creating analysis reports. A search method looking for data given particular criteria is another one (although it’s not a "true OLAP operation" per se).

As you might guessed, SQL is about OLAP and not about OLTP. SQL is the language optimized for operating sets of data.

SQL is a horrible choice when it comes to encoding the business rules of your system. It isn’t flexible enough and we can’t rely on it to express any more or less complex domain model because it lacks the ability to create proper abstractions.

And that is the single most important reason why we shouldn’t use it as a place for business logic.

It’s not about the lack of tooling or support for modern development practices such as versioning control, unit testing and so on. There are quite a few products on the market that allow us to do that. It’s all about the inability to build a simple and expressive domain model. OO (as well as functional) design gives us much more opportunities for that than the procedural design SQL leave us with.

Transaction Script design pattern is all we have with SQL. It works when the application you are developing is simple enough, but blows up right after it reaches some (pretty low) complexity mark.

Ideal use cases for the SQL language

Ideally, we should limit the use of SQL to these two scenarios:

Simple select/insert/update/delete (CRUD) queries with no business logic in them
Read-only queries with some business logic

Simple CRUD operations mean we should treat our database as a dump bag of data and use SQL just to fetch this data into the memory and save it back to the DB. Data processing should occur in the more suitable place - our application code:

Ideal use case for SQL

The other use case for SQL is read-only SELECT queries. Even with the advent of LINQ providers in C# and type providers in F#, it is sometimes hard to write a query of comparable performance in the application code. In such situations, we have to fall down to the ground and use plain old SQL.

It’s not usually a problem because read-only queries don’t mutate data in the database and thus add up little maintenance overhead. Also, this practice perfectly aligns with the CQRS principles. That is the use of separate read and write models for performance reasons.

Not so ideal use cases for the SQL language

A not so ideal use case for SQL is write queries containing some business logic inside. As we discussed earlier, it’s a good practice to avoid such queries but the truth is, it’s not always possible.

For example, we might have a requirement to disable the accounts of all our clients if they pass the due for their payment plan. In this case, it would be much easier to execute a single UPDATE statement for all the clients at once, without loading them into the memory and processing one by one.

My advice in such situation would be: do use such a statement where appropriate, but try to limit their presence and simplify business rules encoded in them as much as possible.

Summary

Let’s summarize:

SQL is a bad place to keep the business logic in
Try to limit the use of SQL in such a way that:
- SQL queries are simple CRUD queries with no business logic in them, or
- SQL queries don’t mutate data in the database