I'm not a fan of stored procedures because 1) It's more maintainable to keep all the logic unified in the one language and system with which developers will be more familiar with 2) The programming tools are normally much better for app code 3) You can write unit tests for app code (does anybody write unit tests for sql?) 4) you can usually avoid transferring too much unnecessary data or doing too many unnecessary network requests, remember to avoid premature performance optimization
On the other hand, one of the highly specialized stored procedure I wrote in PostgreSQL have being successfully and flawlessly re-used/tested on multiple out-of shelf clients app (BI related all developed in different languages) simply because they all support SQL.
I agree this only apply for data-model related functions. But this has proven really low maintenance, and improvements of this "API" are automatically available in all clients.
I'd bet that accessing this stored procedure through an ORM raw SQL query would provide more benefit than rewriting it from scratch in ORM's language.
PS: Also yeah people do write unit test for SQL (never enough but that's another story).
https://pgtap.org/
1) One language and one system: SQL on a database that will last much more than the applications
2) SQL has a limited scope, and so do SQL tools. You need advanced tools if you have "app code": it's a necessary evil.
3) At worst, you can write unit tests for app code that calls SQL queries and stored procedures.
4) "Usually" isn't enough, moving logic to the client requires moving data to the client.
