What is the best practice about microservice architecture for consuming many stored procedures in the same database?

Question

I have to build a micro service for my company, the backend is an Oracle database, but the micro service must consume five (5) stored procedures that already exist in the database (as shown in the screenshot). The database architecture can't change.

My question is: must I build only one (1) micro service that consumes these five(5) stored procedures?

Or must I build five (5) micro services that consume each one of these stored procedures?

I'm very confused about making that decision, because in most architecture notes about micro services, one micro service should exist only for one database but this way it wouldn't be a decoupled solution. But the other hand, if I build five micro services, each one using a stored procedure, it would be five services that share the same database or repository of data.

Thanks a lot for your attention.

Answer 1

I get the feeling that some decision maker in your company heard about the buzzword "micro-service" and decided that you need to have them as well, regardless of if they actually solve a problem you are having.

The primary reason why all notes talk about a separate database for each micro-service is because micro-services are intended to be independently deployable and scalable.

If you find that one feature of your application is much more heavily used than the other features, then if each of the features is implemented by a (proper) micro-service, then you can just run more instances of that one heavily used service. And that can also mean having multiple instances of the database to keep the load on that part of the service within reason.

With your constraint of a fixed database architecture, I would design a single service for that and, independently of whether that service meets all the checkboxes for a micro-service, call it a micro-service (for political reasons).

In short, create the best design given the technical constraints and then slap on the buzz-word labels that people want to see.

Answer 2

Micro- and midi-services can often usefully be divided by Topic; sometimes, more coarsely, they can be divided by the Source/ Category/ Lifetime of the data involved.

Some potential scenarios:

• An Entity and closely related data owned by it should typically be a single service. (In the case your 5 stored procs are different CRUD and child accessors).

• A group of topically related entities (eg. Customer, Address and Preferences) may often be a single service.

• Where multiple entities have strong commonality as to lifecycle, you can sometimes consider grouping these into a single "midi service". For example, a group of Reference Data tables or inputs/ outputs to or from external systems.

• Entities which are separate in topic and lifecycle should be separate services.

See: https://literatejava.com/uncategorized/micro-and-midi-services-how-should-i-divide-my-services/

Answer 3

Although you can not avoid one database, separate your microservice into boundary contexts. The bounded context concept originated in Domain-Driven Design (DDD) circles. It promotes an object-model-first approach to a service, defining a data model that a service is responsible for and is "bound to." In other words, the service owns this data and is responsible for its integrity and mutability. Take a look at

https://dzone.com/articles/implementing-a-bounded-context#:~:text=The%20bounded%20context%20concept%20originated,for%20its%20integrity%20and%20mutability.

Answer 4

Giving a benefit of doubt and assuming this is just first step moving towards decoupled service architecture at your company.

You can think of a 1 stored procedure as one logical unit of work needed to be completed. May be the current procs are very complicated and they are doing lots of cross db queries, linked server calls and all possible workflow that does not fit in the expected end goal. in that case someone has to work lot more on data refactoring.

Or may the procs are simple enough that with little modifications they can be lift and shift to their own db along with underlaying data they need. Whatever the case with procs I am guessing someone is thinking about it doing later or at least it's part of the bigger plan.

So I would lean towards one service for one proc so that later when you guys refactor db components it will more or less align with end to end (application+db) independent deployable service architecture.

That is just MPO based on limited information we have here.

Answer 5

To properly design the microservice(s), you need to step back and look at things from a higher level. It's not enough to identify what components you have and what ones you may need to build; you need to consider how and why each component is used and how they relate to other components.

Imagine you live in a one bedroom apartment but will soon move to a much larger house. You have five pieces of furniture in your living room you would like to keep and need to decide what room(s) to move the furniture to. Would you move each piece of furniture to a separate room in the house or would you move them all to the same room? (Pause for a moment to consider how silly that question might sound.) Presumably, the rooms you choose would depend on what those pieces of furniture actually are.

If two pieces of the furniture are a couch and a TV stand, you would likely move them to the new living room so you have a place to sit and watch TV. You might also move the floor lamp to the living room depending upon how much light there is. However, your kitchen table wouldn't need to be in the living room since you would now have a full sized kitchen to put it in. Likewise, your coat rack would go in the foyer of your new house since that's where the front door is.

This is the same approach you should apply to your new microservice(s). What do these stored procedures do? How are they used and how do they relate to each other? If they are all closely related and see similar levels of use, then it would make sense to put only one microservice in front of them. If, however, those stored procedures have nothing in common aside from being in the same database, then it would make sense to create five separate microservices; if you do so, though, you should also discuss splitting the database into smaller pieces as well.

There are many possible ways you could design microservices. The key is to organize the functionality into services where the internal components are closely related but the external components are loosely related (if related at all). This will make the services easier to comprehend and thus easier to maintain. It will also minimize dependencies, making it easier to scale services as you need to.

In short, the ratio of microservices to stored procedures is irrelevant. What matters it that functionality is organized logically. Otherwise, you'll end up in weird scenarios like having a TV you can't sit in front of or a coat rack that isn't anywhere near a door.