We do manage the SQL code (view definitions, mviews, and the remaining procstocs) right next to the application code with Flyway [1]. We also have SQL-ORM integration tests, spinning a database container with testcontainers [2].
We've had some issues with business rule duplication (updating the ORM and forgetting to do the same in SQL), but so far I'd say it's successful.
This being said, I'm on the SQL boat; and I remain convinced that the CRUD could be done comfortably with an SQL query builder such as jOOQ [3]; and that it would help solving the business rule duplication issue. But hey it's working right now and everybody is happy about it, so why change it?
• Your data makes sense as objects;
• Your data is small enough to fit in memory;
• Most of your operations are CRUD.
If you can answer all three with yes, then use an ORM or some other kind of SQL abstraction layer. Otherwise don't.
You don't even need to go that complex. Even moderately complex JOINs start to look better in SQL than in ORMs.
If you have an employees database, an inventory database, and a clients database, an ORM is perfect. If you have a fixed hierarchy, ORM works great too. It maps objects to the database. That's 90% of web apps.
If you're building e.g. an online CAD system with a complex data model for storing hierarchical layers of objects with complex relations, and where you need to perform complex operations on that data for e.g. simulations and optimizations, SQL will actually handle that just fine. You'll just be doing SQL beyond what fits into an ORM. At that point, if you use an ORM, you'll be doing complex contortions. Think back to your data structures class. Then to the grad-level data structures class. Most of those structures, SQL will handle fine, but ORMs won't.
SQL has also turned out to be surprisingly resilient to different programming paradigms. SQL came out just around a half-century ago, before OO was common, and did fine with OO, functional, structured, and a whole range of other paradigms which have moved into and out of vogue over that time. ORMs, as the name implies, are specific to OO.
A few posts up, the poster is correct that there are design patterns around ORMs which work well, and design patterns around SQL which work well. You want to pick one and stick to it. The mess comes in when you mix layers of abstractions. One or two SQL procedures might not kill you, but when you have big chunks of code using ORM and big chunks NOT using ORM, you'll crash-and-burn.
Footnote: "Complex domains" is also about the database layer, and the type of complexity. Right now, I'm working on a very algorithmically complex system, but that complexity isn't in the data layer. Most of the data engineering is about moving GB of data around in realtime. All the database needs to handle are simple things like auth/auth. That's an ideal use-case for an ORM. I've also built systems with ORMs where the database layer was complex, but complex in ways which aligned well with ORMs. So this shouldn't be read as "dumb programmers who can't handle functional use ORMS."
Footnote to posterity: Should someone stumble upon this in a web search: If you grew up in OO and Java, and never ventured beyond, none of the above will make sense to you. You only learned the programming paradigm ORMs were designed for.
They're great for storing complicated queries, but the actual buiness logic? No.
Have some stored procs for updating and querying data, then a lightweight abstraction around them in the application.
[1] "business logic or domain logic is the part of the program that encodes the real-world business rules that determine how data can be created, displayed, stored, and changed.", https://en.wikipedia.org/wiki/Business_logic
Business logic isn't pulling data from database, how to map an object to a table, or the specific SQL query to grab some data. Those are technical details. SQL should just be pulling, saving and querying from the store without much maniplation. Not changing the data.
It's the rules that a business stakeholder might be interested in creating.
When you start putting the tax calculation rules into the stored proc, thats where the problems begin.
If you create a CTE to pull the information required to perform the calulation then return that info without doing the calculation thats fine.
If you are not allowed to do this kind of things, then what is left? Do you interact with your database as if it were just a bunch of VSAM files?
So in a lot of ways, yes people use databases like they're just persistent memory with indexes.
Just about anything you can do with stored procedures, you can do within an SQL query block in your favourite language. And when all your queries are fully parameterised, your database server can even use the same execution plan cache as "real" stored procedures.
I find both have valid points and there isn't really a compromise. Whichever approach is taken you end up with half the team feeling not listened to and disenfranchised.
I have found few things to be more divisive than the ORM vs No ORM debate and I am not sure what the answer is.