Hacker News Reader

Comment by mtone | Hacker News Reader

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.

View on HN

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

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.

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.

> 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.

> 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.

Reply on news.ycombinator.com