If your front-end is written in React and your business logic is written in SQL, is it really fair to call what's left in the middle tier a "core app language"? If you're writing a SaaS application today, you're more likely to want to rewrite your Java middle tier in Go than replacing your DBMS.
Not everyone is making a web app, or even something amenable to using React native, and even if they are, there's no guarantee that their middle tier isn't also in JavaScript.
That said, I wasn't making a case about replacing your DBMS. I specifically avoided that because yes, most people stay with what they know and used, and even if they switch, they switch for a different project, not within the same project. There are some cases where multiple DBMS back-end support is useful, but I think that's a fairly small subset (software aimed towards enterprises which wants to ease into your existing system and note add new requirements, and open source software meant to use one of the many DBMS back-ends you might have).
My actual point is more along the lines of:
- Most DBMS hosted languages I've seen are pretty shitty in comparison to what you're already using.
- The tooling for it is likely much worse or possibly non-existent.
- You are probably less familiar with it and likely to fall into the pitfalls of the language. All languages have them, shitty languages have more. See first point.
- If you accept those points and the degree to which you accept them should definitely play a role in deciding to use stored procedure you've written in the language your DBMS provides.
- I think trade offs are actually similar to what you would see writing chunks of your program in bash and calling out to that bash script. People can write well designed and safe bash programs. It's not easy, and there are a lot of pitfalls, and you can do it in the main language you're writing probably. Thus the reasons against calling out to bash for chunks of core are likely similar to the reasons against calling a stored procedure.
You make some good points. I personally have experience with business web applications, i.e. large complex data models with rather simple updates and report-like queries. These types of queries combined with a well normalized data model map well to set-based SQL. Of course, it's a different story if you're writing technical applications or games that are more about crunching numbers than querying and updating data.
To me, the shitty procedural languages you mention are just for gluing queries together. The important stuff happens in SQL and the simplicity of keeping it all in the database is worth it.
