I haven't seen an ORM that handles analytical queries well. I'd rather write raw SQL than use SQLAlchemy for complex queries with multiple joins, aggregations, and window functions.
However I have had great luck with using an ORM to load up the database and data, and then having a unit test that calls the function which does raw SQL in the middle. And now the raw database tests are integrated with the unit tests for the rest of the environment in a way that keeps them synchronized with the application code that also interacts with the same database.
And, of course, the limits of ORMs do not change the fact that they have use cases.
There’s ORMs and there’s ORMs - at one end you have the (reprehensible) Active Record anti-pattern, at the other end you have EF Core extended with one’s own build-time type generation - they’re both “ORMs” to everyone involved, but they’re totally incomparable.
…not to say they that EF Core doesn’t have flaws (it does, and they’re legion) but the ORMs of today are nothing like the ORMs of the 1990s… or even like 2010’s NHibernate.
I agree, but I still haven't seen an ORM that handles analytical queries well. Which means that, no matter what the other merits of the ORMs may be, there are important use cases where raw SQL is the only realistic option. Which brings us back to how to test that code.
The problem with writing raw SQL (which I do personally prefer myself, too) is now you need to generate types and/or mappings for each distinct query’s resultset schema - doing that by-hand is tedious and error-prone (or use untyped dict objects for every row, ew) - so what you really need is a project build-step that finds every query in your project and runs it against a prototype database instance in order to get schema result typing info, then generates the strong-types/mappings code for you before everything else gets compiled…
…and it works - but now you have possibly thousands of classes/structs that are all-so-similar but also subtly different - namely disjoint members (so they can’t exist in an inheritance hierarchy, e.g. NewUser won’t have a UserId value, result-types would be immutable, unless they need to be mutable, etc…). It’s all such a huge pain. In a C# project of mine that does something like this, it means that every business-entity typically has at least ~5 actual class/struct/interface types associated with it: e.g. NewUser, IReadOnlyUser, IWritableUserValues, struct UserKey, MutableUser, UpdateUserValues, etc.
I like the approach of just using reflection to do the mapping and throw an error when things don't quite match up like with https://stackoverflow.com/a/21956222/7608007. Combined with something like record types or Lombok, it's not that much effort to create a bean class for each result mapping you care about.
The problem with using something like runtime reflection is you lose compile-time type-safety (i.e.: the guarantees that a particular resultset contains a particular column, its type and nullability is correct, etc) .
With not complex data for sure, I think SQL testing is so much easier once you start having hierarchies and many to many relationships start piling up.
