[dcosson]

This is a pretty condescending take, and doesn't really seem rooted in anything substantial.

The core of your argument revolves around the inevitability of needing to drop down to raw sql for performance -- care to elaborate on that? It seems overblown, I've worked on some apps at decent scale and optimized quite a few queries and I haven't encountered this pattern of needing to throw out the ORM altogether in more and more places.

For any simple to moderately complex queries, the ORM is generating the exact SQL you would write by hand... There are a few pitfalls -- by default you may be fetching more fields than you absolutely need, but this is fairly cheap as long as your rows aren't super wide, and any good ORM offers an easy way to pluck only specific fields that you can use in hot paths. It's often easy to end up with N+1 queries in an ORM, but that's also a well-known access pattern that you learn to avoid with a little bit of experience and doesn't require raw sql. This latter is one area that I think a lot of ORMs could really improve upon, in terms of making it harder to do the wrong thing for inexperienced folks. But it's hardly a reason to throw out the whole tool, you can write n+1 queries in raw sql too.

I would agree that very complex queries can be more tedious to express in an ORM syntax. A good rule of thumb for when it's worth dropping down to raw SQL from the ORM, is if the shape of the query results don't match an object shape anyway -- i.e. for complex analytics type queries that are doing aggregations. But again I don't see much of an argument for throwing out the whole ORM just because you want to write a small percentage of very complex queries in raw sql. Use the best tool for the job. I don't think there's really al performance argument for these types of queries either, a really complex query can still be just as slow written in raw SQL. The solution to scaling these kinds of queries in a hot path tends to be denormalizing or precomputing or caching parts of the data.

[fjp]

Agreed - For example in SQLAlchemy, you have:

1) Super high level - my_record_object.filter(whatever)

2) Common query api: my_table_object.select.where(whatever).limit(whatever) etc etc

3) Straight up SQL execution: db_engine_object.connection.execute(Sql query as string)

There are tools to safely interpolate arguments into SQL query templates for use in #3 too. What else could you want in the vast majority of applications?