Xjyuk

My question is are stored procedures considered bad practice in microservice architecture?

Here are my thoughts:

• most books on microservices recommend one database per microservice.
  Stored procedures typically work on a monolith database.




• again most microservice architecture books state that they should be autonomous and loosely coupled. Using stored procedures written say specifically in Oracle, tightly couples the microservice to that technology.




• most msa books (that I have read) recommend that microservices should be business oriented (designed using ddd). By moving business logic into stored procedures in the database this is no longer the case.

Any thoughts on this?

There is nothing that explicitly forbids or argues against using stored procedures with microservices.

Disclaimer: I don't like stored procedures from a developer's POV, but that is not related to microservices in any way.

Stored procedures typically work on a monolith database.

I think you're succumbing to a logical fallacy.

Stored procedures are on the decline nowadays. Most stored procedures that are still in use are from an older codebase that's been kept around. Back then, monolithic databases were also much more prevalent compared to when microservices have become popular.

Stored procs and monolithic databases both occur in old codebases, which is why you see them together more often. But that's not a causal link. You don't use stored procs because you have a monololithic database. You don't have a monolithic database because you use stored procs.

most books on microservices recommend one database per microservice.

There is no technical reason why these smaller databases cannot have stored procedures.

As I mentioned, I don't like stored procs. I find them cumbersome and resistant to future maintenance. I do think that spreading sprocs over many small databases further exacerbates the issues that I already don't like. But that doesn't mean it can't be done.

again most microservice architecture books state that they should be autonomous and loosely coupled. Using stored procedures written say specifically in Oracle, tightly couples the microservice to that technology.

On the other side, the same argument can be made for whatever ORM your microservice uses. Not every ORM will support every database either. Coupling (specifically its tightness) is a relative concept. It's a matter of being as loose as you can reasonably be.

Sprocs do suffer from tight coupling in general regardless of microservices. I would advise against sprocs in general, but not particularly because you're using microservices. It's the same argument as before: I don't think sprocs are the way to go (in general), but that might just be my bias, and it's not related to microservices.

most msa books (that I have read) recommend that microservices should be business oriented (designed using ddd). By moving business logic into stored procedures in the database this is no longer the case.

This has always been my main gripe about sprocs: business logic in the database. Even when not the intention, it tends to somehow always end up that way.

But again, that gripe exists regardless of whether you use microservices or not. The only reason it looks like a bigger issue is because microservices push you to modernize your entire architecture, and sprocs are not that favored anymore in modern architectures.

To write software requires that you tightly couple to a technology.

At the very least to the runtime environment provided by the programming language being developed within.

More generally though you will find that your micro-service is tightly coupled to several technologies:

• Network Service Framework to provide high level http/ssl/soap protocol implementations


• Repository/ORM/DAO Framework to provide persistence.


• Data Manipulation Frameworks to provide tools for working with data.


• Process/Threading/OS Framework to provide access to os resources such as multi-tasking, the file system, memory, gpu compute, expansion cards, etc...

And that is to make a bare-bones micro-service.

Stored procedures

A stored procedure is simply another technology that you could choose to use or not use. It does not magically make your code monolithic, or micro.

What it is though is:

• Another technology. Each technology present in the application decreases the likely-hood that any given programmer can read, understand, and make wise design choices for that technology mix.


• A language using a different programming paradigm. It is far too easy for non-experts to try and force their own imperative/functional/oo/etc... perspective onto it, which often leads to less than stellar results.


• An API. Which must be maintained like any other class in the code base. It also means that the database is providing a non-generic interface. This makes it harder to both replace the database engine itself, and to transparently apply generic behaviour such as in memory caching.


• An artefact. Which must be versioned, tested, and deployed. This can be done, but databases are living artifacts requiring a different approach. You cannot usually just delete the original, and replace it. Often a careful orchestration of changes over-time are needed to migrate the system to the desired state.

Each of these is a real cost. In some cases the cost is justifiable, in others it is not.

You would be paying almost the same set of costs by hosting a scripting engine. The sole reduction is that you could choose the same programming paradigm as the host language.

Business Logic

Moving business rules into the database is bad practice. Just not because of stored procedures.

Its a bad practice because the database and business logic operate on different shearing levels.

• A database in a mature applications can be in use for decades. Generally these systems will have the engine periodically updated, but the database itself was migrated. It was not killed and rebuilt from the start. There is no reason a micro service cannot be equally so long lived.




• Contrast decades against how quickly business rules change. In my experience an old business rule is perhaps a few years old, most however change quickly, and you can never tell which one will change next. A new requirement from a regulator, an old product being decommissioned, changes to the letter head, changes to how many employees report to a boss, etc, etc, etc...

If the business logic is distributed across the shearing layers, particularly into a slower and longer lived layer, it will generate resistance to change. This is not necessarily a bad thing. Afterall the only database that has zero business logic in it is a triple store.

The mere act of specifying a table schema is moving business logic into the database.

Architecture

You are contending with using the appropriate tool for the appropriate problem, without needing too many tools, nor making it too hard to solve, in order to make and maintain a solution.

This isn't easy.

But lets think the unthinkable, how would you maintain business logic distributed across several languages?

• A catalogue... so that each business rule implementation can be tracked and maintained.


• Tests... that could be used against each business rule regardless of where and how it was implemented.


• A reference implementation.. so that when discrepancies are found, a source of truth exists (or at least a source of debate).

But this has a cost too.

• Is it better to allow the business rules to have many implementations? That can each take advantage of the team skills, and framework provisions, but needing tight quality controls to ward off having many small vagaries?


• Or is it better to have a single source of truth, written in a single language? Arguably cheaper to implement, yet also a single source of failure, itself a monolithic component that resists change in the face of different platforms, frameworks, or as yet to be invented tools?

Stored procedures are implementation details. Database functions, lambdas or a shell script stored somewhere in the file system all are implementation details and irrelevant for the architecture. Microservice doesn't forbid the implementation of one or another solution. As soon as we don't break the golden rule of decoupling because it would defeat the purpose of the architecture, hence the chances for the business' strategy to success.

Bear always in mind that any architecture is a meant to an end. We don't build MS ecosystems for fashion or love to the art.

most books on microservices recommend one database per microservice.

Ok, so we can code the store procedures in these databases.

again most microservice architecture books state that they should be
autonomous and loosely coupled

Between business capabilities, development's life cycles, management,
deployments, team's locations, etc. Nothing to do with the implementation details. Microservices don't solve a technical problem (just the opposite). They come to solve problems with the management and the time-to-market. It's a strategy, not a tactic. A way to fail-fast with the least costs possibles. If a certain business capability is proven to be wrong or unworthy, we drop it without messing other capabilities.

Note that the "splitting" already acts like a decoupling agent. I mean, we have two services, A is backed by Oracle and B by MongoDB. If we didn't break the golden rule, should be possible for us to drop A + Oracle with 0 side effects on B. Look at the big picture, because the small one is irrelevant for the MS architecture.

Using stored procedures written say specifically in Oracle, tightly
couples the microservice to that technology.

It might cause vendor locking. Many times, the vendor is imposed by the business by historical or contractual reasons. I have worked on projects where everything was unclear but the database. The database already was there. In any case, is important to know how to not lock our code to the vendor. Some ORM and frameworks implement an abstraction language that hides the database built-in functions and features.

Although, if our services are micro enough, vendor locking is no longer a problem since it impacts a small part of the whole. A small part that should be possible to be replaced (or isolated) quickly.

most MSA books (that I have read) recommend that microservices should
be business oriented (designed using DDD).

Should be business-driven and here the thing. Not all business' takes advantage of DDD. DDD and MS overlap in many points, but they are not cause-effect. We could end up with an MS ecosystem of anaemic services and still be a good implementation of the architecture.

Regarding books. Bear in mind that they only focus on the execution of the strategy. The tactic. How to make possible a strategy based on distributed computing, but they are (usually) agnostic to the strategy. So you still have to extrapolate what they said to your specific case.