Command–query separation (CQS) – an easy, powerful pattern, CQRS And ES

The idea behind CQS is to separate the query part (the read part / fetching-the-data-part) from the command part (the write part / doing-things-with-the-data-part). This enables you to optimize both parts in different ways. You are able to split the data flow into different optimized pipes.

The other most important benefit of it is, that this approach enforce you, to split your business logic into pretty small peaces of code. This is because each command and each query only does one single thing:

• fetching a specific set of data
• executing a specific command

The fundamental idea is that we should divide an object's methods into two sharply separated categories:

• Queries: Return a result and do not change the observable state of the system (are free of side effects).
• Commands: Change the state of a system but do not return a value.

Because the term 'command' is widely used in other contexts I prefer to refer to them as 'modifiers', you also see the term 'mutators'.

CQS says your command methods should not return anything (be void) and only mutate the object state. Query methods should return results and query method calls should be immutable, query method does not change the object state. This is it.

CQRS is "splitting a model object into two". You will have one model to write and one model to read, which are completely separate. You might store your data in the same database, but where you write to using your commands is separated from where you read from using your queries, they use different models (write and read).

CQRS is an architectural pattern. CQS is more of a class design principle.

Event Sourcing -->

In essence event sourcing is about persisting data in a way that preserves every single bit of information. It’s about representing objects as a sequence of events that took place through the time and led them to the current state.

Event Sourcing and CQRS

The CQRS pattern is often used along with the Event Sourcing pattern. CQRS-based systems use separate read and write data models, each tailored to relevant tasks and often located in physically separate stores. When used with the Event Sourcing pattern, the store of events is the write model, and is the official source of information. The read model of a CQRS-based system provides materialized views of the data, typically as highly denormalized views. These views are tailored to the interfaces and display requirements of the application, which helps to maximize both display and query performance.

Using the stream of events as the write store, rather than the actual data at a point in time, avoids update conflicts on a single aggregate and maximizes performance and scalability. The events can be used to asynchronously generate materialized views of the data that are used to populate the read store.

Because the event store is the official source of information, it is possible to delete the materialized views and replay all past events to create a new representation of the current state when the system evolves, or when the read model must change. The materialized views are in effect a durable read-only cache of the data.

When using CQRS combined with the Event Sourcing pattern, consider the following:

• As with any system where the write and read stores are separate, systems based on this pattern are only eventually consistent. There will be some delay between the event being generated and the data store being updated.
• The pattern adds complexity because code must be created to initiate and handle events, and assemble or update the appropriate views or objects required by queries or a read model. The complexity of the CQRS pattern when used with the Event Sourcing pattern can make a successful implementation more difficult, and requires a different approach to designing systems. However, event sourcing can make it easier to model the domain, and makes it easier to rebuild views or create new ones because the intent of the changes in the data is preserved.
• Generating materialized views for use in the read model or projections of the data by replaying and handling the events for specific entities or collections of entities can require significant processing time and resource usage. This is especially true if it requires summation or analysis of values over long periods, because all the associated events might need to be examined. Resolve this by implementing snapshots of the data at scheduled intervals, such as a total count of the number of a specific action that have occurred, or the current state of an entity.

Dynamic SQL vs Parameterized dynamic SQL and SQL Server Service Broker

DECLARE @parameter integer = 1;

-- Dynamic SQL
DECLARE @sql nvarchar(max) =
    N'
    SELECT *
    FROM AdventureWorks2008R2.Production.Product AS p
    WHERE p.ProductID = ' + CONVERT(varchar(12), @parameter);

EXECUTE (@sql);

-- Parameterized dynamic SQL
EXECUTE sys.sp_executesql
    @statement =
        N'
        SELECT *
        FROM AdventureWorks2008R2.Production.Product AS p
        WHERE p.ProductID = @ProductID;',
    @params = N'@ProductID integer',
    @ProductID = @parameter;

Dynamic sql is simply building a executing against your database. This is open to sql injection. Parameterized query is using variable to hold the values for your query. The big advantage is the query engine will use the supplied value as the datatype and as such is not prone to sql injection. 


============================================================================

SQL Server Service Broker

Service Broker came with SQL Server 2005 that provides messaging and queue functionality between instances. Basic function is sending and receiving messages.

Broker conversation is considered as a Dialog between two participants.

Service Broker can send messages to different databases or different servers. It uses TCP/IP to send messages.

Service Broker is useful to separate applications such as separate Audit Logging.

Setup a Service Broker

Following are the steps to setup Service Broker.

Step 1: Enable Service Broker for the database. By default, it has been disabled.

-- Enable Service Broker

ALTER DATABASE ServiceBrokerDB SET ENABLE_BROKER

GO

Step 2: Create a Message Type. This is a template for messages that you are sending.

-- Create Message Type

CREATE MESSAGE TYPE SBMessageType_TableChange VALIDATION = NONE;

Step 3: Create a Contract that is used to Send and Receive messages. 

-- Create Contract

CREATE CONTRACT SBContract_TableChange (SBMessageType_TableChange SENT BY INITIATOR);

Step 4: Create two Queues to send and receive messages. The Sender Queue is used to send messages and Recipient Queue to poll messages. 

-- Create Send Queue

CREATE QUEUE dbo.SBSendQueue_TableChange

-- Create Receive Queue

CREATE QUEUE SBReceiveQueue_TableChange

Step 5: Create two Services for each Queue. To create a service, we need Queues and Contract. 

-- Create Services

CREATE SERVICE SBSendService_TableChange ON QUEUE dbo.SBSendQueue_TableChange(SBContract_TableChange);

CREATE SERVICE SBReceiveService_TableChange ON QUEUEdbo.SBReceiveQueue_TableChange(SBContract_TableChange);

Now the Service Broker has been created. Now check to send a message. 

Send & Receive Messages

Send a Message

-- Send Message

DECLARE @conversation uniqueidentifier, @msg varchar(max)='My Message 1';

--- Start a conversation using DIALOG:

BEGIN DIALOG @conversation

    FROM SERVICE SBSendService_TableChange

    TO SERVICE N'SBReceiveService_TableChange'

    ON CONTRACT SBContract_TableChange

    WITH ENCRYPTION=OFF;

--- Send the message

SEND ON CONVERSATION @conversation

       MESSAGE TYPE SBMessageType_TableChange (@msg);

View Messages available in the Receive Queue. 

-- View messages from Receive Queue

SELECT CONVERT(VARCHAR(MAX), message_body) AS Message, message_type_name

FROM SBReceiveQueue_TableChange

GO

You can see messages any number of times. Since it is not pulling, messages are not removing from the Receive Queue.

Receive (Polling) Messages from the Receive Queue

-- Receiving Message

DECLARE @conversation uniqueidentifier, @senderMsgType nvarchar(100), @msg varchar(max);

WAITFOR (

    RECEIVE TOP(1)

        @conversation = conversation_handle,

        @msg = message_body,

        @senderMsgType = message_type_name

    FROM SBReceiveQueue_TableChange), TIMEOUT 1000;

       IF @conversation IS NULL

              BEGIN

                     return;

              END   

SELECT @msg AS RecievedMessage, @senderMsgType AS SenderMessageType;

END CONVERSATION @conversation;

GO

One you receive the messages, it will remove from the queue. 

Add incoming messages to a Table using a Stord Procedure

In here I’m going to write a stored procedure to run automatically when message is received to the Queue. The stored procedure insert that message to an another table.

This is called “internal activation”.

Create a Table

-- Create a Table

CREATE TABLE dbo.TableChangeMessage

(

       MessageId int IDENTITY(1,1) NOT NULL,

       DateInserted datetime NOT NULL,

       SBMessage varchar(max),

       CONSTRAINT PK_TableChangeMessage PRIMARY KEY (MessageId)

)

GO

Create a Stored Procedure to Insert messages to the “TableChangeMessage” table.

-- Create a Stored Procedure

CREATE PROCEDURE dbo.ReadTableChangeMessage

AS

BEGIN

       DECLARE @conversation uniqueidentifier, @senderMsgType nvarchar(100), @msg varchar(max);

       WAITFOR (

    RECEIVE TOP(1)

        @conversation = conversation_handle,

        @msg = message_body,

        @senderMsgType = message_type_name

              FROM SBReceiveQueue_TableChange), TIMEOUT 1000;

       IF @conversation IS NULL

              return;      

       IF (@senderMsgType='SBMessageType_TableChange')

       BEGIN

              INSERT INTO dbo.TableChangeMessage (DateInserted, SBMessage)

              SELECT GETDATE(), @msg;

       END

       END CONVERSATION @conversation;

END

GO

Modify Receive Queue to run above Stored Procedure every time a message arrives. 

-- Modify Receive Queue

ALTER QUEUE SBReceiveQueue_TableChange WITH ACTIVATION (

    STATUS = ON,

    PROCEDURE_NAME = dbo.ReadTableChangeMessage,

    EXECUTE AS SELF,

    MAX_QUEUE_READERS = 1);

GO

Now check weather data is available in the table.

SELECT * FROM [dbo].[TableChangeMessage]

GO

Send Messages to Service Broker when Insert/Update/Delete operation

In this case, I’m sending the updated table name to the Service Broker. (Send Messages)

Create two tables

-- Create table Custome

CREATE TABLE [dbo].[Customer](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [Name] [varchar](100) NULL,

       [Address] [varchar](500) NULL,

       [Telephone] [varchar](50) NULL,

       [Email] [varchar](50) NULL,

       [DateUpdated] [datetime] NOT NULL DEFAULT (getdate()),

       [UserUpdated] [varchar](50) NOT NULL DEFAULT ('admin'),

       CONSTRAINT [PK_Customer] PRIMARY KEY ([Id])

)

GO

-- Create table Employee

CREATE TABLE [dbo].[Employee](

       [Id] [int] IDENTITY(1,1) NOT NULL,

       [Name] [varchar](100) NULL,

       [NIC] [varchar](50) NULL,

       [Designation] [varchar](100) NULL,

       [DateUpdated] [datetime] NOT NULL DEFAULT (getdate()),

       [UserUpdated] [varchar](50) NOT NULL DEFAULT ('admin'),

       CONSTRAINT [PK_Employee] PRIMARY KEY ([Id])

)

GO

Create a Stored Procedure to send messages to Service Broker

-- Stored Procedure to Send Message.

CREATE PROCEDURE dbo.Send_TableChange_Message

       @TableName nvarchar(100) = N''

AS

BEGIN

       DECLARE @conversation uniqueidentifier, @msg varchar(max) = @TableName;

       --- Start a conversation:

       BEGIN DIALOG @conversation

              FROM SERVICE SBSendService_TableChange

              TO SERVICE N'SBReceiveService_TableChange'

              ON CONTRACT SBContract_TableChange

              WITH ENCRYPTION=OFF;

       --- Send the message

       SEND ON CONVERSATION @conversation

              MESSAGE TYPE SBMessageType_TableChange (@msg);

END

Create two Triggers to Send Messages to Service Broker

-- Trigger to trigger for Customer table to execute the SP.

CREATE TRIGGER TRG_Customer

ON dbo.Customer

FOR INSERT, UPDATE, DELETE 

AS

DECLARE @TableName NVARCHAR(100) = 'Customer';

EXEC dbo.Send_TableChange_Message @TableName;

GO

-- Trigger to trigger for Employee table to execute the SP.

CREATE TRIGGER TRG_Employee

ON dbo.Employee

FOR INSERT, UPDATE, DELETE 

AS

DECLARE @TableName NVARCHAR(100) = 'Employee';

EXEC dbo.Send_TableChange_Message @TableName;

GO

Now add values to the “Customer” and “Employee” tables. Then check the “TableChangeMessage” table. 

SELECT * FROM [dbo].[TableChangeMessage]

GO

You can see the values are inserting when you Add, Update or delete values to “Customer” or “Employee” tables.