Introduction

I often come across questions about the implementation of the CQRS pattern. Even more often I see discussions about access to the database in the context of what is better – ORM or plain SQL.

In this post I wanted to show you how you can quickly implement simple REST API application with CQRS using the .NET Core. I immediately point out that this is the CQRS in the simplest edition – the update through the Write Model immediately updates the Read Model, therefore we do not have here the eventual consistency. However, many applications do not need eventual consistency, while the logical division of writing and reading using two separate models is recommended and more effective in most solutions.

Especially for this article I prepared sample, fully working application, see full source on Github.

My goals

These are my goals that I wanted to achieve by creating this solution:
1. Clear separation and isolation of Write Model and Read Model.
2. Retrieving data using Read Model should be as fast as possible.
3. Write Model should be implemented with DDD approach. The level of DDD implementation should depend on level of domain complexity.
4. Application logic should be decoupled from GUI.
5. Selected libraries should be mature, well-known and supported.

Design

High level flow between components looks like:

As you can see the process for reads is pretty straightforward because we should query data as fast as possible. We don’t need here more layers of abstractions and sophisticated approaches. Get arguments from query object, execute raw SQL against database and return data – that’s all.

It is different in the case of write support. Writing often requires more advanced techniques because we need execute some logic, do some calculations or simply check some conditions (especially invariants). With ORM tool with change tracking and using Repository Pattern we can do it leaving our Domain Model intact (ok, almost).

Solution

Read model

Diagram below presents flow between components used to fulfill read request operation:

The GUI is responsible for creating
Query
object:

Then, query handler process query:

The first thing is to get open database connection and it is achieved using
SqlConnectionFactory
class. This class is resolved by IoC Container with HTTP request lifetime scope so we are sure, that we use only one database connection during request processing.

Second thing is to prepare and execute raw SQL against database. I try not to refer to tables directly and instead refer to database views. This is a nice way to create abstraction and decouple our application from database schema because I want to hide database internals as much as possible.

For SQL execution I use micro ORM Dapper library because is almost as fast as native ADO.NET and does not have boilerplate API. In short, it does what it has to do and it does it very well.

Write model

Diagram below presents flow for write request operation:

Write request processing starts similar to read but we create the 
Command
object instead of the query object:

Then,
CommandHandler
is invoked:

Command handler looks different than query handler. Here, we use higher level of abstraction using DDD approach with Aggregates and Entities. We need it because in this case problems to solve are often more complex than usual reads. Command handler hydrates aggregate, invokes aggregate method and saves changes to database.

Customer aggregate can be defined as follows:

Architecture

Solution structure is designed based on well-known Onion Architecture as follows:

Only 3 projects are defined:

·        API project with API endpoints and application logic (command and query handlers) using Feature Folders approach.

·        Domain project with Domain Model

·        Infrastructure project – integration with database.

Summary

In this post I tried to present the simplest way to implement CQRS pattern using raw sql scripts as Read Model side processing and DDD approach as Write Model side implementation. Doing so we are able to achieve much more separation of concerns without losing the speed of development. Cost of introducing this solution is very low and and it returns very quickly.

I didn’t describe DDD implementation in detail so I encourage you once again to check the repository of the example application – can be used as a kit starter for your app the same as for my applications.

Written by: Kamil Grzybek – ITSG’s CRM expert, architect .net

Article Source: https://www.kamilgrzybek.com/design/simple-cqrs-implementation-with-raw-sql-and-ddd/