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.
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?
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.
But maybe that's my enterprisey bias.
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.
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.
- 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 challengeNot because of slow queries, but just the cost of executing the stored procs themselves.
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.
I expect at some point we'll have a similar initiative around cloud providers.
This is pretty good since one can generally fast unit test DB code with an embedded database.
The Oracle version had a lot of logic written as SP. I migrated them to MySQL SP.
So the conclusion is: SP don't make it impossible to migrate from one database to another, and second: yes database migrations do happen.
Also: all database migrations I have known involve Oracle in one way to another. Oracle salespeople are way too nosy.
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.