5 ms·

Ask HN: Why shouldn't business logic sit in the database?

I'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. 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? What are your thoughts?

PaulHoule 7y ago
see https://en.wikipedia.org/wiki/IBM_Notes https://en.wikipedia.org/wiki/IBM_Notes
judge2020 7y ago
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.
deleted 7y ago
[deleted]
ebcode 7y ago
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 7y ago
  > 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-logic-in-the-database-yes-or-no-it-depends https://www.vertabelo.com/blog/notes-from-the-lab/business-l...
JamesBarney 7y ago
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 7y ago
  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.
cyndijon1 7y ago
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.
Recce70 7y ago
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. (

PaulHoule 7y ago

see https://en.wikipedia.org/wiki/IBM_Notes https://en.wikipedia.org/wiki/IBM_Notes

judge2020 7y ago

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.

deleted 7y ago

[deleted]

ebcode 7y ago

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 7y ago
> 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-logic-in-the-database-yes-or-no-it-depends https://www.vertabelo.com/blog/notes-from-the-lab/business-l...

JamesBarney 7y ago

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

cyndijon1 7y ago

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.

Recce70 7y ago

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