Command Query Responsibility Segregation (CQRS) vs. Create, Read, Update, and Delete (CRUD): 5 Key Comparisons

1. Implementation

At runtime, the base class takes the command. After the appropriate object handler, such as create, update, or delete, is created, a method to execute the command is called.

The base class records the method being called both before and after the execution. This leads to creating a log from which records can be retrieved and to any time point.

Once the interface and dispatch code exist, a simple computer program, perhaps a for loop, would be required to take the events and replay them.

Here, the program must read a file from a specific point, after which, for each line, a call is made to the command on that line along with the data on it.

While the command handler is complex on the inside, this complexity is limited or encapsulated.

Handlers are responsible for calling each process step to create, delete, or update a logical item within the system. They can dispatch the first request, monitor the completion of the action, and handle errors and rollbacks as required, i.e., the handler executes a long-running two-phase commit, also known as a saga pattern.

Once the handler completes the transaction, or if the transaction has failed or another error has occurred, the results are added to the event log.

A system that primarily creates entries and tracks updates using the above method will eventually form an event log capable of recording all system changes.

The creation of such a log in a consistent way, supporting read and replay functions by a program, enables event sourcing.

1) The create operation is used for entry creation. Examples of entries include user accounts, contact details, posts, tasks, etc.

POST is the HTTP protocol that implements a CREATE function, while SQL database users create using INSERT. Meanwhile, NoSQL databases create using the insert() method.

2) The read operation gives users access to the entries or inputs over the user interface. Simply put, read means to ‘see’ the entries created earlier.

Read access could translate to the user accessing created entries right after their creation, searching for specific entries, and so on.

GET is used to implement a READ operation in the HTTP protocol. SQL databases read using the SELECT entry. NoSQL databases such as MongoDB read with the find() or the findById() method.

3) Update operations allow users to modify or edit existing data.

The HTTP protocols to implement an update operation are PUT and PATCH, depending on the user’s needs. PUT is useful when an entire entry must be updated, while PATCH is for when the entire entry does not need to be modified.

SQL databases use UPDATE to update entries, while NoSQL databases can implement an update operation using the findByIdAndUpdate() method.

4) Finally, the delete operation is to get rid of entries from the database and the user interface.

In both HTTP protocol and SQL databases, DELETE is the operation to delete an entry. In NoSQL databases, delete can be implemented using the findByIdAndDelete() method.

2. Operations

Separation of issues is handled through interfaces.

CQRS cannot be built automatically using the scaffold technique.

Finally, CQRS features better performance and security due to the separation of concerns.

Read and write operations have a chance of being unsynchronized.

Implementation can be automated by the use of scaffolding code generated through tools.

Management of security and permissions is more difficult because both read and write operations are permitted.

3. Use Cases

When a registered user looks at an order, the process is reasonably straightforward: a database read operation.

This read will likely be from a NoSQL key-value store such as Redis. All the data about the user is stored in this key-value for easy access. The information is then picked up by a microservice, after which it’s displayed on a webpage.

The read side is straightforward and can be carried out entirely by the key-value store team in collaboration with front-end developers. Thus, the ‘read’ responsibility of CQRS is decoupled from the ‘write.’

However, the write operation is much more complicated and has numerous dependencies and steps.

Let’s say our user has canceled an order before it could be shipped. Now, the return must be canceled in the cache and returned to the central system of record.

If the enterprise uses a data warehouse, the record must be deleted from the warehouse and auxiliary systems. Additionally, teams handling the physical warehouse and logistics would need to be notified to stop the order from being shipped.

Even processes such as credit card charges being reversed, stocking and inventory counts being corrected, and refill orders being canceled need to be carried out.

The above example, where one change is copied to several different places, is commonplace in today’s digital world. In this example, the create, update, and delete operations must interact with an enterprise service bus (ESB) or a command handler, such as the saga pattern.

CQRS is the system capable of bringing the logic required to handle these complex yet common transactions.

Let’s take the same example of a user’s online order journey as we did for CQRS. In this example, we will assume that the CRUD functions use HTTP requests.

1) Create: After logging into the ecommerce website, our user browses all the products available for purchase and taps on the product of their choice.

They enter their shipping and payment information, which is then correlated to the database model table.

Once the information is submitted, a POST request is sent to the API, and the order is stored in the database.

2) Read: Now that the order is confirmed, the user should see the confirmation on their page.

This is where read, the main functionality that allows the other operations to be used comes in. The API mentioned above would allow the user to view the order confirmation on their page.

If all the active orders placed need to be viewed, a GET request would be used to view active orders without any changes being made to the data stored on the API.

3) Update: Our user now wants to change the address entered earlier because they just remembered they’re going on vacation.

Luckily, the order is fresh, and there’s still time to change the shipping address. For our user to do so, the corresponding HTTP method for updating the address, PATCH, would be used. This would replace the current data of the target order with the updated content (new shipping address).

The order is targeted using a unique ID to ensure only the specified order is updated while leaving any other active orders untouched.

4) Delete: The shipping address has been updated, but our user finds out that their airline has filed for bankruptcy and their tickets have been canceled.

With their vacation plans in mayhem, they need to cancel the order for now.

The HTTP method, DELETE, would be used here to remove all instances of the active order. The unique ID would be used in this case as well.

4. Benefits

In fact, as read operations tend to be more frequent than writes, using CQRS could help reduce response latency by placing the data sources for reads in strategic geolocations to optimize performance.

Decoupling read and write operations also leads to more streamlined scaling of storage capacity according to real-world usage.

The separation of command and query components in CQRS enables polyglot persistence. This means that multiple persistence technologies are enabled to preserve and drive the strengths of each component.

With the support of CQRS, application components can be tweaked for optimal performance, throughput, and scalability. This is because CQRS separates the command and query operations, allowing applications to be fine-tuned in a targeted manner and for enhanced performance of data reads and writes.

Finally, improved separation of concerns and increased security (due to the prevention of data being exposed in the wrong context) are the benefits of CQRS.

The execution plan for a stored procedure is captured as part of the SQL server’s procedure cache. It can then be reused for all successive calls to the procedure.

When the SQL server executes an SQL statement, the relational engine inspects the procedure cache to determine if an execution plan for that specific SQL statement exists.

If the plan does exist, it is used to decrease the need for SQL statement parsing, recompiling, and optimization. If an execution plan does not exist, the SQL server will put together a new one for the query.

Apart from this, removing SQL statements from the application code means that the storage of all SQL can take place in the database. This would leave only stored procedure calls in the client application. Additionally, the use of stored procedures can help minimize database coupling.

Finally, CRUD operations can offer enhanced protection against SQL injection attacks. This is because all SQL statements leverage stored procedures rather than string concatenation for generating dynamic queries from user input data, meaning all the entries into a parameter are quoted.

5. Challenges

For one, supporting the CQRS pattern requires expertise in numerous database technologies. A variety of database technologies are needed to use CQRS patterns. This means higher costs, either in terms of cloud utilization or on-premise hardware.

Additionally, ensuring data consistency means that special consideration is required regarding service level agreements.

Finally, using numerous databases means exposing several points of failure. Therefore, enterprises using CQRS must invest in comprehensive monitoring and failsafe mechanisms to ensure adequate business continuity.

For instance, difficulties in effective scaling, especially when dealing with vast data volumes, are a challenge CRUD users face.

Besides this, maintaining data consistency can be difficult, especially when multiple users are simultaneously updating the same data.

Finally, more complex applications will likely face difficulties in managing CRUD operations. This could require increased efforts toward development and maintenance.

Addressing these challenges calls for enterprises to experiment with alternative data management architectures, such as CQRS, as these might offer greater scalability, data consistency, and performance.