DEV Community: The Juanito Learns Show

Expose SQL Logic as APIs - Domain Driven Design in SQL

The Juanito Learns Show — Thu, 29 Jun 2023 01:06:00 +0000

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.

Expose SQL Logic as APIs

The Juanito Learns Show — Tue, 27 Jun 2023 01:49:00 +0000

HTTP-based APIs have become a very popular way of exposing functionality to external entities outside an organization. For example, in order to support certain features of their products, some Vendors require integrating with your data real-time by calling HTTP APIs to get the information they need. Or maybe you have a very tech-savy customer that would like to get their data via APIs your organization exposes.

Typically, a Development Team spins up those APIs using programming languages and platforms such as .NET and Java. But what if your organization only has Microsoft SQL developers? In this series of posts, I would like to propose that they are able to encapsulate the needed logic into T-SQL stored procedures and/or views without the need of the work of a typical development team. This is great for those “multiple hats” DBAs who might not be strong enough in regular languages, but with good enough skills to do some basic managing in Azure.

Here is an overview of the process:

Design/Develop your database to address the use case. Follow Domain Driven Design guidelines in SQL as if it were a regular programming language by using separate schemas for public and private objects and keeping logic and db limited to one Bounded Context.
Publish your database to a SQL server that can be accessed by Azure. This could be an Azure SQL db, managed instance, Azure VM or an on-prem SQL db (if you have Network connectivity between Azure and your on-prem data center).
Build the HTTP-triggered Logic app. Logic Apps can be built using a graphical interface, no need for code except for some formulas. This logic app will need to call your SQL server. There are many safe ways to authenticate the Logic App with your SQL server. The logic app will take care of transforming the data from the Stored Procedures/Views
Expose the Logic App via Azure APIM. This product will also allow you to add extra features to your API such as rate-limiting, subscription keys, analytics without code except for some XML and JSON (JSON for defining your OpenAPI schema).
Implement security in layers , by:
1. Limiting what can access your SQL server (the logic app)
2. Limiting what can access your Logic App (the APIM)
3. Setting up OAuth Client Credentials for your consumers by using Azure AD and APIM token validation policies.

The Azure offerings mentioned in the above list have either a free or reduced cost pricing option, making this approach inexpensive. In future posts we will dig deeper into each of the items in the list. For a video series please see: https://youtube.com/playlist?list=PLVObt25IiZ6gxbnlGMIDNa_iquKiBjUmo

Excel as the Business Rules Engine for your Application

The Juanito Learns Show — Fri, 19 May 2023 13:05:00 +0000

How can you allow a Business User to maintain the business logic of your application? If your business users are Excel-savvy (as much business users are), then you could use Microsoft’s Graph API to access and run the logic within those Excel workbooks (workbook resource type - Microsoft Graph v1.0 | Microsoft Learn).

This is not the OLE Automation of years past. We are talking about calling an HTTP based API.

The approach is simple, from the business user side:

Have the business users create the workbook and adjust it so that it performs the business rules it needs. They can designate some of the cells to be used as the “parameters” for the business logic. For example, given the values of cells A2 and B2, run a series of formula calculations whose final result is in cell B30.
Save the excel workbook in a folder in your business’ OneDrive.

Then, from your application code, make a series of HTTP calls to the MS Graph API:

Get the Auth Token
Call the createSession endpoint to create a non-persistent session. The non-persistent session will prevent changes from being saved but still allow you to get the calculated results within the session. E.g.:

POST https://graph.microsoft.com/v1.0/me/drive/root:/sources/public/excel-as-bz-rules-sample.xlsx:/workbook/createSession

Call the worksheets endpoint with a PATCH verb (make sure you pass the session id of your non-persistent session) and the values to use to update the cells your business user designated as the “parameters”. E.g.:

PATCH https://graph.microsoft.com/v1.0/me/drive/root:/sources/public/excel-as-bz-rules-sample.xlsx:/workbook/worksheets/Sheet1/range(address='A3')

Read the “results” cell(s) from the workbook by calling the worksheets endpoint with a GET. E.g.:

GET https://graph.microsoft.com/v1.0/me/drive/root:/sources/public/excel-as-bz-rules-sample.xlsx:/workbook/worksheets/Sheet1/range(address='B8')

    (Again, make sure you pass the session id of your non-persistent session)

Having your business rules encapsulated in an Excel workbook would allow your business users to ensure the business logic is always right. Please note that this approach requires the following:

The Business User is now responsible to ensure the logic is correct, which should be fine as they are the domain experts. What the business units are probably not familiar with is versioning of that logic. Thus, it is important to help them create a flow where the “ready” version of the Excel file gets delivered to the appropriate OneDrive folder so that unfinished or incorrect files are not used by the application.
You can still do integration testing from your code to test the accuracy of the business logic in an automated way. This means that you still need to talk to the business users and understand the success and failure conditions and create automated tests for that. This is not different from your unit tests on your domain logic code, with the exception that you are now calling APIs (i.e.: integration tests). But you should only need to do that testing when the business unit passes down in the flow a file ready to be used. Again, it is highly important to establish the proper “release” flow with the business.

Add Sql Server Database Projects To CI & CD pipelines

The Juanito Learns Show — Thu, 13 Apr 2023 01:03:00 +0000

Sql Server Database projects are a great way to define your database structure and keep in in source control. Instead of maintaining a large list of migration scripts, you can instead define your database declaratively in the Sql Project as if you were creating a .net solution. This project style also facilitates the publishing of changes to Sql Servers. The publish process automatically detects what needs to change and creates the necessary Sql code to perform those changes.

One of the challenges of using Sql Server Database projects is to include the Sql Project in your CI/CD pipeline so that it can automatically publish the changes to a Sql Server.

Microsoft has migrated the Sql Server Database project format to the new “SDK style” project (introduced by .NET core).

A generic way to integrate it is to call the commands from the command line as most CI/CD pipelines support some sort of command line invocation. The steps to run from the pipeline are:

Build the project so that it generates a dacpac file
Install the SqlPackage dotnet tool
Run the SqlPackage tool with the “publish” option to publish the dacpac to the desired Sql Server.

The actual commands look like the following (assuming your database is named MyDb and your project is called MyDb.sqlproj):

Cd <folder where your sql project is>
dotnet build /p:NetCoreBuild=true
dotnet tool install -g microsoft.sqlpackage
SqlPackage /Action:Publish /SourceFile:"C:\AdventureWorksLT.dacpac" /TargetConnectionString:"ConnectionString to the db you want to publish to” /UniversalAuthentication=True

The “/UniversalAuthentication=True” is required if you need to use integrated windows authentication to authenticate with the target sql server, i.e. if you use Integrated Security=True in your connection string.

References

Build a Project from the Command Line - Azure Data Studio | Microsoft Learn

SqlPackage Publish - SQL Server | Microsoft Learn

Can use Integrated Security via .net but not via JDBC

The Juanito Learns Show — Tue, 09 Nov 2021 00:29:00 +0000

I encountered an obscure issue when trying to connect to a SQL Server database using Windows Integrated Authentication from Java. My JDBC URL was correct but I kept getting the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

And the SQL logs in the SQL server were showing the following:

SSPI handshake failed with error code 0x80090346, state 46 while establishing a connection with integrated security; the connection has been closed. Reason: The Channel Bindings from this client are missing or do not match the established Transport Layer Security (TLS) Channel. The service might be under attack, or the data provider or client operating system might need to be upgraded to support Extended Protection.

The puzzling thing was that I was able to connect fine suing the system.data.sqlclient from .net and from SQL Server Management Studio from the same machine and domain account that was getting the JDBC error. In addition, Integrated Authentication was working fine when connecting to other SQL servers. So, it had to do something with that particular server. After searching in the internet, I found that the issue is due to the JDBC not implementing the Channel Binding feature needed to connect to SQL servers whose Extended Protection is turned on. The native SQL Client does implement that feature and because the system.data.sqlclient from .net and SSMS use the native SQL Client under the covers, the connection for those succeeds. The following link takes you to the issue in GitHub for this scenario: https://github.com/microsoft/mssql-jdbc/issues/963

How to debug workflow console projects in Visual Studio 2019 using Designer Breakpoints

The Juanito Learns Show — Fri, 07 Aug 2020 14:32:00 +0000

I ran into an issue with debugging a Workflow Foundation workflow by using breakpoints in the designer in Visual Studio 2019. The breakpoints in the designer were not being hit when running the console workflow program in debug mode.

A question posted by another member of the community at https://developercommunity.visualstudio.com/content/problem/846593/not-able-to-debug-windows-workflow-designer.html led me to another link at https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/retargeting/4.7.2-4.8#windows-workflow-foundation-wf.

In that last page, at the bottom, under the "Workflow XAML checksums for symbols changed from SHA1 to SHA256", I found the piece of configuration code to add to my app.config file in the console workflow VS project. The following is the full configuration of my project that allowed VS to pause at the designer breakpoints:

<?xml version="1.0" encoding="utf-8" ?>

<startup>

    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />

</startup>

<runtime>

    <AppContextSwitchOverrides value="Switch.System.Activities.UseSHA1HashForDebuggerSymbols=true" />

</runtime>