For established software where performance matters, hand-writing the SQL is reasonable.
Hand-writing SQL for, say, a faceted filtering UI is a tedious job that takes most of a day in even fairly simple cases, or about 20 minutes with a decent ORM.
ActiveRecord (and related libraries like ActiveAdmin) are _amazing_ for rapid prototyping - eg if you don't even know whether you're going to end up keeping the faceted search.
> For established software where performance matters, hand-writing the SQL is reasonable.
These things aren’t mutually exclusive though. Every ORM I know gives you an escape hatch to write whatever sql you want. ORMs are great for 90% of things and as a reviewer I don’t need to scrutinize their queries too much. It’s much easier to for me to review an ORM builder query because I know it’s going to do the correct joins on the correct columns. For example in the ORM I use id rather see:
query()
.where()
.eq(“parent”, parent);
Instead of:
“select * from table join parent on parent.id = table.parent_id where parent.id = :parent”
Since you’re not returning anything from `parent`, it makes much more sense to use a semijoin, which is something ORMs usually bury in an obscure section of docs, if they support them at all.
SELECT * FROM `table` t
WHERE EXISTS (
SELECT 1 FROM parent p
WHERE p.id = :parent)
Or, you know, just eliminate the other table entirely (which the optimizer may well do) since p.id = t.parent_id = :parent
You’re completely missing the point while also completely making my point.
The ORM is going to do the correct thing here, while the SQL I quickly typed out will work, but does the inefficient thing and requires more manual review and back and forth in discussions.
I disagree. The point you're making is predicated on not understanding SQL. If you know an ORM well, and don't understand SQL, then of course it will be easier to review. I would however argue that if you don't understand SQL, then you can never truly understand an ORM, in that you can't know what the DB is capable of doing. You'll probably get lucky for things like `WHERE foo IN (...) --> WHERE EXISTS` translations that the DB's optimizer does for you, but you also probably won't even know that's happening, since in this scenario you don't understand SQL.
ORMs typically do an OK job at producing OK queries, in that they're unlikely to be the worst possible option, but are unlikely to be optimal. This is largely driven by schema decisions, which, if you don't understand SQL, are unlikely be optimal. The clunkiest, least-performant queries I've ever dealt with were always rooted in having a poorly-designed schema.
Hand-writing SQL for, say, a faceted filtering UI is a tedious job that takes most of a day in even fairly simple cases, or about 20 minutes with a decent ORM.
ActiveRecord (and related libraries like ActiveAdmin) are _amazing_ for rapid prototyping - eg if you don't even know whether you're going to end up keeping the faceted search.