I wrote about my open source solution for doing so in MySQL/MariaDB here: https://www.skeema.io/blog/2023/10/24/stored-proc-deployment...
As someone who works in a team that abstracts the DB layer as a dependency of the specific controller logic there are multiple good reasons to do so but the primary one is that it makes code independent from the specific database you're using.
We use filesystem json databases in the developer machine, swappable with in-memory databases that die with the process, which are perfect for unit and integration testing that always get a clean db service, while running a mixture of SQLite and azure cosmos in prod. All the developer interacts with is UserRepo.find or ProductRepo.add or Transaction repo.query without ever having to think about the database it's running against.
The best part is that you can still write custom queries for specific environments (e.g. performance reasons) or when you may want specific ways to generate a specific view of your data.
Another good reason to do so, is that most people plain suck at interacting with databases, often they do more harm than good, and unless a query is noticeably slow I'd rather have them work with a predefined sets of primitive operations.
But in general, this is hardly ever needed.
Acid transactions are probably the only exception to the rule before, but even then you can hide the details and need to touch it.
I would create a new Redis command that would do some computation on the input and then store results in appropriate Redis types. One can also use a plethora of C libraries as well. Just don’t block the thread. One could then disable other Redis commands and turn Redis from a bag of data structures into a domain-specific Application Server. The power of this is that it makes the internal processing always consistent, and the version of the application logic is tied to the Module. Plus you can tune it like you tune Redis (for example with respect to persistence).
For HTTP interfacing, I’d either make specific Redis commands for a web service to use or give the web service authority to have direct access to Redis keys (traditional Redis architecture). Often I’d use OpenResty or Golang for that.
I haven’t done this for a while, nor played with Valkey, but it was fun.
If I were to come up with a rule: A database should avoid having to rely on stored procedures to maintain invariants, but shouldn't avoid using stored procedures to maintain invariants it would otherwise struggle to maintain.
1. Some applications can't tolerate the roundtrip latency, so the typical way is to put the business logic inside the stored procedure.
2. DBA wants to guard the database.
Predicate push down belongs to the DB. A small library of well known object/graph query primitives can be colocated with the DB.
These primitives tend to be side effect free and easy to comprehend. They also form 80% of "business logic".
This comes from having worked at companies that did put their business logic in the database and it lead to worse problems than almost any codebase I've ever had to deal with.