ORMs are not evil, but I like doing my queries in sql (and using pg functions). In the past I’ve seen the separate queries that an ORM would send to the db to build the data objects I wanted at the app level and I found it more comfortable to pound on queries in sql, then make a view so the app only needs to do simple selects. It was easier for me to maintain. I’m not saying it was the ORMs fault, but I liked having the ability to keep tables simple, make joined structures in views, then dump json out from there. With CTEs it is easy to nest arrays within parents etc. Then with PG we can materialize the view for data that doesn’t change frequently.
As horrible as that all sounds, hasura is the first layer that makes me want to try app directed queries, schema migrations etc. I got tempted by django way back but missed that train.
I'm not sure I follow. Do you mean you use database views that output json? And you prefer basing those views on select...with rather than with explicit joins? Because if they're all views on the postgres side, I'm not sure how orms affect the choice of cte vs joins?
Or are there orms that work more nimbly with ctes than with joins (or views)?
I'm currently fighting with some legacy ruby on rails apps that interface with various more-or-less legacy databases - and I've actually been somewhat pleasantly surprised by how concise some joins might be hammered out even under rails 4.