Expose SQL Logic as APIs - Domain Driven Design in SQL
Tags: api, azure, ddd, domaindrivendesign
Published: 2023-07-23 01:46:38

---
title: Expose SQL Logic as APIs - Domain Driven Design in SQL
published: true
date: 2023-06-29 01:06:00 UTC
tags: API,Azure,DDD,DomainDrivenDesign
canonical_url: 
---

Most Domain Driven Design articles and books teach you to develop your business logic in such a way that there is no trace of a database or other dependency that would make your business logic (also known as Core code or Domain Logic) impure. That is great advice if you are developing your core business logic in a programming language that is _not SQL_. But what if all you know if SQL or SQL is your strong skill? The answer is yes, you can develop your domain logic in SQL in a domain driven way, if you try to adhere to the spirit of the DDD concepts.

## Objects or Tables?

One of the main arguments for developing Domain Logic in Object-Oriented languages is that “real” world things are objects and thus is easier to model those real-world things in classes. But if you are in an organization and you start talking to Business Units about the logic they need, you would find that the concept of “spread sheets” are actually very familiar to them. It is so familiar, that countless of Business Units across history have tried to solve their problems but modeling their logic in Excel Spreadsheets. Thus, rationalizing the problem domain at hand in terms of tables (a.k.a. spreadsheets) is not very far of how a businessperson thinks day to day when solving their problems.

It might even make your domain easier to explain to a Business Unit.

## SQL features friendly to DDD

Beyond tables, SQL allows us to also model **relationships**. Relationships are an important Business concept as “things” in Businesses do not exist in a vacuum.

Most SQL variants (such as T-SQL) also allows us to avoid [Primitive Obsession](https://blog.ploeh.dk/2015/01/19/from-primitive-obsession-to-domain-modelling/) via custom types that we can define to model Business values. For example, instead of using **varchar(50)** we can define a custom type **VendorName** and use it in our tables and stored procedures to make clear the value represents a Vendor’s name not just a random varchar with max length of 50 characters.

Another tool that SQL provides to ensure our domain modeling is enforced, is the use of [check constraints.](https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-check-constraints?view=sql-server-ver16)

Granted, a lot of the features we discussed are also found in OOO languages, but the point is that SQL _also_ offers them.

## One database, one Bounded Context

One popular advice in the Micro Services realm is that each Micro Service needs its own database that one that service can access. What if the _database itself_ is the service? And if we follow another popular opinion that a service should implement only one DDD Bounded Context ([Designing a DDD-oriented microservice | Microsoft Learn](https://learn.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/ddd-oriented-microservice)) , then it follows our database should only model one Bounded Context.

Does it mean we expose everything in the db as a service? No, instead we should have at least two schemas (or the equivalent of a MS SQL schema in other RDBMS systems):

- One schema that acts as our internal implementation that we can change at will (much like the _private_ members and classes in typical programming languages).
- And another schema that acts as our contract to the callers outside our database. This schema serves the purpose of the _public_ members/types in other programming languages. 
This collection of Stored Procs, types and Views that compose your “public contract” schema **should not change much** as it is what you are agreeing the callers can expect to interact with when working with the logic and data in your database. This will free you to make any changes you need to your objects in your internal schema because you know that as long as your public API schema is complying with the contracts established, your callers will not break.  

## Less Risky Deployments

One argument against database deployment is that they are risky. And sometimes they are because the databases that are being deployed are big in the sense that are trying to model multiple bounded contexts and thus the risk is that one thing can break another seemingly unrelated thing.

But the same is true if you are going to deploy a large application, if the application is one single unit of deployment. And just like the solution for reducing the risk of deployments in applications is to try to break the application into smaller, independent parts, we can mitigate this risk in the database world by limiting the database to modeling only one Bounded Context.

There are also ways to automate the deployments of the changes from a Repo, just like other programming languages have.

## Breaking Schema Changes

Some of you might be wondering: what about breaking changes? And that question might come to mind because you might have experience in the past a painful task of a database change that broke something. The question is how do other programming languages deal with breaking changes? The answer for them also applies to SQL: a versioning strategy.

For example in C#, there are three types of changes to a class: removal of members, changing types or names of members and addition of members. Additions usually do not break things, but changes and removals do. And although is OK in C# to do those destructive changes in the internal implementation of your service, you better not break your public classes that define your public contract. And if you do, the most common solution is to version the library or the classes themselves.

A similar solution can be applied to SQL. Your objects in your “private” schema can have destructive schema changes as long as your do not violate your public contract. As for data loss, if a change would incur data loss and the data needs to be retained, it could retained in a table whose purpose is to archive that data or to have a separate database whose mission is to keep an archive version of that data. It all depends on the requirements of the business for that data that would be lost. As for changes to your public contract, just like in services implemented in other languages, you would have to adopt a versioning strategy with either having a separate stored proc for older versions or a separate db altogether. It all comes down to what makes more sense for the specific situation.

## Unit Testing

Although not as polished as other languages, there are ways to create quick unit tests in SQL and have them run by a tool or command in the CI/CD pipeline. **But if you touch the db then is not a unit test anymore!** Well, technically, even though your unit tests might involve temporarily storing data into tables, your test are still self-contained in transactions. And they might not run as fast as their .NET counterparts, but they would be fast enough to give you a good test dev loop.

To get started with unit testing, you can start simple, by having a schema for Test objects. Then define in that schema your Stored procs that will implement the test. Inside those procedures, you can do the typical [Arrange, Act, Assert](https://learn.microsoft.com/en-us/visualstudio/test/unit-test-basics?view=vs-2022) steps for the test and wrap them in a transaction to isolate the test. To run the test, just run the stored proc.

## Conclusion

In a future post and video, we will try to show an example of a database that follows the above advice.