3 ms·

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 nea

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.

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.