5 ms·

Logic in the Database has the following issues: 1. it doesn't work well with source control 2. deployments, rollback, replication, synchornization - they don'

Logic in the Database has the following issues: 1. it doesn't work well with source control 2. deployments, rollback, replication, synchornization - they don't work very well with db procedures 3. unless you connect directly to the db, then you must have some logic on the serverside, usually you end up replicating logic from the db to the serverside 4. Databases languages (even advanced ones like PL/SQL) are not expressive enough 5.It's much easier to scale out the server then the database (and if you are using Oracle/Sql-server etc... also cheaper), and you don't want your database's cpu to be clogged with logic code execution 6.Unit testing (or any testing) is extremely difficult 7.Debugging is hard and convoluted (also it doesn't usually work inside your IDE) and a whole lot more. Nothing is absolute or completely obsolete, but it is considered a bad practice for a long time by most industry professionals. As far as I know the most popular article about it is: https://blog.codinghorror.com/who-needs-stored-procedures-anyways/ https://blog.codinghorror.com/who-needs-stored-procedures-an...

waldfee 10y ago
4, 6 and 7 are my main problems with this. i occasionally have to work with a big application which is essentially written 100% in sql. you simply can't easily change parts without testing the whole thing from start to finish because automated testing at a granular level is horrible. and sql does not lend itself to encapsulation, it does everything to make it hard to break stuff down to manageable pieces and in sql everything you do is just so complicated, tons of boilerplate stuff you would not have in a real programming language. it's called "query language" after all, not "programming language". so don't use it for that
- collyw 10y ago
  Its not that the logic goes into SQL that is the problem (that I usually see), its usually a poor database design and a load of code at the application level to compensate for the poor database design.
- amorphid 10y ago
  If "put your code in the database" becomes a trending fad into which I am dragged, I'm going to write a MyFavoriteLanguage-to-SQL transpiler.
lostboys67 10y ago
All of that apart from 5 is totaly wrong
- mmoroz 10y ago
  Care to elaborate a bit? Especially, why do you think unit testing and debugging of SQL is not hard?
  - lostboys67 10y ago
    I don't see that writing code to test SPROCs is that hard just have a set of inputs that match all of the use cases including all the edge ones run that through and check that the results on the db are as expected. Debugging mm possibly slightly harder in that you might have to have a 3rd monitor for Toad or work Manager - but you code your sprocs properly in the first place you should not have that many problems that jump between code and sql . Just saying its hard doesn't help in that case we ought to still be coding in GWBASIC
  - coldtea 10y ago
    >Care to elaborate a bit? Especially, why do you think unit testing and debugging of SQL is not hard? An SQL returns results directly in tables which you can check in all kinds of ways. You can create any number of temporary tables, with the same schema as your business tables, and check all kinds of invariants. There's absolutely no reason why unit testing SQL should be harder than anything else, considering a single query as the "unit" of testing. In fact, that's to the built-in checks, constraints and types a RDBMS has, you are freed from having to unit test all kinds of crap too (similar to having less to unit test in Haskell vs Ruby).
    - lagadu 10y ago
      Well, you'll have to store your mock data somewhere, which in this case means more databases, often on other servers; so you'll have your SPs connecting to another DB in order to access their data.
  - jholman 10y ago

waldfee 10y ago

4, 6 and 7 are my main problems with this. i occasionally have to work with a big application which is essentially written 100% in sql. you simply can't easily change parts without testing the whole thing from start to finish because automated testing at a granular level is horrible. and sql does not lend itself to encapsulation, it does everything to make it hard to break stuff down to manageable pieces and in sql everything you do is just so complicated, tons of boilerplate stuff you would not have in a real programming language. it's called "query language" after all, not "programming language". so don't use it for that

collyw 10y ago
Its not that the logic goes into SQL that is the problem (that I usually see), its usually a poor database design and a load of code at the application level to compensate for the poor database design.
amorphid 10y ago
If "put your code in the database" becomes a trending fad into which I am dragged, I'm going to write a MyFavoriteLanguage-to-SQL transpiler.

lostboys67 10y ago

All of that apart from 5 is totaly wrong

mmoroz 10y ago
Care to elaborate a bit? Especially, why do you think unit testing and debugging of SQL is not hard?
- lostboys67 10y ago
  I don't see that writing code to test SPROCs is that hard just have a set of inputs that match all of the use cases including all the edge ones run that through and check that the results on the db are as expected. Debugging mm possibly slightly harder in that you might have to have a 3rd monitor for Toad or work Manager - but you code your sprocs properly in the first place you should not have that many problems that jump between code and sql . Just saying its hard doesn't help in that case we ought to still be coding in GWBASIC
- coldtea 10y ago
  >Care to elaborate a bit? Especially, why do you think unit testing and debugging of SQL is not hard? An SQL returns results directly in tables which you can check in all kinds of ways. You can create any number of temporary tables, with the same schema as your business tables, and check all kinds of invariants. There's absolutely no reason why unit testing SQL should be harder than anything else, considering a single query as the "unit" of testing. In fact, that's to the built-in checks, constraints and types a RDBMS has, you are freed from having to unit test all kinds of crap too (similar to having less to unit test in Haskell vs Ruby).
  - lagadu 10y ago
    Well, you'll have to store your mock data somewhere, which in this case means more databases, often on other servers; so you'll have your SPs connecting to another DB in order to access their data.
- jholman 10y ago