Skip to main content

Expose SQL Logic as APIs - Domain Driven Design in SQL

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 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.

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) , 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 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.

Comments

Popular posts from this blog

How to create online multiplayer HTML5 games in Contruct2

  Construct2 can use websockets to send and receive messages between games. By using socket-io , we can use a Node.js script as the server and my modification to the socket-io plugin for Construct2 to allow the games to synchronize data between them in real-time. There are two parts to this design: the Node.js server and the Construct2 clients (the games playing). The main part of building an online multiplayer HTML5 game is to plan: how the clients will communicate how often and what to communicate how much of the logic will go into the server and how much to the client. In my sample game, I chose to have each client own a player and have the server just relay messages: Use string messages in the form TypeOfMessage, Parameter1, Paremeter2, Parater3, etc to communicate. Have the clients send their player position about 16 times a second. Whenever their player shoots, the client needs to send a message immediately. Almost all of the game logic will...

Power Automate: SFTP action "Test connection failed"

When I added an SFTP create file action to my Power Automate flow ( https://flow.microsoft.com ) , I got the following error in the action step, within the designer: "Test connection failed" To troubleshoot the Power Automate connection, I had to: go the Power Automate portal then "Data"->"Connections"  the sftp connection was there, I clicked on the ellipsis, and entered the connection info It turns out, that screen provides more details about the connection error. In my case, it was complaining that "SSH host key finger-print xxx format is not supported. It must be in 'MD5' format". I had provided the sha fingerprint that WinScp shows. Instead, I needed to use the MD5 version of the fingerprint. To get that, I had to run in command line (I was in a folder that had openssh in it): ssh -o FingerprintHash=md5 mysftpsite.com To get the fingerprint in MD5 format. I took the string (without the "MD5:" part of the string) and put ...

Mail labels and letter templates for jasperreports

The following are free (MIT license) mailing labels and letter templates for jasperreports that you can download and use in jasperserver and/or ireport: Update 3/15/2011 : I moved the Mail templates zip file here . Please consider making a small donation if the templates are of help to you, Thank you! If you need more information on how to use those templates please leave a comment in the blog.