At the big tech firm I work at, there's a best practice where any database (whether that's a traditional RDBMS or a NoSQL client) is abstracted away by a microservice with a defined API, and every other application that wants to get that data needs to interact with the microservice. That way, the database schema can change without it affecting multiple applications. There's still the traditional mismatch between ORM and database, but doesn't feel as painful because only a single application is using that data, and that application can have special knowledge of the persistence layer underneath (meaning, use database hints if necessary, defer to raw SQL, etc.)
We have decades of research into filtering, joining, and aggregating across a complex set of tables and views. With microservices you have to roll your own query planning and stream all the intermediate results on the wire even when you're throwing away most of them.
The thing is, letting the system handle it isn't good enough. We have decades of research into this stuff, so 90% of the time the database gets it right - but the interface for explicitly taking control when the DB gets it wrong is black magic at best. The value of having an exposed representation of filtering/joining/aggregation in a general-purpose programming language where you can interact with it is big enough to outweigh that of a database that can do it for you most of the time.
Unfortunately, SQL servers aren't generally scalable. Offloading joins and aggregation onto (inexpensive) app servers can increase over all system performance, despite lack of advanced query planning.
Bollocks. SQL scales fine unless you are stupid in the way you use it. All the crap performance I have seen in the last couple of years have been from doing crap like you suggest - doing joins and aggregation at the application level. Our front page currently makes over 1000 database calls because of this sort of nonsense.
That kind of sounds like a microservices anti-pattern in disguise. Its thought by many to be bad practice for microservices to share a common database. But that's pretty much what you're doing, but just with an API instead of jdbc for the access.
