Practical Microservices Development Patterns: CRUD Vs. CQRS

There are several patterns out there for handling data in microservices development. The most familiar one being the CRUD pattern.

In this pattern, the basic data operations that can be performed on any data set (Create, Read, Update, and Delete) are brought forward to the application level as an interface to access data. This pattern is applicable to a large number of use cases and has been implemented extensively.

However, it’s not appropriate for all situations. In some cases, you may need to isolate the read and write models and handle them separately.

This can be achieved by following the CQRS (Command Query Responsibility Segregation) pattern. CQRS is frequently used along with the event sourcing pattern, which tracks the mutations done on your data set as discrete events.

This allows you to have all the information throughout time on how your solution’s current status came to be.

In this article, we will look at how each of these patterns works and build case studies to demonstrate their capabilities and differences. We will use the Ballerina programming language to implement these scenarios to display how its extensive features assist in the development of microservices.

CRUD

The CRUD pattern basically models our service operations by shadowing the operations we are doing with our data store. We expose these operations directly as the service interface. This pattern is mostly applicable to business domains that are simple and don’t have complex relationships between domain entities and their operations. In order to get a better idea of the concept, and to analyze the pattern, let’s take a look at a simple scenario of implementing the operations of a school’s student registry. 

Case Study: Student Registry

The use cases required for this system are simple. You need to be able to add new students, update student information, get student information, and remove student information from the system. We will be using a relational database to create the data model. Let’s look at how the ER (Entity/Relationship) model looks like. 

Figure 1: Student Database ER Diagram

Here, we simply have a single relation in our database, which represents the Student entity. Let’s see how we can create a service to expose the student registry functionality based on the Student table above.  

Listing 1: Student Registration Service - Create/Read Functionality

Listing 1 shows how the create and read operations are implemented in our CRUD service. The resources

addStudent

getStudent

POST

GET

SQL INSERT

SELECT

getStudents

GET

Listing 2: Student Registration Service - Update/Delete Functionality

Listing 2 shows how the two HTTP resources are mapped to the

PUT

DELETE

Sample Execution

We can see the above service in action by building the Ballerina project, running it, and sending out the respective HTTP requests for each resource invocation. 

Build

$ ballerina build -a
Compiling source
    laf/student_registry:0.1.0
Creating balos
    target/balo/student_registry-2020r1-java8-0.1.0.balo

Running Tests

    laf/student_registry:0.1.0
    No tests found

Generating executables
    target/bin/student_registry.jar

Database Creation

$ mysql -u <user> -p < student_registry.sql

Service Deployment

$ ballerina run target/bin/student_registry.jar
[ballerina/http] started HTTP/WS listener 0.0.0.0:8080

Service Invocation Requests

• Adding student records

$ curl -d '{ "name": "Paul Smith", "birthYear": 1982, "address": "3993, N. First Street, San Jose 95100" }' http://localhost:8080/StudentRegistry/student
{"id":"2b8f75ad-4a04-40e8-9204-bebf799310be", "name":"Paul Smith", "birthYear":1982, "address":"3993, N. First Street, San Jose 95100"}

$ curl -d '{ "name": "Jack Dawson", "birthYear": 1990, "address": "333 Valley Farms Street Hicksville, NY 11801" }' http://localhost:8080/StudentRegistry/student
{"id":"399979df-a08a-42f8-a255-59b110890cbf", "name":"Jack Dawson", "birthYear":1990, "address":"333 Valley Farms Street Hicksville, NY 11801"}

• Lookup student records

$ curl http://localhost:8080/StudentRegistry/student/399979df-a08a-42f8-a255-59b110890cbf
[{"id":"399979df-a08a-42f8-a255-59b110890cbf", "name":"Jack Dawson", "birthYear":1990, "address":"333 Valley Farms Street Hicksville, NY 11801"}]

$ curl http://localhost:8080/StudentRegistry/student/
[{"id":"2b8f75ad-4a04-40e8-9204-bebf799310be", "name":"Paul Smith", "birthYear":1982, "address":"3993, N. First Street, San Jose 95100"}, {"id":"399979df-a08a-42f8-a255-59b110890cbf", "name":"Jack Dawson", "birthYear":1990, "address":"333 Valley Farms Street Hicksville, NY 11801"}]

• Update student records

$ curl -X PUT -d '{ "name": "Jack Dawson", "birthYear": 1990, "address": "7403 East Hill Field Lane Battle Ground, WA 98604" }' http://localhost:8080/StudentRegistry/student/399979df-a08a-42f8-a255-59b110890cbf

$ curl http://localhost:8080/StudentRegistry/student/399979df-a08a-42f8-a255-59b110890cbf
[{"id":"399979df-a08a-42f8-a255-59b110890cbf", "name":"Jack Dawson", "birthYear":1990, "address":"7403 East Hill Field Lane Battle Ground, WA 98604"}]

• Delete student records

$ curl -X DELETE http://localhost:8080/StudentRegistry/student/399979df-a08a-42f8-a255-59b110890cbf

$ curl http://localhost:8080/StudentRegistry/student/
[{"id":"2b8f75ad-4a04-40e8-9204-bebf799310be", "name":"Paul Smith", "birthYear":1982, "address":"3993, N. First Street, San Jose 95100"}]

Business Domain Model vs. Data Model

In the simple CRUD pattern example above, we saw how to use the same data model in reading and writing. It was nothing out of the ordinary — a no-nonsense way of accessing and manipulating data. What else would we want, right? Well, there’s more to the story. Let’s expand the scope of our student registry to include course registrations. This would result in the following ER diagram.

Figure 2: Student/Course Database ER Diagram

As we can see in Figure 2, the new association of courses to students created two new tables, one for course information and another mapping table to create a many-to-many relationship between students and courses. So using our CRUD pattern, how do we create a link from Student to Course? We can create a set of CRUD operations such as

addStudentCourse

deleteStudentCourse

StudentCourse

enrollInCourse

leaveCourse

The clarity of the service interface is one benefit we get when we align the operations with the behaviors of the domain model. It also forces us to follow the optimal data operations design required for the business domain. For example, in a case where we need to update multiple tables in a single operation and transaction, this would be very complicated if we simply had CRUD operations for separate tables, and we would have to implement a mechanism to propagate the transaction context between service calls. 

Our earlier CRUD service happens to correspond somewhat closely to the business domain of adding students, updating student info, and deleting students. But then again, it would have been more appropriate to call them

registerStudent

updateAddress

Also, when we directly work with the data model, we lose the context or the intent that the operation was done with. For example, in the case of deleting a student record, we have to ask the questions: did the student get expelled, leave school in the middle or graduate; has the student not enrolled at this school at all? So in many cases, persisting the intent of the actions are important, especially in industries that have regulations to provide an audit trail of the actions done in the system.

A data access pattern that enforces domain based modelling is realized with CQRS. Let’s take a look at how this is implemented and used in a practical manner. 

CQRS

The CQRS pattern sounds complicated, but it’s really not. It just talks about separating the data models used for writing and reading. There is a good chance that many of you have used this pattern even before the term was invented. But again, as with any pattern, if you really want to make it complicated, you can do so by using it in the wrong situations, or implementing it in the wrong way. 

Figure 3: CQRS Data Model Operations

The write operations in CQRS are called commands, which are direct representations of the business domain operations. So a command will encapsulate the context/intent of a business action in the system. The read operations are done as queries to the system that is strictly a read-only operation and does not modify the state of the system. This simply returns a Data Transfer Object (DTO) to be used by the system. Due to this separation of the data models, we will be able to optimize each group of data operations in their respective domains. A common pattern for queries is carried out by creating a materialized view from the database that the read operations can use. 

Read/Write Data Model Separation

In CQRS, the generation concept is that the separation of the read/write models allow us to possibly use a separate database for each data model. Even two different database types can be used. Most of our use cases entail the write operations to be considerably lower than the number of read operations. So it is generally instructed that we can use

• A write optimized model such as a highly normalized relational database for commands
• A read optimized denormalized database such as a document database for queries.

Moreover, having the read and writes in separate databases may help in avoiding possible lock contentions and merge conflicts. 

But then again there is no free lunch. When we separate the read/write nodes, there should still be a way for the write nodes to synchronize the data to read replicas. So in the end, the read nodes will still get the same write traffic as the individual write nodes are getting. Also, given the separation of read/write nodes and that the syncing is done asynchronously, the read and write models will not always be consistent. So this generates an overall eventually consistent data model in the system. Most times this is fine, but there are some scenarios where the write model’s changes must be immediately visible by the read model. For example, in a shopping cart, if we add an item to the cart and go for the checkout immediately, the cart should show all the items we put in earlier, or else, the user will consider this an error situation. 

Let’s take the scenario of using different types of databases for read/write models. 

Figure 4: CQRS Data Model Operations

Here, we are using an SQL-based relational database for the writes and a document database for reads. The moment we issue a command, the write operation is done on the relational database. We have to make sure a similar message is sent out to the document database in order to do the sync. This can be done directly as a synchronous or an asynchronous operation using a message queue between the databases. In either way, we need to create a distributed transaction between the two databases or the relational database and the message queue in order to make sure our overall data consistency is retained. Distributed transactions are almost always bad news and should be avoided whenever possible. Any performance improvements we expect from the system will be wiped out by the overhead incurred due to the transaction processing. Not to mention the overall complexity added to the system, which makes testing and troubleshooting the system much more difficult to do. 

Keeping it Simple - A Reference Implementation

In the interest of building a CQRS system that is simple to maintain while providing the characteristics we need, let’s model a system based on a single MySQL database server with read replicas. A single primary database server will take in the write requests. Then we have multiple read replicas where we can configure fully synchronous replication in order avoid eventual consistency behaviors, if required. 

Figure 5: Reference Implementation - CQRS with Read Replicas and Materialized Views

As shown above in Figure 5, we run our commands in the primary MySQL node and it takes care of the details of propagating the changes to the read replicas. We can scale the read replicas as much as we want according to our load requirements for queries. Also, we create materialized views in our read replicas in order to pre-execute complex SQL operations in our normalized database and create a denormalized view of the resultant data. These materialized views basically act as read-only caches for the queries and will provide optimal performance. Of course, the materialized views are used in an eventual consistent manner, and thus should be used in suitable use cases. For example, in an ecommerce scenario, the sales count for products can be generated in a materialized view, since the query required to generate the final dataset will be complex and will contain multiple table joins and aggregate operations. We do not really care if the stats are outdated for a while, but rather, we can get a general idea of the state of the system. These are usually implemented in query intensive places such as top product listing in a website, product recommendations for the season and so on. 

As a general rule of thumb, we should always use proven technology when it’s available. When we try to create our data syncing operation between multiple database servers, we are basically solving a problem that has already been solved carefully. So there is a high probability we will not do a better job in imitating already well established systems. It also takes our attention away from the core business functionality of the system, and we rather end up spending more time on infrastructure related implementations. 

Event Sourcing - Tracking the Past

Now that we have examined the basic functionality of CQRS, let’s move on to an extended functionality of it, which combines with event sourcing. With commands, it encapsulates the intent of the business operation, but after we apply the operation to the data model, this information is lost. So the write data model currently simply contains the last state of the system. Event sourcing means we store all the commands we execute against the system as events. In this manner, we can go back in time and derive the state of our system. It provides us an audit trail of all the business operations that were executed.

Having these events means we are storing all the activities that happened in the system, and we can recreate our system at any point in time in the past. For example, if we later decide that we should have created a report by looking at some specific data like a price analysis of products on the website, and don’t have historical data, we can only generate that report for future use. But with the event sourcing mechanism with CQRS, we can generate any number of reports, as if we took the decision back in the past. For implementing event sourcing, we can use a technology that can provide an append-only data store. In extending our earlier reference implementation based on a relational database, we can do this by storing the sequence of events in a database table. In this manner, we can do a simple local transaction for updating the current state in the write model, as well as appending the commands as events. 

Figure 6: Reference Implementation - CQRS with Event Sourcing

The diagram above in Figure 6 shows our reference implementation of CQRS with event sourcing features. As a command is submitted to the system, a corresponding event is generated with the same information as the command. On the same occasion the write model is updated with the command, the event is also appended to an event list implemented using a database table. Since both data operations happen in the same database, we can simply do a local transaction to make sure both data operations succeed in a consistent manner. 

More Ways to Audit?

There are many other ways we can audit our data operations, where we can use some of the database server’s features itself to set up auditing, or other dedicated tools for tracking changes in data. But the key distinction of event sourcing is that it works with domain operations whereas other auditing approaches will be tracking the changes in the data model. In this manner, the context of the command or the intent will be lost. So it is important that we store the correct representation in the event stream. 

In the next section, we will look at how we can go about developing the reference implementation we have mentioned earlier for CQRS with event sourcing. 

Case Study: Core Banking Account Management

In this case study, we are going to create a hyper-simplified operation of a core banking system which does account transactions. It will model basic operations such as account debit/credit operations, and also, for its internal usages, provide information such as the active/inactive account ratios for each branch in the bank. And for our scenario here, we assume those lookup operations are done frequently. 

The following ER diagram in Figure 7 is used in creating our data model.

Figure 7: Bank Account Management ER Diagram

As shown above, every account is associated with a bank branch and has multiple account log entries associated with it. An account log entry contains any and all actions done against the account. This is encoded in an event payload in the log entry. These events have a specific event type in order to understand what this event would do, and it will be basically used in our command handlers in our code. Also, the event types can be versioned as well, in order for any evolution of the events that are generated for the accounts.

In the following section, let’s check the commands and queries that will be supported by the system. 

Commands and Queries

The commands and queries executed against the system will contain all the interactions we can do against our accounts. So all possible operations must be modeled as our business domain operations. Let’s start with the commands we will support.

• Create account
• Close account
• Freeze account
• Credit account
• Debit account

These commands provide the basic functionality we will need to maintain the account. The information of each command will be recorded as an account log event as an audit trail of all the actions that occurred. This information is required for regulatory requirements and for the bank to provide historical information to customers in any dispute regarding past transactions that have occurred. Also, this information can be directly used by customers, probably from an online portal, to list the past transactions as well. For example, month by month past account statements can be generated by simply replaying the events from the account log with specific event types. 

The list of queries are as follows:

• Lookup account details
• List past transactions
• Active/inactive account ratio by branch

These contain direct operations such as the account balance that can be directly looked up by the account table, which contains the current state. In the case of listing past transactions also, we can directly query the account log to retrieve the required entries. As for more process intensive queries such as creating account summaries, which probably will contain complex database joins and aggregations, it will provide an additional load to the database and can affect the performance of general operations of the database. Due to this, we will create materialized views of these data sets and refresh these views periodically. We assume that this is a use case that is required in the day time when general banking operations with customers are happening. 

The materialized views will have some outdated data until it’s refreshed, but this is acceptable for the use case here. It will provide a major performance boost since each time these queries are done, it will not end up in an expensive database query operation, but will return a cached result. 

In the next section, let’s take a look at how these operations are actually implemented using a microservices approach in Ballerina. 

Implementation

The functionality will be exposed through a Ballerina service. It has separate resource functions for the respective commands and queries. 

The skeleton Ballerina service we will be implementing is shown below. 

Listing 3: Account Management Service Skeleton

Listing 3 shows all the access points to the functionality of the system. Let’s start by looking at how the commands and their respective command handlers are implemented. 

Commands

A command handler is the logic that is executed when a specific command is submitted into the system. Here, we are going to model this by using a function pointer map, which maps from a command type to an implementation. 

Listing 4: Command Handler Registration

Listing 4 shows how a command handler is implemented and registered by inserting into a function pointer map. The command handler will be used for executing the command when it’s first invoked and the event replaying will also be dispatched through the same handlers. 

Let’s take a look at how the command dispatch and the events corresponding to them are also persisted. 

Listing 5: Command Dispatching and Event Persistence

The

executeCommandAndLogEvent

transaction

Now, with the command dispatching and the event log persistence functionality in place, we can implement the individual service resource functions. 

Listing 6: Resource Function Implementation

As we can see in Listing 6, the resource functions simply have to extract the JSON representation of the command and call the

executeCommandAndLogEvent

Let’s now check how we can replay the event log if we have such a requirement to do so. Here, we simply have to load all the required events from the event log and call our dispatch logic. Listing 7 shows the implementation for this. 

Listing 7: Event Log Replay Implementation

Notice that we used the same dispatch function here, so it seamlessly integrates with the logic we have already implemented earlier in executing the commands. 

Queries

The queries follow a more straightforward approach in accessing the database and returning the data. Let’s take a look at how these are implemented. 

Listing 8: Query Operations Implementation

The

getAccountActiveRatios

Listing 9: Implementing the Materialized View in MySQL

Listing 9 shows the database table definition and also a stored procedure used to refresh and populate the table to work similar to a materialized view. Listing 10 shows the Ballerina code involved in invoking the above stored procedure. 

Listing 10: Implementation for Refreshing the Materialized View

Sample Execution

Here we will go through a sample flow of building the Ballerina project, creating the database, and invoking the service operations to test out the functionality. 

Build

$ ballerina build -a --experimental
Compiling source
    laf/account_mgt:0.1.0

Creating balos
    target/balo/account_mgt-2020r1-java8-0.1.0.balo

Running Tests

    laf/account_mgt:0.1.0
    No tests found

Generating executables
    target/bin/account_mgt.jar

Database Creation

$ mysql -u root -p < banking.sql

Service Deployment

$ ballerina run target/bin/account_mgt.jar
[ballerina/http] started HTTP/WS listener 0.0.0.0:8080

Command Executions

$ curl -d '{ "name": "James Hunt", "address": "68 Wild Rose St. Goldsboro, NC 27530", "balance": "0.0", "branchId": "BWI" }' http://localhost:8080/AccountManagement/createAccount
{"accountId":"db386e20-0335-487c-9779-7af7b191bec1", "name":"James Hunt", "address":"68 Wild Rose St. Goldsboro, NC 27530", "balance":"0.0", "state":"ACTIVE", "branchId":"BWI"}

$ curl -d '{ "name": "John Wayne", "address": "75 Applegate St. Dawsonville, GA 30534", "balance": "0.0", "branchId": "GNC" }' http://localhost:8080/AccountManagement/createAccount
{"accountId":"3d679c5f-f645-4790-acbb-3c054eb4b62f", "name":"John Wayne", "address":"75 Applegate St. Dawsonville, GA 30534", "balance":"0.0", "state":"ACTIVE", "branchId":"GNC"}

$ curl -d '{ "name": "Jill Jackson", "address": "7439 Armstrong Ave. Lynnwood, WA 98037", "balance": "0.0", "branchId": "GNC" }' http://localhost:8080/AccountManagement/createAccount
{"accountId":"083acdbf-d52a-4599-bc8f-ae25b7ab272b", "name":"Jill Jackson", "address":"7439 Armstrong Ave. Lynnwood, WA 98037", "balance":"0.0", "state":"ACTIVE", "branchId":"GNC"}

$ curl -d '{ "name": "Bill Johnson", "address": "75 Applegate St. Dawsonville, GA 30534", "balance": "0.0", "branchId": "GNC" }' http://localhost:8080/AccountManagement/createAccount
{"accountId":"b8880ba4-9d7a-48e6-9760-2d5273aace7b", "name":"Bill Johnson", "address":"75 Applegate St. Dawsonville, GA 30534", "balance":"0.0", "state":"ACTIVE", "branchId":"GNC"}

$ curl -d '2000' http://localhost:8080/AccountManagement/creditAccount/db386e20-0335-487c-9779-7af7b191bec1

$ curl -d '225.5' http://localhost:8080/AccountManagement/debitAccount/db386e20-0335-487c-9779-7af7b191bec1

$ curl -d '1500' http://localhost:8080/AccountManagement/creditAccount/3d679c5f-f645-4790-acbb-3c054eb4b62f

$ curl -d '1500' http://localhost:8080/AccountManagement/creditAccount/083acdbf-d52a-4599-bc8f-ae25b7ab272b

$ curl -d '1200' http://localhost:8080/AccountManagement/creditAccount/b8880ba4-9d7a-48e6-9760-2d5273aace7b

$ curl -d 'Migrating' http://localhost:8080/AccountManagement/closeAccount/b8880ba4-9d7a-48e6-9760-2d5273aace7b
{"reason":"Migrating"}

Query Executions

• Account details

$ curl http://localhost:8080/AccountManagement/getAccountDetails/b8880ba4-9d7a-48e6-9760-2d5273aace7b
[{"accountId":"b8880ba4-9d7a-48e6-9760-2d5273aace7b", "name":"Bill Johnson", "address":"75 Applegate St. Dawsonville, GA 30534", "balance":1200.0000, "state":"CLOSED", "branchId":"GNC"}]

• Listing account transactions

$ curl http://localhost:8080/AccountManagement/listTransactions/db386e20-0335-487c-9779-7af7b191bec1
[{"accountId":1, "name":"db386e20-0335-487c-9779-7af7b191bec1", "address":"CreateAccount", "balance":"{\"accountId\":\"db386e20-0335-487c-9779-7af7b191bec1\", \"name\":\"James Hunt\", \"address\":\"68 Wild Rose St. Goldsboro, NC 27530\", \"balance\":\"0.0\", \"state\":\"ACTIVE\", \"branchId\":\"BWI\"}", "state":"2020-06-17 12:38:46"}, {"accountId":5, "name":"db386e20-0335-487c-9779-7af7b191bec1", "address":"CreditAccount", "balance":"2000", "state":"2020-06-17 12:41:26"}, {"accountId":6, "name":"db386e20-0335-487c-9779-7af7b191bec1", "address":"DebitAccount", "balance":"225.5", "state":"2020-06-17 13:03:50"}]

$ curl http://localhost:8080/AccountManagement/listTransactions/b8880ba4-9d7a-48e6-9760-2d5273aace7b
[{"accountId":4, "name":"b8880ba4-9d7a-48e6-9760-2d5273aace7b", "address":"CreateAccount", "balance":"{\"accountId\":\"b8880ba4-9d7a-48e6-9760-2d5273aace7b\", \"name\":\"Bill Johnson\", \"address\":\"75 Applegate St. Dawsonville, GA 30534\", \"balance\":\"0.0\", \"state\":\"ACTIVE\", \"branchId\":\"GNC\"}", "state":"2020-06-17 12:39:22"}, {"accountId":9, "name":"b8880ba4-9d7a-48e6-9760-2d5273aace7b", "address":"CreditAccount", "balance":"1200", "state":"2020-06-17 13:04:47"}, {"accountId":10, "name":"b8880ba4-9d7a-48e6-9760-2d5273aace7b", "address":"CloseAccount", "balance":"{\"reason\":\"Migrating\"}", "state":"2020-06-17 13:05:59"}]

• Account active ratio analysis (materialized view)

$ curl http://localhost:8080/AccountManagement/getAccountActiveRatios
[{"branchId":"BWI", "ratio":1.0}, {"branchId":"GNC", "ratio":0.666667}]

• Event log replay

$ mysql -u root -p BANKING_DB -e "truncate ACCOUNT"

$ curl http://localhost:8080/AccountManagement/getAccountDetails/b8880ba4-9d7a-48e6-9760-2d5273aace7b
[]

$ curl -X POST http://localhost:8080/AccountManagement/replayLog/b8880ba4-9d7a-48e6-9760-2d5273aace7b

$ curl http://localhost:8080/AccountManagement/getAccountDetails/b8880ba4-9d7a-48e6-9760-2d5273aace7b
[{"accountId":"b8880ba4-9d7a-48e6-9760-2d5273aace7b", "name":"Bill Johnson", "address":"75 Applegate St. Dawsonville, GA 30534", "balance":1200.0000, "state":"CLOSED", "branchId":"GNC"}]

Summary

In this article, we have reviewed two popular data handling patterns for microservices. The CRUD model provides a simple way of modeling the data and exposing this as its service interface. This may be fine for simple database tables, but for a database with multiple tables and foreign key relationships, it will be hard to model the operations using a CRUD pattern. Also, the operations don’t represent the business domain operations, thus it becomes hard to model the software system which uses it. Additionally, CRUD is considered a destructive pattern when it comes to handling data, due to the existence of update and delete operations. After these operations are done, the full past state is lost, and we are not able to read it back. 

Because of these reasons, CRUD in services is generally known as an anti-pattern. We should rather model the data operations similar to the business domain. CQRS avoids the main issues that are inherent in CRUD, and also when used in conjunction with event sourcing, it provides a comprehensive audit log that will allow us to relive any moment of the system in the history. We have come up with a reference implementation for CQRS with event sourcing using the MySQL database server with materialized views to do an efficient read/write model.

The Ballerina projects and the source code for the samples mentioned here can be found below:

• https://github.com/lafernando/samples/tree/master/ballerina/crud-cqrs

More information on Ballerina and its features can be found in the resources below:

• Ballerina by Example
• Ballerina API Documentation