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?

A multitier architecture encourages application logic to be decoupled from the data storage. It's alright to have stored procedures sanitize any data issues you have, as long as it doesn't go beyond that. — 94239, Sep 16 '19 at 07:26
Consider this: If a microservice uses 1 database per service, that database structure would be non-relational. Thereby technologies such as noSQL are really popular. They already don't support SP's making the choice for you. — RandomUs1r, Sep 16 '19 at 17:40
@RandomUs1r sorry, this doesn't make sense to me. Why does the DB structure have to be non-relational? Sure, it may have external references, but its internal structure may well be 100% relational — IMil, Sep 17 '19 at 00:09
The problem with your points is that all of your premises are wrong. The statement that microservices should be autonomous and loosely coupled means, first and foremost, that they should be loosely coupled *to each other*; how you manage coupling of internal components is a different matter - and of secondary importance (but not unimportant) - especially if you can just *replace the whole microservice* in an update. So no reason why you can't use sprocs within those confines. Also, DDD *does not* forbid sprocs, or paradigm mixing; some aspects of some problems aren't best suited for OO. — Filip Milovanović, Sep 17 '19 at 08:48
How monolithic your database is has to do with your Data Design and implementation, it has nothing to do with using or not using stored procedures. — RBarryYoung, Sep 17 '19 at 12:23
@RandomUs1r You are confusing service with operation. Read more [here](https://chrisrichardson.net/post/antipatterns/2019/05/21/antipattern-more-the-merrier.html) — JimmyJames, Sep 17 '19 at 16:39
@IMil it doesn't have to be, but... why wouldn't it be? You defeat the purpose of a micro service if you cross cut concerns in the database that the microservice isn't responsible for or shouldn't be privy to. I hate when somebody says they are following SRP ms and then their SP / db implementation does 50 things. That's db first design, which defeats the purpose of ms. A better design is for each ms to have it's own store, plenty of docs on google to support the finer whys. — RandomUs1r, Sep 17 '19 at 18:54
@JimmyJames that article talks about refactoring and splitting micro services as needed. However, this has nothing to do with the data they handle, if anything a SP with multiple joins would be a sign to split your service, but why not just start w a store per service & refactor on the fly rather than in hindsight? — RandomUs1r, Sep 17 '19 at 18:57
@RandomUs1r I have no idea what that has to do with the the subject. You suggested that microservice DBs can't be relational which suggested to me that you were under the impression that each operation needs it's own DB. This is not the case. A micro-service can have many operations that all share the same DB. — JimmyJames, Sep 17 '19 at 19:03
_"Stored procedures typically work on a monolith database."_ You should strongly consider discarding any information or advice you get from whatever source that shared that "fact" with you. — StingyJack, Sep 18 '19 at 04:28
@RandomUs1r We write microservices that use a perfectly fine relational database (MSSQL Server). The microservices run perfectly fine on their own database and are just as happy sharing the same database depending on how they're deployed. I mean using SPs is a bad idea in many situations, but that's a completely separate topic. — Voo, Sep 18 '19 at 08:02
@Voo so you're taking on all the overhead of having a relational database with none of the benefits +1. — RandomUs1r, Sep 18 '19 at 15:26
@RandomUs1r Umm no, the only thing you really lose is that you can't use foreign key constraints on reference keys - which is rather the point of microservices. For one the idea that NoSql databases are somehow magically faster has been disproven repeatedly, but even if they were faster (they''re not), you also get all the existing infrastructure, knowledge and existing code for free - which is *huge*. CERN and many others manages terabytes of data using relational databases just fine. NoSql databases have their use but those are independent of whether you use microservices or not. — Voo, Sep 18 '19 at 15:53

Flater · Answer 1 · 2019-09-16T08:21:01.083

48

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.

edited Sep 16 '19 at 08:21

answered Sep 16 '19 at 08:12

Flater

44,596
8
88
122

4

I'm not sure if it is correct to say that microservices push you to modernize your entire architecture. More often than not, they end up being a thin layer over a behemoth of a mess of poorly planned code. They can be pretty good when well done, but they don't really push you in any way towards better coding than any other architecture. Still, good answer. You got a +1 from me. – T. Sar Sep 16 '19 at 11:56
11

@T.Sar modern is not the same as better. Refactoring (to microservices or whatever) means change. Change forces you to use your current ideas. We hope they are better ideas. – candied_orange Sep 16 '19 at 12:19
2

@T.Sar: Hacks are timeless, and you can usually abuse any system (modern or not) to do something it can technically handle but was never intended for. Microservices _urge_ you to do it differently (and thus reevaluate some old approaches) but they can't _universally enforce_ it. With universal enforcement you usually suffer in the compatibility/valid fringe case department. – Flater Sep 16 '19 at 12:24
4

@candied_orange "modern is not the same as better" - I think I wholeheartedly agree to that. Very good point. – T. Sar Sep 16 '19 at 13:47
3

Modern is not even synonynous of "adequate". – Laiv Sep 17 '19 at 16:13
2

Dinosaur here. Why are Stored Procs considered a bad things these days? In our applications we use them extensively because they allow for further separation of implementation and interface, which also lets our Data wranglers worry about the best implementations completely separate from other devs. It has also let us hotfix production issues without touching a running application. – Chuu Sep 18 '19 at 15:38
@Chuu: It splits your business logic. The problem is that when your domain changes, you need to update both your codebase and your sprocs (compare this to e.g. EF Code First, where you need to make the change only once). Additionally, it's easy for sprocs and code to be out of sync (version mismatch). I don't mind a raw SQL query for sufficiently complex queries (where EF cannot be performant) but even then I would advocate for storing this in your codebase rather than your database, just to keep the business logic together. – Flater Sep 18 '19 at 16:57
@Chuu: You mention yourself you have data wranglers. But most developers do not have dedicated coworkers who handle the DB (you do have DBAs to keep the servers up, but they tend not to optimize your queries for you as they are support staff). As a dev team, it's generally more interesting to not also have to wrangle SQL if you can avoid it. EF is popular specifically because it makes you have to wrangle one less technology/language (on top of simplifying boilerplate CRUD code and transactional behavior) – Flater Sep 18 '19 at 16:58
Just to be clear, stored procedures are often *recommended* for security reasons, along with parameterized queries, to prevent SQL Injection attacks. The less the SQL commands are made in-memory, in general the better. It also makes it easy to very clearly limit what kinds of users can perform what actions. There are definitely other ways around these limitations, but historically these are some of the reasons they have been used. – Nate Diamond Sep 20 '19 at 16:44
@NateDiamond: SQL injection attacks can _easily_ be circumvented without resorting to sprocs. The only difference is that a sproc inherently enforces data parametrization (which prevents injection) whereas code doesn't explicitly forbid you to string concatenate (which allows for injection); but like I said, there are many (easy) ways to clean up/parametrize your data and avoid injection while keeping your query in code. – Flater Sep 21 '19 at 14:28
Totally agree! Comments like: data separate from other devs, hotfix production issues, stored procedures are often recommended for security reasons... worked several years ago. Can someone imagine the ec2 service of aws in a monolithic application or the entire algorithm in one store procedure? If the algorithm or logic is inside of a big store procedure, what about the unit testing? – JRichardsz Nov 03 '21 at 22:10

score 24 · Answer 2 · edited Sep 18 '19 at 23:43

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?

score 11 · Answer 3 · answered Sep 16 '19 at 18:09

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:

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.
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.
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.)
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.
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:

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.
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.
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.

I think all of your first bullet-points also apply, if you write the business logic in e.g. a Java-Framework. Switching the DB-Engine will change performance characteristics and require retesting and maybe rewriting statements. If you write the SQL-Statements e.g. as Strings in you application, you have the same problem with changing variables breaking stuff. You need to decide if you app uses a technical user or individual users to connect to the DB and so on... — Falco, Sep 17 '19 at 08:59
@Falco I think if you're using JPA exclusively it shouldn't bee too difficult to change databases. Performance can definitely vary substantially and always needs to be tested. A couple services I maintain aren't "micro" in the sense that they can scan or aggregate over millions or billions of data points and return arbitrarily large (often paginated) data sets. I can't imagine using JPA for them, but I can imagine changing the underlying database engines (and rewriting the SQL) while maintaining the same API. — ngreen, Sep 20 '19 at 15:33

score 4 · Answer 4 · edited Sep 19 '19 at 01:19

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.

score 1 · Answer 5 · answered Sep 17 '19 at 03:33

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.

Microservices and stored procedures

5 Answers5