9 ms·

Can you explain why you feel that "do everything in the database" is archaic? A lot of logic (especially authentication logic) can be put in the database only.

Can you explain why you feel that "do everything in the database" is archaic? A lot of logic (especially authentication logic) can be put in the database only. Not to mention that I won't trust anything that only has application level security, and nothing at database level to check/limit it.

hacker_9 10y ago
Productivity.
yoz-y 10y ago
From what I gathered one of the biggest problems of stored procedures is that they do not play well at all with source control and associated tooling such as code review.
- lostboys67 10y ago
  Rubbish you can check in your sprocs into a source control system along with the Database schema I never had any problems at BT when we did this. And why can you not code review your sprocs?
- djrobstep 10y ago
  I just tried to commit a stored procedure to git and it seemed to work. I then asked a colleague to review it. This also worked.
  - yoz-y 10y ago
    Ok. I did not know that. As I said this is what I have gathered from what I have read around as a common complaint. Out of curiosity, what is the testing-deployment cycle of stored procedures in source control? How do you assure yourself that the database uses a procedure coming from a specific commit?
    - jholman 10y ago
      How do you assure yourself that your non-database code uses code coming from a specific commit? In general, you have a deployment procedure that overwrites all the old code with all-new code, and from there on out you trust, right? Why not use the same approach?
renolc 10y ago
Absolutely. SQL is great, and having the DB just give you the correct data from the get go is convenient. But in the case where complex logic is necessary, SQL is much more difficult for correct implementation than something more expressive. You _could_ delve into cursors or long merge statements or what have you, but in business logic specifically, the code will be read and altered numerous times by several different people. In that case, a language made specifically for expressive statements is significantly easier to deal with. That's not to say that I believe the opposite is true and that _everything_ should be done in programming space. I just think there is a better balance that can be achieved, and to default having everything in either category probably means you're not balancing correctly.
- lostboys67 10y ago
  And your point is? yes sometimes you have complex business logic but throwing way all the benefits of using a RDBS is a sign that your developers cant hack SQL properly.
  - deleted 10y ago
    [deleted]
  - deleted 10y ago
    [deleted]
  - renolc 10y ago
    I would be the first one to admit my SQL chops are probably lacking, so perhaps this is just my own bias revealing itself. I'm not saying to _not_ use stored procedures. I'm just saying that throwing literally _all_ of the business logic in them feels a lot like a silver bullet. I've always felt that any of the more "standard" backbend languages would be a better choice for that complexity, since their expressiveness helps to describe that complexity in a way that is easier to grok for a larger number of developers.
    - lostboys67 10y ago
      you mean like COBOL :-)
alkonaut 10y ago
> Can you explain why you feel that "do everything in the database" is archaic I think it is because I want to be able to compile and test from end to end without having a certain database on hand. I consider the (specific choice of) DB to be an implementation detail, just like whatever file system the application might reside on once deployed. I know this is an idealistic point of view and sometimes you end up with terribly slow ORM multi-join code where a stored procedure update might have been very simple - but that's an optimization I'd like to keep until it's actually needed because of the flimsy guarantees and poor integration of SP tooling (If I misspell something in a a stored procedure can I be sure it's caught on my dev machine without having to run through integration tests?, etc)
Illniyar 10y ago
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.
FuNe 10y ago
I've been in this job long enough to know that there is no silver bullet in tech stack, methodology, technique, etc. Yet, it took me a while to get there and even if I did it means nothing because wherever I look I see S/W houses being governed by adherents of the this or that 'true religion'. I think that most of that is just cargo cults - i.e. what happens to work for this company (and whereupon people there believe in) is taken to be the way to go for any other company/product/case. Are SPs bad/evil/nice/safe/etc? I don't know and I cannot tell unless we are talking about something concrete. Senior devs were saying a few years ago that they are the holy grail. Senior devs are saying now that they are the devil. Go figure. Is TDD the holy grail? Dunno - these days TDD seems to be synonymous to progress and modernity. Any opposed view seem to belong to cavemen but wasn't that so with OOP just a few months before functional became the way to go? Anyway - you catch my drift. I'm doubly cautious when I hear people speak with the greatest conviction about this and that these days unless they are speaking off a concrete example.
throwanem 10y ago
Well, for one thing, auth logic can only live in the database when your application uses no third-party authentication providers, and while "archaic" is maybe a strong word, that's a less and less common situation these days. Even in the "enterprise" world, single sign-on via LDAP is generally the order of the day, and good luck doing that in a stored procedure...
Terr_ 10y ago
I'm fine with it as long as: 1. Everybody recognizes that "The Database" has now become its own unique product, which just happens to provide remote service-calls to other products over an SQL channel 2. I'm not the one responsible for managing the multi-tenant clusterfuck it will become
lagadu 10y ago
No separation between between the application, model and database layers? That makes it a huge problem making changes to just one of them, if you want to support a different database for example or move one of the layers somewhere else.

hacker_9 10y ago

Productivity.

yoz-y 10y ago

From what I gathered one of the biggest problems of stored procedures is that they do not play well at all with source control and associated tooling such as code review.

lostboys67 10y ago
Rubbish you can check in your sprocs into a source control system along with the Database schema I never had any problems at BT when we did this. And why can you not code review your sprocs?
djrobstep 10y ago
I just tried to commit a stored procedure to git and it seemed to work. I then asked a colleague to review it. This also worked.
- yoz-y 10y ago
  Ok. I did not know that. As I said this is what I have gathered from what I have read around as a common complaint. Out of curiosity, what is the testing-deployment cycle of stored procedures in source control? How do you assure yourself that the database uses a procedure coming from a specific commit?
  - jholman 10y ago
    How do you assure yourself that your non-database code uses code coming from a specific commit? In general, you have a deployment procedure that overwrites all the old code with all-new code, and from there on out you trust, right? Why not use the same approach?

renolc 10y ago

Absolutely. SQL is great, and having the DB just give you the correct data from the get go is convenient. But in the case where complex logic is necessary, SQL is much more difficult for correct implementation than something more expressive. You _could_ delve into cursors or long merge statements or what have you, but in business logic specifically, the code will be read and altered numerous times by several different people. In that case, a language made specifically for expressive statements is significantly easier to deal with. That's not to say that I believe the opposite is true and that _everything_ should be done in programming space. I just think there is a better balance that can be achieved, and to default having everything in either category probably means you're not balancing correctly.

lostboys67 10y ago
And your point is? yes sometimes you have complex business logic but throwing way all the benefits of using a RDBS is a sign that your developers cant hack SQL properly.
- deleted 10y ago
  [deleted]
- deleted 10y ago
  [deleted]
- renolc 10y ago
  I would be the first one to admit my SQL chops are probably lacking, so perhaps this is just my own bias revealing itself. I'm not saying to _not_ use stored procedures. I'm just saying that throwing literally _all_ of the business logic in them feels a lot like a silver bullet. I've always felt that any of the more "standard" backbend languages would be a better choice for that complexity, since their expressiveness helps to describe that complexity in a way that is easier to grok for a larger number of developers.
  - lostboys67 10y ago
    you mean like COBOL :-)

alkonaut 10y ago

> Can you explain why you feel that "do everything in the database" is archaic I think it is because I want to be able to compile and test from end to end without having a certain database on hand. I consider the (specific choice of) DB to be an implementation detail, just like whatever file system the application might reside on once deployed. I know this is an idealistic point of view and sometimes you end up with terribly slow ORM multi-join code where a stored procedure update might have been very simple - but that's an optimization I'd like to keep until it's actually needed because of the flimsy guarantees and poor integration of SP tooling (If I misspell something in a a stored procedure can I be sure it's caught on my dev machine without having to run through integration tests?, etc)

Illniyar 10y ago

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.

FuNe 10y ago

I've been in this job long enough to know that there is no silver bullet in tech stack, methodology, technique, etc. Yet, it took me a while to get there and even if I did it means nothing because wherever I look I see S/W houses being governed by adherents of the this or that 'true religion'. I think that most of that is just cargo cults - i.e. what happens to work for this company (and whereupon people there believe in) is taken to be the way to go for any other company/product/case. Are SPs bad/evil/nice/safe/etc? I don't know and I cannot tell unless we are talking about something concrete. Senior devs were saying a few years ago that they are the holy grail. Senior devs are saying now that they are the devil. Go figure. Is TDD the holy grail? Dunno - these days TDD seems to be synonymous to progress and modernity. Any opposed view seem to belong to cavemen but wasn't that so with OOP just a few months before functional became the way to go? Anyway - you catch my drift. I'm doubly cautious when I hear people speak with the greatest conviction about this and that these days unless they are speaking off a concrete example.

throwanem 10y ago

Well, for one thing, auth logic can only live in the database when your application uses no third-party authentication providers, and while "archaic" is maybe a strong word, that's a less and less common situation these days. Even in the "enterprise" world, single sign-on via LDAP is generally the order of the day, and good luck doing that in a stored procedure...

Terr_ 10y ago

I'm fine with it as long as: 1. Everybody recognizes that "The Database" has now become its own unique product, which just happens to provide remote service-calls to other products over an SQL channel 2. I'm not the one responsible for managing the multi-tenant clusterfuck it will become

lagadu 10y ago

No separation between between the application, model and database layers? That makes it a huge problem making changes to just one of them, if you want to support a different database for example or move one of the layers somewhere else.