I'm trying for the life of me to think of queries I could make that are non-CRUD. Maybe I am just stuck in my current tools to think flexibly about it.
I presided of over a Migration from DB2 to Oracle. All of the operations were CRUD.
However the DB2 database had materialized views that were crucial to the business.
Porting those over was non-trivial. Oracle returned a different value for null fields than DB2 (empty strings for null strings vs actual null in one case) did so various portions of the code would fail seemingly randomly.
Some of the constraints didn't map quite as cleanly as the others.
And we haven't even started on the performance tuning and how your schema affects them.
Sure you may be able to port all or most of your schema and queries over to another database but you'll have a lot of work to do after that verifying that nothing is broken and your performance is still acceptable.
SQL is not even close to a guarantee of portability.
Using CTEs, including writable CTEs is not something I'd want to do in an ORM. Also, whenever I find myself having to have more than a few expression fields, I find ORMS clunky.
Trying to load more than one record from a a single row is supported in some, but not all ORMs as well.
Aggregate queries, especially more advanced ones than just min, max, sum, avg, stddev, &c, are not always supported by ORMs either, or you need to use expressions or extend parts of the ORM. It's just a mess sometimes and I'd rather query and then load.
I'm trying for the life of me to think of queries I could make that are non-CRUD. Maybe I am just stuck in my current tools to think flexibly about it.