To avoid obscure ORM syntax and resulting garbage queries, as well as cumbersome inline SQL blocks, just implement your complex queries as stored procedures / views etc.
Additionally, you're making sweeping generalizations based on a limited perspective. Loading up the application layer is the right choice when your data will only ever be consumed by that application, and multiple versions need to coexist. When you're talking about data that cuts across a business, and where a single application version lives at a time, loading up the application layer is infeasible and unnecessary.
So now we have something like
CreateCustomer_1
CreateCustomer_2
CreateCustomer_3
And different apps are using different versions. If you have to make s change. You have to make a change to every version because you don’t know which app is dependent on which version.
What changed between the stored procedures and what code commit did they go with? Who changed it? (Usually this is solved very badly by having headers with comments that go back years). I can do a “git blame” on code, diffs, etc.
Loading up the application layer is the right choice when your data will only ever be consumed by that application,
Ideally, it should be one service per domain that is responsible for one set of data. That can be either a microservice or a module in a mono repo.
Of course reporting would be separate and would cross cut multiple domains and would usually have intimate knowledge of the tables anyway.
It’s also much easier/cheaper/faster to scale app servers than DB servers.
If you're going back and changing old versions of a stored procedure, you're doing it wrong. The only case where this would be true is a breaking schema change, and then application layer code would break too, so the point is moot.
Siloing your data makes it less useful (or rather, it increases the cost of using it) and less discoverable. Additionally, if you have some sort of security model in place on your data that becomes exponentially harder to manage in the silo'd case.
As for scaling - read scaling databases is REALLY easy. I admit that write scaling is more challenging, but stored procedures are read-dominant in general. For specialized or very high throughput cases, custom application layer logic can be made much computationally cheaper, but this requires more (and more expensive) engineers, so that must be factored into any potential savings.
Siloing and thick application layers are great for some use cases. Data aggregation and thick database layers are great for others. Insistence on one model in all cases is only going to limit you as an engineer.
And what happens when I need to rollback? I have to rollback the stored procedure code separately. What about regression testing?
If you're going back and changing old versions of a stored procedure, you're doing it wrong. The only case where this would be true is a breaking schema change, and then application layer code would break too, so the point is moot.
It would be one change in one module (in process) or one microservice (out of process).
Siloing your data makes it less useful (or rather, it increases the cost of using it) and less discoverable. Additionally, if you have some sort of security model in place on your data that becomes exponentially harder to manage in the silo'd case.
For reporting/analytics that would be a separate longer running process anyway and wouldn’t be a part of your OLTP system anyway and you probably wouldn’t be using stored procs. You would be using some type of reporting system.
As for scaling - read scaling databases is REALLY easy. I admit that write scaling is more challenging, but stored procedures are read-dominant in general. For specialized or very high throughput cases, custom application layer logic can be made much computationally cheaper, but this requires more (and more expensive) engineers, so that must be factored into any potential savings.
Yes, throwing up read replicas is just a matter of throwing money at the problem - and I’m not morally opposed to using money as an optimization technique when necessary. But you can still get more granular scalability with app servers.
For specialized or very high throughput cases, custom application layer logic can be made much computationally cheaper, but this requires more (and more expensive) engineers, so that must be factored into any potential savings.
And that’s where I come in :)