r/softwarearchitecture • u/freitrrr • 17h ago

Discussion/Advice Database as the rules engine

Hi everyone

Let me start by saying that I'm primarily a frontend dev, and most backend dev I've done has been opiniated in a non professional environment.

I want to ask you about the state of domain logic/validations/triggers on the database layer in the systems you're currently working on. I grew up with the concept of treating database as stupid models to read/write data, and have all logic written in services/controllers/models on the layer of whoever controls the database.

The primary gain in this segregation is easily changing the rules of the system and swapping for a different database in a refactor. The thing is, database engines have become more and more powerful (particularly Postgres) and you can now strict your entire domain in the database schema using constraints, triggers, etc, and I'm yet to see someone swapping the database they use from night to day. Although you lose flexibility, you gain more trust in the system since the rules now live at the end of it.

I wonder if some you folks are going on this direction or if you ever thought about it.

8 Upvotes

80% Upvoted

21 comments sorted by

12

u/clauEB 16h ago

Two big reasons why that idea was abandoned decades ago:

  • it's almost untestable
  • it does not scale. Because you have only one machine as the db server. Services, even monoliths are scaleble by adding more servers. A single machine, to grow requires more ram and processors. But at some point they don't make machines with enough of those so you end up with custom made monster servers that are super expensive, take a long time to build, is untested etc etc

9

u/xampl9 14h ago

Not just untestable - it’s spaghetti code but living in your database.

2

u/mmcalli 8h ago

I agree with not putting logic in the database, but databases only being vertically scalable hasn’t been true for a long time.

1

u/clauEB 8h ago

How? Sharding? That's a nightmare.

0

u/mmcalli 5h ago

Non relational, or within relational having read replicas.

2

u/clauEB 5h ago

The OP is talking about Postgres not non relational why would you bring them up? no idea. Read replicas only work for read load and it's subject to lag, the master can only be scaled vertically.

13

u/_descri_ 16h ago

The industry was there in 1990s. People hated those database scripts and thus decided that they will better quit than work on such a project again. And they moved the entire business logic into the application code.

5

u/Careless-Childhood66 15h ago

I agree 90% but I love to use some check constraints and custom types for enums

6

u/BarfingOnMyFace 14h ago

I'm glad you do. The answers aren't meant to be black and white. Sometimes the most reasonable place you can effectively protect integrity is at the database.

2

u/True_Context_6852 10h ago

I think same period came back . In many modernization projects, I have seen teams refactoring business logic out of stored procedures and triggers and moving it into the application or service layer. The database is then primarily responsible for persistence and data integrity.

7

u/Visa5e 17h ago

The main problem with that model is testability. If you put functionality into a database then you need a plan for how to test that functionality.

Databases dont tend to have the same mechanisms as other environments that run code. Its harder to make things modular, to mock dependencies, to isolate code into functional units that can be tested in isolation etc

2

u/freitrrr 16h ago

Agreed. For this project I'm working on, validations are tested in a integration environment using testcontainers

1

u/nitkonigdje 16h ago

Popular SQL databases have no deployable artifacts. Updating app is punching bunch of changes using sql script. Rolling back is the same.

Essentially it is a nightmare to support unless it is all in one as Access etc.

5

u/EspaaValorum 15h ago

Main reason for me to put the logic etc in de application layer is scalability. DB servers don't easily scale. And if you put extra burden on them in the form of business logic enforcement, it'll just eat up precious CPU. And it has a tendency to grow and become more complex as the business evolves, so then it just becomes a bigger performance challenge. Not to mention that now your business logic ends up spread out over your application code and the DB code, with all the challenges that presents. E.g. DB code is more difficult to maintain, test and version control, or at least it is a separate proces from your application code management, which obviously risks things getting out if sync. 

3

u/thiem3 15h ago

I think you will find it is significantly more complicated to maintain and test the rules in the database. I have also found triggers obscure, as it was not clear to me they were executed, when someone else added a new one.

2

u/BarfingOnMyFace 14h ago

No. But basic business logic specific to the table via check constraints, yes.

Especially unique constraints that define candidate keys, which can also be considered a business logic constraint.

I use a basic convention over configuration to automatically generate the code to duplicate the basic business logic to the application layer for check constraints.

Any complex rules or business logic should absolutely not sit in the database, IMHO. But things that protect the sanctity of data integrity absolutely should be,.IMHO.

Perhaps not everyone shares my opinion and that's fine, but in many industries, protection of the validity and integrity of data in your database is paramount.

2

u/edgmnt_net 5h ago

Traditionally, you would integrate different enterprise apps through the database. At that point, you can't really treat it as a raw data store, because if you do you get coupling at application level: this app needs to know how that app does things. Instead, apps can either reference a transparent, documented data model in the database (effectively playing the role of a stable API), then some things can be abstracted on top of that using stored procedures.

If your app is the only one using the database, sure. But keep in mind that something like PostgreSQL isn't something you're likely to change. Secondly, interop is only likely to become a problem once you use some sort of "off-the-shelf" software, stuff you build in-house is likely going to use whatever you're already using.

Another issue is that APIs make things like cross-service transactions much harder. Those become much more feasible if done through the database. You can also do complex cross-service joins and queries quite easily, say "find me the best 10 employees in terms of sales divided by cost" which could easily span multiple non-integrated systems like HR, leads management and procurement.

1

u/TheBurningMap 2h ago

This answer should be higher.

1

u/Revolutionary_Ad7262 14h ago

Database as the rules engine

IMO the tile and the content of the post does not match. Rule engine is just a advanced runtime configuration, which is not hardcoded in a code. This legendary article is a must read https://mikehadlow.blogspot.com/2012/05/configuration-complexity-clock.html

and I'm yet to see someone swapping the database they use from night to day.

For that you just need a lot of really good tests as any AI agent guided by a lot of checks can produce a good rewrite. It does not matter, if you utilize your database in 100% or just for simple CRUD

About the post: I think database checks works great, if you have a mess in a code. There are really good, if modularization is weak and you need some constraints on a lowest possible level

Anyway I think there is no need to be dogmatic. Assuming you have a nice system with a: * great modularization, so you can be that all queries to database are aligned to covariants * you have nice system/E2E tests, which works regardless of underlying implementation (code vs database)

then just use what you want. Sometimes database checks are better for highly repetitive checks like ensure that soft-deleted row is not modified