ORMs are a massive benefit to productivity and security for about 80% of your database access. The problem is that people expect to use them for 100% of their database access. For that last 20%, they just get in the way and cause more problems than they solve.
> For that last 20%, they just get in the way and cause more problems than they solve.
If I had a nickel for every project I worked on that started using an ORM but eventually was forced to write actual SQL to cover those use cases the ORM just couldn't I'd be rich. In fact it's the number 1 reason I don't even bother with ORMs anymore.
If I abstract my data access to a set of APIs then all I have to do is reimplement the SQL or whatever behind each API when I need to move to a new storage system which usually ends up not being very difficult compared to the headaches of trying to get an ORM to cover that last 20%.
While I do write SQL during exploration, for the purposes of production systems I've never had a query that I haven't been able to represent using SQLAlchemy. The reason for this is that in addition to being an ORM, it exposes an object-oriented general SQL abstraction layer.
> For that last 20%, they just get in the way and cause more problems than they solve.
This is why my preferred measure of an ORM is how effectively one can write your own SQL with it, and what you get out from your database call when you do so (do you still get "real" business objects or just key-value pairs?)
That said, I've found you can trick even a rather recalcitrant framework into making sophisticated queries by setting up a database view and tell your ORM that the view is just another table...
