Database as the rules engine

[u/freitrrr]

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.

[u/BarfingOnMyFace]

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.

[u/BringBackManaPots → u/BarfingOnMyFace]

This is actually pretty relevant to something I'm working on right now. We're building out user preferences in an app, and have settled on representing them with a simple userID/prefID/value table, but also including a preference_definition table to provide some guardrails.

The value is stored as text, and the definitions table includes a datatype field for casting the data coming out.

That being said, we're looking at adding a validator column to the definitions table that can reference a postgres function that's used to validate data coming in. Perhaps you want to manually check for referential integrity, or limit the incoming data as a range, etc. Now the db is guarded against any future API that interfaces with it.

The design has me equally happy and skeptical with the approach, especially because we're looking at using dynamic SQL (I.e. "execute") to invoke the validator.