Simple application deployment using CQRS pattern with raw SQL and DDD

Tram Ho


I often get questions about implementing the CQRS ( Command Query Responsibility Segregation ) pattern. Even more often, I find discussions about ORM or pure SQL database access which one is better.

In this article, I want to show you how to quickly deploy a REST API application with the CQRS pattern used. NET Core . I immediately pointed out that this is the simplest CQRS version. Updating the Write Model immediately updates the Read Model , so we won’t have a final agreement here. However, many applications do not need final agreement, while the logical division of writing and reading using two models is recommended and more effective in most solutions.

Especially in this article I have prepared a demo, the application works fully, see the source code on Github .

My purpose

Here is the destination I want to achieve in the demo application:

  1. Clearly splitting and isolating Write Model and Read Model .
  2. Receiving data using the Read Model should be as fast as possible.
  3. Write Model should be implemented with DDD ( Domain Driven Design ) approaches. The level of DDD deployment should depend on the complexity of the domain.
  4. Application logic should be separate from the user interface (GUI).
  5. Choose libraries carefully, famous and supported.


Flow between components looks like this:

As you can see the processing for reading is quite simple because we should make data queries as quickly as possible. We do not need here abstractions and troublesome approaches. Get arguments from the query object, execute plain SQL statements against the database, and return data. That is all there is to it.

It is different from the case of recording. Recording often requires more than advanced technologies because we need to perform some logic, do some calculations or simply check the conditions (especially things that don’t change). With the ORM tool with change tracking and using the Repository Pattern we can keep the Domain Model intact.


Read model

The diagram below shows the flow between components used to handle the operations that require reading data:

The user interface is responsible for creating a Query object:

Next, the query handler processes the query:

The first thing is to open the database connection and to get there we use the SqlConnectionFactory class. This is the class IoCContainer by IoCContainer with the HTTP request lifetime scope so we can make sure we only use one database connection while processing the request.

The second thing is to prepare and execute pure SQL statements against the database. I try not to reference the tables directly and instead refer to the database views. This is a good way of creating abstraction and separating our application from the database schema because I want to hide as much of the database as possible.

In terms of SQL execution I use the small ORM Dapper library because most of it is as fast as native ADO.NET and doesn’t have APIs available. In short, it does what it needs to do and it does it very well.

Write model

The chart below shows the flow of processing, which records:

Processing the write request starts similar to reading but we create a Command object instead of a query object :

Next, CommandHandler is called:

The command handler looks different than the Query handler . Here, we use the higher level of abstraction, using the DDD approach with Aggregates and Entities . We need it because in this case the problems to solve are often more complex than the data reader. The command handler calls the aggregate method, saving the changes to the database.

Customer aggregate is defined as follows:


Solution Solution is designed based on the emerging Onion Architecture as shown below:

There are 3 projects defined:

  • API projects with API endpoints and application logic (command and query handlers) use the Feature Folders approach
  • Domain project with Domain Model
  • Infrastructure project – integration with the database


In this article I tried to show the simplest way to implement CQRS pattern using pure SQL to handle Read model side and DDD approach for Write Model implementation. In doing so we can achieve more than the splitting of concerns without losing the pace of development. The cost of this solution is very low and it returns results very quickly.

I do not describe DDD deployment in detail, so I recommend you once again review the source code of the demo application and can use it as a starting point for your application.

Thank you for watching.

Article translated from source:

Share the news now

Source : Viblo