
Ask HN: Why shouldn't business logic sit in the database? - vanilla-almond
I&#x27;m increasingly of the view that business logic should sit in the database. As much of it as possible. Not in a separate layer outside, or in code that connects to the database. I suspect some of you will be horrified.<p>You could argue that the mere creation of a database schema with primary and foreign keys is already business logic. So why not include as much of that business logic in the database, rather than spin it out into a layer of code?<p>What are your thoughts?
======
Recce70
In the 90s and early 00s I worked on applications where the bulk of the
business logic / processes have been encoded into Stored Procedures and
sometimes Triggers. And it's easy in a lot of ways because you're working
"close to the data". The issue it that SQL isn't the best language for
encapsulating complicated and / or changing business logic.

From a design perspective you end up creating "transaction scripts". This is
great if what you are creating is simple and unlikely to change. As complexity
increase and you received more and more Change Requests it becomes harder.
Dino Esposito's book "Microsoft .NET: Architecting Applications for the
Enterprise" covers this well and my experience reflects what he says. And
while it's also easy to create "transaction scripts" in C#/Java, you can't
take a Domain-Driven Design approach with T-SQL.

I also agree with Brent's comment below, testing is far harder, although
whatever project you're on automated testing has to be fully embraced or it's
just overhead. (

------
JamesBarney
I wouldn't recommend it. I've worked on a couple of systems where all the
business logic was placed into stored procedures. Problems I ran into.

SQL is not nearly as expressive as modern languages.(to get similar
functionality to things like polymorphism/interfaces/first class functions you
have to do a bunch of clunky hacky stuff)

Do to this lack of expressiveness developers turn to dynamic sql which is far
more risky and opens the application up to SQL injection attacks.

The tooling is lacking, most sql development IDE's aren't nearly feature rich
as modern language IDE's.

Testing is much more difficult.

You will be shackled to your database choice.

It's harder to find developers who are experts in your chosen database dialect
such as T-SQL/PL-SQL/PostgreSQL/MySql as well as experts in your back end
language.

You end up having to write a bunch of boiler plate code to wrap all of your
stored procedures for your back end.

What are the benefits you're hoping to achieve that outweigh these costs?

~~~
BrentOzar
To echo and debate some of these:

> developers turn to dynamic sql which is far more risky and opens the
> application up to SQL injection attacks.

Just to be clear, I've seen SQL injection hit on the app side when people
build queries in the app, too. I agree with you - it's more commonly seen in
SQL that generates SQL, though.

> The tooling is lacking, most sql development IDE's aren't nearly feature
> rich as modern language IDE's.

In particular, debugging queries, especially multi-step things like procedures
and functions, is much more challenging than it is for in-app code. Code
completion is also a problem, not a strength of most database IDEs.

I'm guessing the original post author posed this question because he's tired
of bad data development experiences in his code IDE, and excited by how query
design looks at first glance in a database IDE. The grass looks greener when
you put the logic in the database, but it's only the top layer of grass that
looks good.

> Testing is much more difficult.

I can't emphasize this enough - it can be done, but it's HARD.

I'll toss in one advantage for putting the logic in the database layer: write
it in stored procedures and functions, and test it once, and then you can have
as many front ends as you want without having to re-test all of them. That's
what the brochure says - but the reality is that each front end wants its data
slightly differently, or wants to do different things with it, so you still
end up writing lots of different data access pieces, each of which needs its
own testing, and you're right back to square one.

------
ebcode
I'm not horrified, as I've had similar thoughts to yours lately. I do think
you might get a better response if you could extrapolate on your reasons for
moving the logic into the database. Maybe a blog post?

Your argument that a schema w/ keys is already "business logic" could be
refuted with the stance that the database is actually the "business model",
and the logic is in _how_ the rows and columns are updated, which takes place
(and should) in the "application", not the "storage mechanism".

What I imagine is that because SQL was not originally conceived to be a turing
complete language, the possibility of having your business logic _in_ the
database was simply not there.

The possibility now exists, and I've run into projects where stored procedures
do a _lot_ of heavy lifting, but I've never seen an example where stored
procedures do _all_ of it. After all, you wouldn't write your UI w/ a stored
procedure.

~~~
taffer
> the logic is in how the rows and columns are updated, which takes place (and
> should) in the "application", not the "storage mechanism".

Business logic is any logic that exists to encode business requirements[1],
everything else is just plumbing. If there is a requirement that each product
must have a category, then you need business logic to enforce this. If there
is a requirement that a price must not be negative, then that is business
logic. Where to put this logic? I would put it in the database.

> What I imagine is that because SQL was not originally conceived to be a
> turing complete language, the possibility of having your business logic in
> the database was simply not there.

SQL is based on relational algebra, which is based on first order logic. So it
is perfect for business logic [2] and it rarely takes a turing complete
language to implement business requirements.

[1]
[https://en.wikipedia.org/wiki/Business_logic](https://en.wikipedia.org/wiki/Business_logic)

[2] [https://www.vertabelo.com/blog/notes-from-the-
lab/business-l...](https://www.vertabelo.com/blog/notes-from-the-lab/business-
logic-in-the-database-yes-or-no-it-depends)

------
cyndijon1
I love having business logic in the database. Nothing changes faster than your
customers minds. Having it in the DB allows for quick and easy changes without
having to go through a painful tedious build and deploy cycle, which can be
especially painful when you have a billion front ends touching a smallish
number of database servers. Also, if you're trying to "hack" SQL by attempting
to code interfaces and classes, you're doing it wrong. SQL is not your
application language, please stop! Perhaps study SQL for a minute or two
before attempting your needless hacks. Your DBA will thank you.

------
judge2020
I wouldn't say primary or foreign keys in databases is business legic (unless
your code relies on the primary/foreign key existing for relationship logic);
my view is that these keys are meant to enforce data integrity, not define it.

------
PaulHoule
see

[https://en.wikipedia.org/wiki/IBM_Notes](https://en.wikipedia.org/wiki/IBM_Notes)

