4 ms·

The line of reasoning seems valid, do what you do best; anyone who has had to deal with stored procedures in legacy systems will however disagree. Store procedu

The line of reasoning seems valid, do what you do best; anyone who has had to deal with stored procedures in legacy systems will however disagree. Store procedures are to be avoided.

liotier 3y ago
What is wrong with stored procedures ? They are not fashionable nor especially expressive, but they perform reliably and they do pretty much what anything that might be expected from relational logic. But maybe that's my enterprisey bias.
- mtone 3y ago
  They're turing-complete and modular so it's not really about what they can or cannot do. Testability, tooling and the open-source ecosystem and either bad or non-existent. Writing PL/SQL is the worst environment I've worked in. That database sent emails, processed CSVs scheduled jobs, etc. yet there was still a web app to maintain next to it. They're OK for certain things like essential triggers or performance-sensitive functions, but I would never deliberately put app logic in there. Major red flag.
  - mixedCase 3y ago
    > Testability, tooling and the open-source ecosystem and either bad or non-existent If you're properly testing the code in your application that exercises persistence, that means your test harness runs a real database like the one you're running in production and thus you can also write the database logic tests using your own application's testing facilities. Of the things you listed, "the database sends e-mail" is the only one where I'd think you'd have to change the code at all, and have the database go through a mockable middle-man so that it becomes testable; but everything else can be comfortably tested from a test suite that is able to talk to a real database.
  - Scubabear68 3y ago
    Yep. Releasing, testing, debugging, etc are all more difficult in stored procs than in a “regular” language. Stored procs have other down sides: - often unique to that DB, so locks you in - Scaling that code is now tied to scaling your DB tier - Tooling is often very inadequate - Versioning and backwards compatibility of code can be a challenge
    - rnk 3y ago
      Some of those concerns apply to any database. Your query could slowdown if the database picks a bad plan, so you could say you will never trust the db to scale. That's separate from scaling the stored proc - just using the db can run into a scaling issue.
      - Scubabear68 3y ago
        No, what I mean is your code scaling is now directly tied to how your DB scales. Your SP code can be impacting the rest of your DB, and vice-versa. I have seen large SP based systems to require Oracle boxes to be scaled up at enormous cost (hundreds of thousands or even millions of lines of SP). Not because of slow queries, but just the cost of executing the stored procs themselves.
  - Shorel 3y ago
    > That database sent emails, processed CSVs scheduled jobs, etc. That's really a bad, very bad use of SP. They should only deal with and care about the data, not doing any interaction with any external systems.
- stcroixx 3y ago
  I've never worked on a system where we didn't make an effort to make sure we were NOT dependent on a specific DB vendor/implementation and avoiding SP's has always been a part of that.
  - pmlarocque 3y ago
    And in how many systems you ever ended switching from one database engine for another?
    - devonbleak 3y ago
      We went through and got Oracle out of our enterprise after a particularly nasty licensing negotiation and audit with them some years ago. So it does happen. I expect at some point we'll have a similar initiative around cloud providers.
  - tasuki 3y ago
    Yes, a very commendable goal, I've seen this effort undertaken many times. Curiously enough, I've never actually seen anyone switch databases.
    - lenkite 3y ago
      Depends on the product in question. For integration products, its very common to support multiple database vendors with the same core engine. So any SQL written and db data-types is generally SQL-92/99. This is pretty good since one can generally fast unit test DB code with an embedded database.
    - AmpsterMan 3y ago
      Once of the major lifts at my last job was going from Oracle to post Fred's. The speed procedures were the worst part of it all.
    - stcroixx 3y ago
      I’ve done it twice. Mass migration off Oracle and before that DB2 and Sybase. Current system is also occasionally sold on premise to customers who have their own db preference and we can support them all.
tasuki 3y ago
I used to deal with stored procedures in legacy systems and honestly it was not bad. The first concern was getting the stored procedures into version control and creating a mechanism to update the systems based on the things in version control upon deployment. After that it was smooth sailing. Why are stored procedures to be avoided?
- saltcured 3y ago
  I think most people are reacting to the impedance mismatch of normal software lifecycles versus sticky databases. Naive or legacy users and documentation use the database as the system of record for the custom code, rather than as an execution environment provisioned from a real system of record like a source control system. Where it gets even more tricky is not just stored procedures but application-specific functions embedded in views, or triggers running custom functions. It's no longer just a library of functions you can choose to call (or not) durng a query, but code that runs on its own based on clients queries that never directly mention the functions. The same goes for schema management, and I think that is a big reason why so many developers fixate on "schemaless" approaches. They want to pretend that the database exists in a static way outside the software lifecycle, just like they ignore the filesystem and operating system and treat it as an unchanging abstraction.
Shorel 3y ago
Store procedures and functions are very good for many things. I am not claiming you should put all your business logic in the database, like old-time Oracle consultants wanted (for self-serving reasons), but some calculations are better handled in a single place, instead of being reimplemented in different parts of the stack, or stacks, given that it is common to write a project using one framework and language, and then migrate to another one. The only reason to avoid SP is that you don't know any SQL and your ORM can't write the function calls, so you can't call them. Which is a general complaint I have about modern software development: many things are done in convoluted ways because some developers don't know SQL and don't want to learn it.

The line of reasoning seems valid, do what you do best; anyone who has had to deal with stored procedures in legacy systems will however disagree. Store procedures are to be avoided.

liotier 3y ago

What is wrong with stored procedures ? They are not fashionable nor especially expressive, but they perform reliably and they do pretty much what anything that might be expected from relational logic. But maybe that's my enterprisey bias.

mtone 3y ago
They're turing-complete and modular so it's not really about what they can or cannot do. Testability, tooling and the open-source ecosystem and either bad or non-existent. Writing PL/SQL is the worst environment I've worked in. That database sent emails, processed CSVs scheduled jobs, etc. yet there was still a web app to maintain next to it. They're OK for certain things like essential triggers or performance-sensitive functions, but I would never deliberately put app logic in there. Major red flag.
- mixedCase 3y ago
  > Testability, tooling and the open-source ecosystem and either bad or non-existent If you're properly testing the code in your application that exercises persistence, that means your test harness runs a real database like the one you're running in production and thus you can also write the database logic tests using your own application's testing facilities. Of the things you listed, "the database sends e-mail" is the only one where I'd think you'd have to change the code at all, and have the database go through a mockable middle-man so that it becomes testable; but everything else can be comfortably tested from a test suite that is able to talk to a real database.
- Scubabear68 3y ago
  Yep. Releasing, testing, debugging, etc are all more difficult in stored procs than in a “regular” language. Stored procs have other down sides: - often unique to that DB, so locks you in - Scaling that code is now tied to scaling your DB tier - Tooling is often very inadequate - Versioning and backwards compatibility of code can be a challenge
  - rnk 3y ago
    Some of those concerns apply to any database. Your query could slowdown if the database picks a bad plan, so you could say you will never trust the db to scale. That's separate from scaling the stored proc - just using the db can run into a scaling issue.
    - Scubabear68 3y ago
      No, what I mean is your code scaling is now directly tied to how your DB scales. Your SP code can be impacting the rest of your DB, and vice-versa. I have seen large SP based systems to require Oracle boxes to be scaled up at enormous cost (hundreds of thousands or even millions of lines of SP). Not because of slow queries, but just the cost of executing the stored procs themselves.
- Shorel 3y ago
  > That database sent emails, processed CSVs scheduled jobs, etc. That's really a bad, very bad use of SP. They should only deal with and care about the data, not doing any interaction with any external systems.
stcroixx 3y ago
I've never worked on a system where we didn't make an effort to make sure we were NOT dependent on a specific DB vendor/implementation and avoiding SP's has always been a part of that.
- pmlarocque 3y ago
  And in how many systems you ever ended switching from one database engine for another?
  - devonbleak 3y ago
    We went through and got Oracle out of our enterprise after a particularly nasty licensing negotiation and audit with them some years ago. So it does happen. I expect at some point we'll have a similar initiative around cloud providers.
- tasuki 3y ago
  Yes, a very commendable goal, I've seen this effort undertaken many times. Curiously enough, I've never actually seen anyone switch databases.
  - lenkite 3y ago
    Depends on the product in question. For integration products, its very common to support multiple database vendors with the same core engine. So any SQL written and db data-types is generally SQL-92/99. This is pretty good since one can generally fast unit test DB code with an embedded database.
  - AmpsterMan 3y ago
    Once of the major lifts at my last job was going from Oracle to post Fred's. The speed procedures were the worst part of it all.
  - stcroixx 3y ago
    I’ve done it twice. Mass migration off Oracle and before that DB2 and Sybase. Current system is also occasionally sold on premise to customers who have their own db preference and we can support them all.

tasuki 3y ago

I used to deal with stored procedures in legacy systems and honestly it was not bad. The first concern was getting the stored procedures into version control and creating a mechanism to update the systems based on the things in version control upon deployment. After that it was smooth sailing. Why are stored procedures to be avoided?

saltcured 3y ago
I think most people are reacting to the impedance mismatch of normal software lifecycles versus sticky databases. Naive or legacy users and documentation use the database as the system of record for the custom code, rather than as an execution environment provisioned from a real system of record like a source control system. Where it gets even more tricky is not just stored procedures but application-specific functions embedded in views, or triggers running custom functions. It's no longer just a library of functions you can choose to call (or not) durng a query, but code that runs on its own based on clients queries that never directly mention the functions. The same goes for schema management, and I think that is a big reason why so many developers fixate on "schemaless" approaches. They want to pretend that the database exists in a static way outside the software lifecycle, just like they ignore the filesystem and operating system and treat it as an unchanging abstraction.

Shorel 3y ago

Store procedures and functions are very good for many things. I am not claiming you should put all your business logic in the database, like old-time Oracle consultants wanted (for self-serving reasons), but some calculations are better handled in a single place, instead of being reimplemented in different parts of the stack, or stacks, given that it is common to write a project using one framework and language, and then migrate to another one. The only reason to avoid SP is that you don't know any SQL and your ORM can't write the function calls, so you can't call them. Which is a general complaint I have about modern software development: many things are done in convoluted ways because some developers don't know SQL and don't want to learn it.