It is a really, really bad idea to put business logic in your database and there are people in this thread downvoting everyone who points it out. Please do not do this in production - I've had to maintain systems that married business logic and database and it was a nightmare to maintain and cost a fortune to fix.
> It is a really, really bad idea to put business logic in your database and there are people in this thread downvoting everyone who points it out
Your database is your business logic. From constraints to the queries you write, it's all business logic.
Regardless, you misunderstood the article. This is about moving formatting into the db layer. Letting Postgres return JSON, as opposed to middleware code to translate rows to JSON.
Which is like the opposite of business logic code.
Like everything, it depends. Some business logic like "this field should never be negative" is probably fine.
More complex logic is a lot harder to maintain. You don't want to stress your DB's CPU if you can avoid it. You still need some kind of connection pooler anyway. There's a lot of reasons it doesn't make sense to put all your business logic in the DB. SQL is not the easiest language to maintain (not enough abstraction).
IMO the line to draw is just enough to keep data sanity.
I like this distinction from wikipedia [1] "Anything that is a process or procedure is business logic, and anything that is neither a process nor a procedure is a business rule."
Meaning constraints and the model represent business rules.
Why are they less maintainable? It’s just code you can write in a code editor, version control in a versioning system, have automatic tests for, have automatic deployment scripts for, etc. In the end the database is just an application server executing your code close to the data. Only thing you have to ask yourself is how much your code needs to scale. But it can be much more efficient to run business logic in the database as stored procedures.
I personally have found it's much more difficult to partially mock out concepts when writing logic in the DB layer though my tooling may have been the weakpoint. In a nice application language I can easily isolate specific units of code for direct testing - while every test I write over complex database logic feels like composing a symphony where I need to make sure the horns are coming in on the right pitch and the wind section isn't too early to properly simulate a relatively easy error scenario.
Since the application layer is much more naturally aligned with small well isolated chunks of logic it is easier to minimize the volume of code and logic in scope when testing a particular attribute - when that goes into the DB I've always seen things get more complex rapidly.
Using a migration utility like ActiveRecord, Flyway, or go-pg, SQL definitions can also live in version control, have automatic tests, and deploy automatically. Who has a database in production without these things?
That's perhaps true of stored procedures, but SQL views, constraints, and triggers written in a declarative manner are far more maintainable and far less buggy than imperative application code, in my experience.
I have no words for "triggers" and "maintainable" in the same sentence - enjoy your deadlocks and having no clue why a single write says "UPDATED x records" y times without having to dig down through an ancient UI.
I am actually "good" at this stuff - writing logic in SQL through triggers, which call functions (at which SQL Server is way better than PG is) and stored procs, etc, and it's just a horrible, horrible practice.
Business logic goes into the code, data goes into the database.
Sometimes business logic is implemented as rules, in which case the rules (configuration) can go into either configuration files or a database. But that doesn't make it data...
Data is just crystallized business logic. Anemic databases aren’t a big deal when the database is just a persistence layer for a single app but, when you’re rewriting the application code or allowing BI queries directly against the database, designing a database as a service whose interface is stored procedures starts making data integrity much easier.
Once you start having multiple data sources, business logic implemented in application code might be easier to reuse than business logic implemented in one of the data sources. For example: start with Postgres, then add ClickHouse to the mix. It might be easier to let the application handle access to both DBs, rather than write RLS policies in Postgres and then try to apply them to ClickHouse.
I think this is slightly reductive. The majority of your business logic, I agree, should reside in application code, but lightweight business logic in the form of database constraints are powerful at disallowing bad data. For example, you may have a rental booking application that shouldn't have overlapping date ranges. The database can give you this guarantee out of the box and will work for ALL clients and applications.
The boundary between business logic and database validation logic is a fuzzy one. In one extreme everything goes in stored procedures inside an RDBMS, at the other extreme you right everything on top of a no-frills key value store.
I would say 10 years ago yes. However now days with the way postgres is developing and with support for python/v8, and postgresREST. For db intensive stuff, it just make sense to do it in DB like postgres.
how old were the systems? And have you ever had to maintain systems that were that many years old that weren't a nightmare to maintain and cost a fortune to fix?
