Microservices and stored procedures

Question

Are stored procedures considered bad practice in a microservice architecture?

Here are my thoughts:

• most books on microservices recommend one database per microservice. Stored procedures typically work on a monolithic 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 microservice architecture books (that I have read) recommend that microservices should be business oriented (designed using domain-driven design (DDD)). By moving business logic into stored procedures in the database this is no longer the case.

Any thoughts on this?

Answer 1

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.

Answer 2

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.

It's 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. After all, 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 let's 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?

Answer 3

I'll preface my answer by saying that I actually maintain a couple microservices that use stored procedures. Also I've written a lot of stored procedures at various points in my career, and I definitely agree that things can go very, very wrong if they are used incorrectly.

So the short answer is, no, stored procedures aren't inherently bad in a microservice architecture. But you do need to understand:

1. You're adding obstacles to the substitution of storage engines. If some operational or performance characteristics or feature limitations require you to switch storage engines, the cost will be greater because you'll be writing and testing a lot of new code. Running more than one storage engine (either during a migration phase or to isolate activities based on performance needs) can introduce consistency problems unless you use two-phase commit (2PC), which has performance issues itself.
2. You've got another API to maintain, which means your dependencies can break. Adding, removing, or changing the types of parameters on procedures can break existing code. The same thing happens with tables and queries, but your tools might be less helpful with tracking down where things might go wrong. Problems with stored procedures are typically found at runtime, very late in the develop/deploy process.
3. Your database permissions just got more complicated. Does a procedure run as the logged in user or as some other role? You need to think about this, and manage this (hopefully in an automated fashion.)
4. You need to be able to migrate to new versions safely. Oftentimes a procedure must be dropped and re-created. Once again, permissions might cause some problems for you.
5. Rollback of a failed migration can mean extra effort. When the production environment is separated from developers, things get even more challenging.

These are some uses of stored procedures that I think are often worthwhile:

1. Enforcement of edit history (audit logs). Triggers are commonly used for this purpose, and triggers are stored procedures. It's also possible in some databases to disallow inserts and updates entirely for the application role: clients execute procedures which are run as a different role with appropriate permissions and which enforce all of the necessary behavior.
2. Extension of check constraints. This might get you into business logic territory, but there are cases where a database's built-in constraint tools might not be sufficient for what you need. Often times the best way to express checks is with imperative code, and you risk letting bad data in if you depend on your application to do it for you.
3. Encapsulation of complex queries when a view is inappropriate or too complicated. I've seen a few cases where a correct view requires some extremely complex SQL that can be expressed much more understandably in a stored procedure. This is probably rare, but it does occur.

In general, I suggest that you try out views first, and resort to procedures only when necessary. Well-designed views can actually function as an API, abstracting out the details of how underlying tables are queried. Augmenting your API (views) with stored procedures makes sense in some circumstances. It's even possible to emit JSON directly from a SQL query, bypassing the whole mess of mapping data from query results to your application's data model. Whether that's a good idea is something for you to determine based on your own needs.

Since you should already be managing your database resources (schema, permissions, etc.) through some automated tool, no, stored procedures are not inherently bad for microservices.

Answer 4

Stored procedures are implementation details. Database functions, lambdas, or a shell script stored somewhere in the file system are all implementation details and irrelevant for the architecture.

most books on microservices recommend one database per microservice.

Ok, so we can code the stored 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 possible. If a certain business capability is proven to be worthless, we drop it without messing up other capabilities, deployments, projects' management, releases...

Note that the "split" already acts like a decoupling agent. Say we have two services, A is backed by Oracle and B by MongoDB. If we don’t break the golden rule of decoupling, it should be possible to drop A + Oracle with negligible side effects on B.

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

It might cause vendor lock-in. Many times, the vendor is imposed by the business due to historical or contractual reasons¹. It is important to know how to not lock our code to the vendor. For example, some ORM and frameworks implement a new query language that hides the database built-in functions and features.

Although, if our services are micro enough, vendor lock-in 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).

It should be business-driven and here the thing. Not all business take advantage of DDD. DDD and microservices overlap in many points, but they are not cause-effect. We could end up with a microservices ecosystem composed of anaemic services. Or composed of a mix of both: services implementing a complex domain and dumb anaemic services providing POJOs directly from the DB. There's nothing wrong with that.

Regarding books, they only focus on the execution of the strategy. The tactics -how to take advantage of the distributed computing- how to make it work to success, but they are (usually) agnostic to the strategy. Strategies vary from company to company and rarely depends on developers. So, we still have to extrapolate and adapt what books say to our specific needs, requirements and constraints. The goal is to make the business strategy profitable and sustainable.

Always bear in mind that any architecture is a means to an end. The business rules. We don't build microservices ecosystems for fashion or for love to the art.

Answer 5

It doesn't really have anything to do with microservices.

Stored procedures can make sense if your service has an 'old-style' layered architecture in which the DB is the foundation of the service, with data access and business logic layers on top. The interface between the service and the database in such an architecture is very specific to innermost details of the service. Typically there will be service-specific adapters for each kind of supported database, and the specificity of the API exposed by the adapter makes it possible to use stored procedures in the underlying layers.

There are lots of problems with architectures like that. Most importantly it makes most of the logic very difficult to unit test. These architectures are no longer in favour.

If you're using a newer-style "clean architecture", "onion architecture", or similar, then the database will be an injected dependency, specified at the outer layers. Since it's defined in the outer layers, the interface provided for the database must be generic. It cannot reflect the innermost details of the service, because those details must be hidden from the outermost layers of the architecture. Defining a generic stored procedure interface that can work with any database or unit test harness is incredibly difficult, and not really necessary, so stored procedures are not often practical in these kinds of architectures.

The relationship with microservices is just that microservices are new and ascendant -- we don't do monoliths anymore -- and that these new architectural styles are also ascendant -- we don't do flat layers anymore.