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:

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:

// 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 && 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:

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:

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.

Comments