Hacker News Reader

Comment by maxk42 | Hacker News Reader

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.

View on HN

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

What do you mean? Isn’t your database precisely where you ought to put your business logic?

Is this a joke? SPs and SQL Functions are far less maintainable than .NET/Java/Node/Python.

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.

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?

Unrelated, but which ones of these do you recommend (also factor in LiquiBase) that's free?

As far as automatic tests for dbs - I have no idea what you are talking about unless you mean integration testing through a backend.

I’d recommend whichever one is available in a language the project uses already.

Automatic tests for a database could simply be writes followed by reads with expectations for the returned rows. This is how Postgres tests itself.

My personal go-to right now is ActiveRecord and RSpec. The examples could just as easily run raw SQL directly using the Ruby pg driver.

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.

"But it can be much more efficient to run business logic in the database as stored procedures."

But this is not code, this SQL or equivalent, if SQL was known to be better than code to do busines logic everyone would do that.

How is this not code: https://oracle.su/docs/11g/appdev.112/e10472/packages.htm#in... ?

What is your definition of code?

There's all sorts of programming styles in use and calling stored procedures from dot net programs for instance is at least a thing.

Plus, lots of those ORM things get plenty of hate from DBAs, I mean I like them but then again I'm not a very good programmer.

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.

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.

Validation on the client, for user convenience. Validation on the backend, for security. Validation on the DB, for sanity.

Error states are a bit funky if you don't validate in the backend logic.

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.

Wut? You put data in a database. Logic goes in the app.

In some cases, things like constraints and the model itself represent business logic.

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.

1. https://en.wikipedia.org/wiki/Business_logic

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.

Where exactly do you draw the line?

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?

This isn't really business logic... unless you're in the business of simply converting data formats.

As a data fetching approach and especially as tooling primitive to eliminate the n+1 ORM problem this is a terrific tool to be aware of.

Thanks for sharing now wisdom, now if you could also explain why?

The posted article doesn't advocate for putting business logic in the DB.

It only describes postgres native functions for formatting data.

Reply on news.ycombinator.com