Database Design Patterns

There are various cloud service design patterns. I think many have heard about the same Sidecar or Ambassador. Templates are designed to solve specific problems, and the two templates that will be discussed in today’s article are also needed for a specific task – working with databases.

A DBMS is an integral part of any serious modern application. Accordingly, when designing an application, the question may arise as to how best for services to interact with the database: by providing shared access to one database, or should each microservice have its own database. We will look at two templates designed to solve this problem – Shared database and Database per Microservice. Let's start with Shared database.

General base

First of all, when talking about this and the next template, we will mean that we use a microservice architecture, that is, our application being developed consists of many separate services that interact with each other. At the same time, we still need to save the data for subsequent processing. So, if we are developing an online store, then obviously we need to save information about orders, customers, availability of goods in stock, etc.

We may have different requirements for the databases used and data storage. So, often, in some cases the best choice is a relational database, in other cases NoSQL is best suited, for example, for storing complex unstructured data.

At the same time, the logic of the requests itself may be different. Sometimes queries need to combine data belonging to multiple services. For example, finding customers in a specific region and their latest orders requires combining customers and orders.

But in a microservice architecture, the ideology is that services should be loosely coupled so that they can be developed, deployed, and scaled independently of each other.

On the other hand, as we mentioned above, some transactions must use data from multiple services. So, when placing an order, we must make sure that the new order will not exceed the customer's credit limit. Other business transactions must update data belonging to multiple services.

As a solution to the problems listed above, you can use a Shared database for several services with a common data structure. With this approach, each service will have free access to data belonging to other services using local transactions according to the ACID rule (atomicity, consistency, isolation, durability).

Let's see how the Shared DB pattern can be implemented in practice. TicketService and UserService services have access to each other's tables. For example, TicketService can use the following ACID transaction to ensure that a new order will not violate the customer's credit limit:

BEGIN TRANSACTION

…

SELECT TICKET_TOTAL

 FROM TICKETS WHERE USER_ID = ?

…

SELECT CREDIT_LIMIT

FROM USERS WHERE USER_ID = ?

…

INSERT INTO TICKETS …

…

COMMIT TRANSACTION

Like all other patterns, there are also advantages and disadvantages, and, accordingly, situations when it is preferable to use them.

The benefits of using the Shared DB pattern are that the developer uses familiar and simple transactions to ensure data consistency. Also, it is easier to work with a single database and it is easier to track data changes.

But this template also has a number of disadvantages that limit its use. Thus, the developer of the TicketService service must coordinate schema changes with developers of other services that access the same tables.

In addition, since all services access the same database, they can potentially interfere with each other. For example, if a long-running UserService transaction holds a lock on the TICKET table, TicketService will be locked.

Finally, one database may not meet the requirements for data storage and access to all services.

An alternative to the Shared DB pattern is Database per service.

Each service according to the database

This template proposes to abandon the use of a common database and instead create each service its own database, which no one else works with. This way we can provide loose coupling between services.

The main idea of the Database per service pattern is that microservices do not have access to the database of neighboring services and communicate with each other via REST, or through a message broker. This way our microservices become truly storage independent and can be developed in parallel.

The main advantages of this pattern are the loose coupling of services. Changes to the database used by one service do not affect other services as is the case with the Shared Database pattern.

This way, our services can use those DBMSs that are best suited for them. For example, one service can use Elastic search, a second NoSQL, a third SQL, if business logic requires it.

This pattern inherits its main disadvantages from the ideology of the microservice architecture itself. Thus, the implementation of business transactions covering several services is a difficult task that requires separate study. And making changes to the query logic also requires work.

We are also having difficulty managing multiple databases in our application. For example, if PostgreSQL, NoSQL and Elastic are used, then we will need specialists familiar with all these databases.

Conclusion

In this article, we looked at two patterns related to the operation of microservices and databases. Each of them has its own advantages and disadvantages and is more applicable when solving a certain range of problems.