Join table between Different Microservices

Question

I am still trying to make sense of micro service architecture.

The idea to separate different application (include the database) excites me. But I am still confused if there are two micro-services e.g. Product and User. both product and user own table product and user respectively in their database. According to best practice in micro service, we only can access the database from the service.

The problem is, let us suppose we have product table that has user_id column. We want to do search product which also return the name of the user who create the product. This requires join between product table in product micro-service and user table in user micro-service. How do you handle this?

score 24 · Accepted Answer · answered May 20 '17 at 12:39

24

You'll have to make a call to each microservice and do the join manually or pass in the relevant user ids to each service.

UserMicroservice:

SELECT * FROM Users WHERE some condition is true

Get list of Users back including their ids.

ProductMicroserivce:

SELECT * FROM Products WHERE some condition is true AND userId IN (.........)

So users and products can still be in two different databases, the products will just need to have the concept of a userId.

The reverse can also be done, ProductMicroserivce:

SELECT * FROM Products WHERE some condition is true

Extract all the UserIds then call the UserMicroservice:

SELECT * FROM Users WHERE some condition is true AND id IN (.........)

answered May 20 '17 at 12:39

Jan Vladimir Mostert

12,380
15
80
137

1

Thanks, I will try :D – Robert Limanto May 20 '17 at 12:46
2

so we call the service to each other using the api (Rest/http)? – Robert Limanto May 20 '17 at 12:47
can you guys help me on this one? http://stackoverflow.com/questions/44083919/microservice-configuration-in-play-2-framework – Robert Limanto May 20 '17 at 12:48
Typically whichever service wants the data needs to make the call to both services, UserService and ProductService won't be making calls to each other. – Jan Vladimir Mostert May 20 '17 at 12:48
1

oh, so we call both apis from the frontend? – Robert Limanto May 20 '17 at 12:49
Either the frontend can do it, or some other backend service can do it, it's up to you to decide. Frontend is usually a good option. – Jan Vladimir Mostert May 20 '17 at 12:50

score 21 · Answer 2 · answered May 22 '17 at 03:58

21

While I think there's nothing wrong with doing it the way Jan suggested I would like to add that the difference microservices should add to your system is of a different nature.

The above segregation of services is what we've seen a lot in the SOA world and it turned out to become too complex very quickly without offering much value.

If you, and I understand it's just an example, have the need to query user connected to products - why split the service up? You end up designing a service per db-entity instead of looking at what the requirements are for a given, what seems to me bounded context.

-Lars

answered May 22 '17 at 03:58

Lars

321
1
4

8

Fully agree on this, microservices for the sake of microservices makes no sense. – Jan Vladimir Mostert May 22 '17 at 05:34
so what is the best example of different microservice? if user and product should be in the same microservice – Robert Limanto May 22 '17 at 06:06
Yes, if your use cases show that those 2 entities are queried/updated a lot at the same time that would show that they should live in the same bounded context and therefore being addressed by the same microservice. Of course there might be instances where you find that 2 entities are very loosely coupled in which cases you may want to replicated data at db-level so 2 or more microservices can access these without having to interact a lot with each other. – Lars May 24 '17 at 01:05
2

@Lars This is an old post and I have run into the same situation multiple times now. I agree that if service boundaries cannot be properly respected then, there's no point in splitting up into different services. However, there are cases when each service, by itself has considerable complexities and differences. In those cases, keeping the services together, coupled tightly under a single API causes more problem. It would be great to figure out what's the best strategy for "cross-service" queries (don't know if that term is valid). – Abrar Hossain Apr 07 '20 at 06:26
@AbrarHossain wrt. designing the services, I like to stick with the rules from the book "Implementing Domain-Driven Design" by Vaughn Vernon: 1) keep the boundaries clearly separated; 2) Minimise the aggregates size, preferably one entity per aggregate unless eventual consistency is not acceptable by the business requirements. Wrt. the queries accross multiple services, I'd suggest CQRS and materialised views as described by Praful's answer. – dbaltor Apr 27 '20 at 21:25

score 14 · Answer 3 · answered Feb 12 '18 at 13:05

Best answer to such scenarios is CQRS. Maintain the materialized views of User and product relationship. Meterialized views could be anything which provides low latency in reading like NoSql DBs. Whenever Command updates User or Product by their microservices,the corresponding events should get generated and gets captured by other service managing this materializedView. This service will be used to fetch the query you are interested in. Always keep in mind,Microservices architecture believes in Eventual Consistency which definitely is not a bad thing :) .Hope this answers your question.

Join table between Different Microservices

3 Answers3