I used the stored procedures approach sometimes. The usual criticisms are
1. Almost nobody in the team knows SQL, stored procedures more so.
2. It's not possible to deploy and test stored procedures with the usual tooling. Actually I don't know what's the usual way to do it in projects that go with that approach.
I don't really understand the confusion here, you write a stored procedure, you document it, test it, peer review it, then stuff it into the database. You then write code that uses it. No sarcasm, but what's the problem?
Yes, what is the normal way to unit test stored procedures? Dev teams used to Node, Python, Ruby or whatever language have tools that work with ORMs to setup a test database, run tests and zero it. I did wrote some stored procedures in a Rails migration many years ago but that's not the place where one would normally write code. Furthermore there will be many migrations like that, almost one for every change to the stored procedure. The problem is in the frameworks, that are not designed for that. So, what's the recommended way to handle them?
Right, I'm starting to get the picture. I've never worked with an ORM as my SQL is pretty solid. I have to put my hands up here and say I don't know, sorry.
I think I can tell you the answer: you have a very slow system test that spins up a whole real database, sets up all the tables and stored procedures, and fires a load of requests through the system and inspects the responses and the final state of the system.
That works; it's just maybe thousands of times slower than unit tests, and much more work to create and maintain.
1. Almost nobody in the team knows SQL, stored procedures more so.
2. It's not possible to deploy and test stored procedures with the usual tooling. Actually I don't know what's the usual way to do it in projects that go with that approach.