Code in Database vs. Code in Application

https://brandur.org/fragments/code-database-vs-app

by timokoesters • 2 years ago

14 8 2 years ago

I mean I have a pretty hard an fast rule: no business logic in stored procedures.

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.

wisnesky• 2 years ago

The code in database vs database in code duality has a long history, and one way to formalize it mathematically uses category theory: the notion of LINQ (language integrated query, such as collection comprehensions) is formally related to the notion of "query-integrated languages" (where data integrity constraints are Turing-complete domain specific languages extending the database vocabulary). https://arxiv.org/pdf/1511.06459

Arch-TK• 2 years ago

When people insist that code in the database is never acceptable, these are precisely the kinds of situations where the benefits outweigh the downsides.

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.

epolanski• 2 years ago

One reason why I've stopped putting business logic in databases bar rare times is that you tie your business logic in a defined dependency.

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.

neomantra• 2 years ago

When Redis introduced Modules, I liked to turn Redis into a mini-application server akin to what OP discussed with SQL. The article lays out the pros/cons of this pretty well. [I sorta did it with Redis Lua scripts too, but I preferred modules.]

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.

adsharma• 2 years ago

Code and Business Logic are terms not precise enough to describe.

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

evanelias• 2 years ago

The "deployment and versioning" piece is definitely solvable with the right tooling. Imperative migration tools are the wrong approach there, but declarative tools can offer a workflow very similar to how you version and deploy any other code.

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

tianzhou• 2 years ago

From the stored procedure Pro clan:

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.

Code in Database vs. Code in Application

Related Stories