Simple CQRS implementation with SQL and DDD
Introduction
In this post I want to show you how you can quickly implement simple REST API application with CQRS using .NET Core.This is the simplest edition - the update through the Write Model immediately updates the Read Model, therefore we 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.
Design
High level flow between components looks like:
As you can see the process of 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 SQL against database and return data - that's all.
It's 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 invariant). With ORM tool with change tracking and using Repository Pattern we can do it leaving our Domain Model intact.
Solution
Read Model
Diagram below presents flow between components used to fulfill read request operation:
The GUI is responsible for creating Query object:
/// <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);
}
{
public Guid OrderId { get; }
public GetCustomerOrderDetailsQuery(Guid orderId)
{
this.OrderId = orderId;
}
}
Then, query handler process query:
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;
}
}
}
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 SqlConnectonFactory 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 SQL statements 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 we 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
Write request processing starts similar to read but we create the Command object instead of the query 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:
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;
}
}
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 follow:
public class Customer : Entity
{
public Guid Id { get; private set; }
private readonly List<Order> _orders;
private Customer()
{
this._orders = new List<Order>();
}
public void AddOrder(Order order)
{
this._orders.Add(order);
this.AddDomainEvent(new OrderAddedEvent(order));
}
public void ChangeOrder(Guid orderId, List<OrderProduct> products, IReadOnlyCollection<Product> allProducts)
{
var order = this._orders.Single(x => x.Id == orderId);
order.Change(products, allProducts);
this.AddDomainEvent(new OrderChangedEvent(order));
}
public void RemoveOrder(Guid orderId)
{
var order = this._orders.Single(x => x.Id == orderId);
order.Remove();
this.AddDomainEvent(new OrderRemovedEvent(order));
}
}
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 architecture is designed based on Onion Architecture as follow:
Only 3 projects as defined:
- API project with API endpoints and application logic (command and query handlers)
- 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 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.
