[ehutch79]

Yes, there definitely need to be escape hatches for situations where you need to write sql.

But that should be rare. If you're commonly bailing to raw sql, I'd say there's something wrong, probably a poor fit of the orm to the problem.

[ryanbrunner]

I'll use raw SQL (maybe not as an entire query, but something like a computed column) pretty often, for situations where I want to query things like "give me all foos with a count of bars related to them", or "give me a list of foos with the name of the latest related baz". Most ORMs would want to hydrate the graph of related objects to do that, or at least have multiple round trips to the DB server.

[ehutch79]

That doesn't sound like a good ORM. They should be lazy until you access the relevant data.

That said.

This is where the knowing what's going on behind the scenes matters.

[ryanbrunner]

Oh they would be lazy, it's just that expressing something like that efficiently (i.e. something like "SELECT foo.*, (SELECT count(1) FROM bar where foo_id = foo.id") is usually really hard to do. Most ORMs I've seen would N+1 on that with a naive approach, and even the "optimized" approach will want to fetch all bars vs. just the counts.