Loading...
Skip to Content

Blog

Post: Simple CQRS implementation with raw SQL and DDD

Simple CQRS implementation with raw SQL and DDD

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:

CQRS flow

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:

ReadModel flow

The GUI is responsible for creating Query object:

// Query object creating

/// <summary>
/// Get customer order details.
/// </summary>
/// <param name="orderId">Order ID.</param>
[Route("{customerId}/orders/{orderId}")]
[HttpGet]
[ProducesResponseType(typeof(OrderDetailsDto), (int)HttpStatusCode.OK)]
public async Task<IActionResult> GetCustomerOrderDetails(
    [FromRoute]Guid orderId)
{
    var orderDetails = await _mediator.Send(new GetCustomerOrderDetailsQuery(orderId));

    return Ok(orderDetails);
}
// GetCustomerOrderDetailsQuery
internal class GetCustomerOrderDetailsQuery : IRequest<OrderDetailsDto>
{
    public Guid OrderId { get; }

    public GetCustomerOrderDetailsQuery(Guid orderId)
    {
        this.OrderId = orderId;
    }
}

Then, query handler process query:

// GetCustomerOrderDetialsQueryHandler
internal class GetCustomerOrderDetialsQueryHandler : IRequestHandler<GetCustomerOrderDetailsQuery, OrderDetailsDto>
{
    private readonly ISqlConnectionFactory _sqlConnectionFactory;

    public GetCustomerOrderDetialsQueryHandler(ISqlConnectionFactory sqlConnectionFactory)
    {
        this._sqlConnectionFactory = sqlConnectionFactory;
    }

    public async Task<OrderDetailsDto> Handle(GetCustomerOrderDetailsQuery request, CancellationToken cancellationToken)
    {
        using (var connection = this._sqlConnectionFactory.GetOpenConnection())
        {
            const string sql = "SELECT " +
                               "[Order].[Id], " +
                               "[Order].[IsRemoved], " +
                               "[Order].[Value] " +
                               "FROM orders.v_Orders AS [Order] " +
                               "WHERE [Order].Id = @OrderId";
            var order = await connection.QuerySingleOrDefaultAsync<OrderDetailsDto>(sql, new {request.OrderId});

            const string sqlProducts = "SELECT " +
                               "[Order].[ProductId] AS [Id], " +
                               "[Order].[Quantity], " +
                               "[Order].[Name] " +
                               "FROM orders.v_OrderProducts AS [Order] " +
                               "WHERE [Order].OrderId = @OrderId";
            var products = await connection.QueryAsync<ProductDto>(sqlProducts, new { request.OrderId });

            order.Products = products.AsList();

            return order;
        }
    }
}
// SqlConnectionFactory
public class SqlConnectionFactory : ISqlConnectionFactory, IDisposable
{
    private readonly string _connectionString;
    private IDbConnection _connection;

    public SqlConnectionFactory(string connectionString)
    {
        this._connectionString = connectionString;
    }

    public IDbConnection GetOpenConnection()
    {
        if (this._connection == null || this._connection.State != ConnectionState.Open)
        {
            this._connection = new SqlConnection(_connectionString);
            this._connection.Open();
        }

        return this._connection;
    }

    public void Dispose()
    {
        if (this._connection != null &amp;&amp; this._connection.State == ConnectionState.Open)
        {
            this._connection.Dispose();
        }
    }
}

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:

WriteModel flow

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

// Create command object

/// <summary>
/// Add customer order.
/// </summary>
/// <param name="customerId">Customer ID.</param>
/// <param name="request">Products list.</param>
[Route("{customerId}/orders")]
[HttpPost]
[ProducesResponseType((int)HttpStatusCode.Created)]
public async Task<IActionResult> AddCustomerOrder(
    [FromRoute]Guid customerId, 
    [FromBody]CustomerOrderRequest request)
{
   await _mediator.Send(new AddCustomerOrderCommand(customerId, request.Products));

   return Created(string.Empty, null);
}

Then, CommandHandler is invoked:

// AddCustomerOrderCommandHandler
public class AddCustomerOrderCommandHandler : IRequestHandler<AddCustomerOrderCommand>
{
    private readonly ICustomerRepository _customerRepository;
    private readonly IProductRepository _productRepository;

    public AddCustomerOrderCommandHandler(
        ICustomerRepository customerRepository, 
        IProductRepository productRepository)
    {
        this._customerRepository = customerRepository;
        this._productRepository = productRepository;
    }

    public async Task<Unit> Handle(AddCustomerOrderCommand request, CancellationToken cancellationToken)
    {
        var customer = await this._customerRepository.GetByIdAsync(request.CustomerId);

        var selectedProducts = request.Products.Select(x => new OrderProduct(x.Id, x.Quantity)).ToList();
        var allProducts = await this._productRepository.GetAllAsync();

        var order = new Order(selectedProducts, allProducts);
        
        customer.AddOrder(order);

        await this._customerRepository.UnitOfWork.CommitAsync(cancellationToken);

        return Unit.Value;
    }
}
// Order entity
public class Order : Entity
{
    public Guid Id { get; private set; }
    private bool _isRemoved;
    private decimal _value;
    private List<OrderProduct> _orderProducts;

    private Order()
    {
        this._orderProducts = new List<OrderProduct>();
        this._isRemoved = false;
    }

    public Order(List<OrderProduct> orderProducts, IReadOnlyCollection<Product> allProducts)
    {
        this.Id = Guid.NewGuid();
        this._orderProducts = orderProducts;

        this.CalculateOrderValue(allProducts);
    }

    internal void Change(List<OrderProduct> products, IReadOnlyCollection<Product> allProducts)
    {
        foreach (var product in products)
        {
            var orderProduct = this._orderProducts.SingleOrDefault(x => x.ProductId == product.ProductId);
            if (orderProduct != null)
            {
                orderProduct.ChangeQuantity(product.Quantity);
            }
            else
            {
                this._orderProducts.Add(product);
            }
        }

        var existingProducts = this._orderProducts.ToList();
        foreach (var existingProduct in existingProducts)
        {
            var product = products.SingleOrDefault(x => x.ProductId == existingProduct.ProductId);
            if (product == null)
            {
                this._orderProducts.Remove(existingProduct);
            }
        }

        this.CalculateOrderValue(allProducts);
    }

    internal void Remove()
    {
        this._isRemoved = true;
    }

    private void CalculateOrderValue(IReadOnlyCollection<Product> allProducts)
    {
        this._value = this._orderProducts.Sum(x => x.Quantity * allProducts.Single(y => y.Id == x.ProductId).Price);
    }
}

Architecture

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

Solution

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.

Solution dependencies

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.

Comments

Related posts See all blog posts

Domain Model Validation
4 March 2019
In previous post I described how requests input data can be validated on Application Services Layer. I showed FluentValidation library usage in combination with Pipeline Pattern and Problem Details standard. In this post I would like to focus on the second type of validation which sits in the Domain Layer – Domain Model validation.
Read More
Feature Folders
3 December 2018
Today I would like to suggest a less-common, but in my opinion a much better way to organize our codebase. Meet the Feature Folders.
Read More
Handling Domain Events: Missing Part
18 June 2019
Some time ago I wrote post about publishing and handling domain events. In addition, in one of the posts I described the Outbox Pattern, which provides us At-Least-Once delivery when integrating with external components / services without using the 2PC protocol. This time I wanted to present a combination of both approaches to complete previous posts. I will present a complete solution that enables reliable data processing in the system in a structured manner taking into account the transaction boundary.
Read More
Processing commands with Hangfire and MediatR
29 September 2018
In previous post about processing multiple instance aggregates of the same type I suggested to consider using eventual consistency approach. In this post I would like to present one way to do this.
Read More
REST API Data Validation
20 February 2019
This time I would like to describe how we can protect our REST API applications from requests containing invalid data (data validation process). However, validation of our requests is not enough, unfortunately. In addition to validation, it is our responsibility to return the relevant messages and statuses to our API clients. I wanted to deal with these two things in this post.
Read More