6 ms·

As a counterpoint, Derek Sivers says Simplify: move code into database functions <https://sive.rs/pg https://sive.rs/pg> I think they are both right, Alex Kond

• scarface74 3y ago
  When I was choosing jobs in the real world[1], I would avoid any company that depends heavily on stored procedures for business logic and where things were ruled by “database developers” and all the developers were doing were calling stored procedures. Stored procedures are harder to unit test, do automated rollbacks, version control, etc. I’ve never seen a system that 5 years in anyone said “I’m so glad we decided to use stored procedures everywhere” [1] I work in consulting now and have no allusions that I face the same issues that day to day developers do. I get to lead development “solutions” and move on.
  • horsawlarway 3y ago
    It's worse than that. Your database will be the first major thing to cave under load. The traditional tools most folks use (postgres/mysql/maria/etc) want to scale vertically, not horizontally (you can do it, but it's hard, and has lots of drawbacks). Eventually - You will hit hardware limits. Providers only have instances that are so big. There is a ceiling on how much you can scale your DB without having to do major, major work. I've seen this pattern play out now at 3 high growth companies I've worked at. The one that was using lots of stored procs and materialized views got hit the fastest and the hardest.
    • pjmlp 3y ago
      When it happens it is time to actually use the big boys databases.
      • scarface74 3y ago
        I’ve worked with the big boys and seen plenty of times where databases fell over no matter how much data you through at it because of locking issues and other database contention issues. It’s much easier to horizontally scale a bunch of application servers than database servers.
        • pjmlp 3y ago
          That is why big boys databases do clusters.
          • scarface74 3y ago
            “clusters” only help when you can deal with eventual consistency (which is often admittedly more than most businesses realize). Sure you can scale your read only instances. BTW: I work in the consulting department of a little company I am sure you have heard of that knows something about dealing with “the big boys” at scale.
            • pjmlp 3y ago
              Great, me too, several of them actually.
      • AdieuToLogic 3y ago
        > When it happens it is time to actually use the big boys databases. For years Oracle had a very small limit of how many CPU's stored procs could use baked into the DBMS engine. IIRC, it was something like 4. Maybe that's changed now, I don't know. I do know Oracle qualifies for most as being one of "the big boys databases."
      • Shorel 3y ago
  • 0xCMP 3y ago
    I would agree, but I think it's worth checking out one of Siver's postgres repos where I thought he tested things pretty well[0]. Also things like Postgres are often run as a "server" on local dev or CI but could easily be run more ephemerally[1]. That should help with testing various states and behavior of the database. I still would actually follow what you said thought because often times places which rely on stored procedures are, as you say, ruled by developers with the database access to manage those procedures and from what I've seen often do not have anything close to a reproducible setup. Besides the fact that often it's some proprietary database which is much more difficult to run locally than sqlite3 or PostgreSQL. Still I keep that separate from considering if views/stored procedures are actually a good solution. I think sometimes it really is. Honestly Sivers' experiments really convinced me of that. [0]: https://github.com/sivers/store https://github.com/sivers/store [1]: https://jamey.thesharps.us/2019/05/29/per-project-postgres/ https://jamey.thesharps.us/2019/05/29/per-project-postgres/
  • pjmlp 3y ago
    It is a matter of database tooling, Oracle and SQL Server provide quite good IDEs, graphical debugging, merge tooling, testing infrastructuring. I can tell you that at least a pharmaceutical is quite happy to have plenty of stored procedures into their Oracle databases, including making use of Apex, and it is at least several decades in production.
    • scarface74 3y ago
      And when you get ready to rollback, can you just revert all of your code and deploy? Can you just do a “git branch” and work on your own isolated code in your dev account?” How well does merging work when you do a pull request and have to merge your code and make sure your stored procedures are up to date?
      • pjmlp 3y ago
        Yes, because there is such thing as CI/CD pipelines, and test environments, for database code as well.
        • scarface74 3y ago
          If there are ten developers with 10 feature branches are they going to be running 10 instances of Oracle with their own “branches” of the stored procedures?
          • pjmlp 3y ago
            Yes,install the RDMS into their computer just like any other development tool. Yes, learn to use multiple schemas and instances, just like using multiple deployments into Apache, Tomcat, IIS,... Or even better, join the hype, and use containers with volumes configured for each feature branch.
            • scarface74 3y ago
              And then copy “enough” data on their computers to have a realistic test and make sure you have some type of script so sensitive data isn’t on their laptops and ensure schema changes are constantly propagated to each developers workstation. This isn’t even mentioning if you have a commercial database like Oracle where you have to deal with licenses. And how do you merge changes and do rollbacks? Not to mention the whole GetCustomer_1 GetCustomer_2 GetCustomer_3 Where code is copy and pasted and modified slightly.
• richardjennings 3y ago
  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
• throwaway201606 3y ago
  Tom Kyte, who for a long time was the "ambassador to the world" for Oracle, makes essentially the same arguments https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2143974700346554115 https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUEST... For data that will either : + 'outlive' the user interface - this data will be used forever but we are not sure where e.g. financial docs, records, etc etc + 'extend' past the user interface: this data started on an installed desktop app / green screen but we are not sure how we may want to extend it - eg to an API or to web the DB is the right place to put business logic. Coupling the data with business logic, in the DB, allows almost complete flexibility in how it is access and interacted with at the expense of complete lock in to the DB platform. Which may or may not be a bad thing. But if you have a skilled SQL team and some resources to pay the DB licensing and support piper, this is a good direction to ensure app support longevity. For example, you can do the same thing on desktop or phone or web client or even hardware switches: say you have a DB stored_procedure to indicate that the process of manufacturing this part has reached stage X update_item_status( Item 11111, 'Manufacturing Stage X' ) (these is obviously really contrived but it is an example to make things clear) You can run this, if it is in the DB by: - hitting this button on the assembly line - scanning a barcode can do the same thing - having a user change a status in a UI - have a batch job run off a script with a list of parts to apply the status to And even better, if you want to update how the stored proc works, you update once in the DB and it is available to all 'interfaces' instead of having to update hardware switch code + user screen UI code + batch script code etc etc... I know that an argument can be made that you have an app server layer separate from the DB so this is not a 'real' problem but this approach just puts the app server IN the DB One closing thought: it is often easy to forget that, in most cases, the data is the product of software development and the tooling is just support. Only counterpoints I can think of here are games and interactive demo systems where the process ( of playing the game or using the software ) is the product. Positioning the database and front-end as being similar is a mistake if business goals are thought about from a "the data is the product" perspective. If the rules about how to handle, manage and interpret the data are a core component of that data product, then the argument that the right place for business logic is in the DB is made even stronger.
• eternalban 3y ago
  Derek is right. Tooling is the main reason not to put code in DBs. It’s a pain, that’s all. Tools are like ‘70s calls to say never forget me’. But logically it makes eminent sense. At the end of the day, writing code in the dB is not that different than writing components for an app server. There is also another reason. Designing effective schemas and bundled logic is not junior level work. Microservices, imo, all song and dance aside, really were trying to deconstruct the “monolith” of the schema: a schema that serves the requirements of today, is modular, and is possible to sanely extend in the future is not commodity work. So a practical reason not to do it is the required labor (expertise).
• Kinrany 3y ago
  Both are implementations of the same idea: not splitting the logic into two services deployed separately. Either store data directly on disk or in a general-purpose piece of infrastructure that needs no configuration.
• wintogreen74 3y ago
  I think a really important aspect is keeping the logic in your head (and others learning it) is helped by mirroring this single source of truth with your implementation.
• Gordonjcp 3y ago
  Okay, how about "Either put your domain logic in the database, or put it in the code, but don't randomly scatter your shit throughout the code and database"?