ORMs are amazingly useful in the limited case that you're performing basic CRUD operations or simple joins.
In any sufficiently complex codebase you'll be doing more than this, however, and the work to support the ORM properly then outweighs the work of simply coding the raw SQL and preparing statements as appropriate.
People hugely exaggerate this IMO. If you're writing a lot of queries that can't be composed, with sufficient performance, in ActiveRecord, but can be in raw sql, then you've probably done something hair-brained else-where.
And for those special (and IMO pretty rare) occasions, you can drop down to arel or raw SQL anyway. Why throw away the consistency and readability of something like AR for edge cases, when you can just treat your edge case as an edge case with raw sql and still keep AR for your other 95% of queries.
I maintain a relatively unremarkable but bespoke online discussion forum, which has hundreds of queries, few of which could be composed by an ORM, let alone composed and run performantly. The median complexity query in my code base probably has two or three joins, two or three subqueries, and some kind of aggregation or window function.
The result is a typical page runs around two or three queries total—one query to authenticate the user and load everything about their profile and permissions, one to load the entirety of the data being output on that page, and occasionally one to update a statistic somewhere.
(The authentication query runs on every page because there's absolutely no persistence in the application layer. The authentication query goes three layers deep in subqueries and includes half a dozen joins. It hits perfect indexes when it runs and takes only a few msec round trip.)
> People hugely exaggerate this IMO.
In my experience, people who think an ORM can do most things are simply under-experienced with SQL and set theory.
> The median complexity query in my code base probably has two or three joins, two or three subqueries, and some kind of aggregation or window function.
Well, just from that description, Django ORM could do it. Can you post an example of a median query? I'm curious to see why it can't be ORM'ed.
This is a heavily redacted, completely renamed and summarised version of a typical page data query. It's less complicated that the top three most common queries that run to build the most common pages.
SELECT
b.field, b.field, b.field,
group_concat(concat(y.field, useful_thing)) as useful_things
FROM (
SELECT t.field, f.field, w.field,
(case when w.wid is null then 0 else 1 end) as has_watched,
exists(select id from posts p where p.tid = t.tid and p.uid = :uid) as has_posted
FROM (
SELECT tid
DENSE_RANK() OVER (PARTITION BY foo ORDER BY bar DESC) AS useful_pseudo_id
FROM editorial_things
WHERE thing = :thing
ORDER BY something
LIMIT number
) as a
INNER JOIN thread as t ON t.tid = a.tid AND t.last > Now()-INTERVAL 2 DAY
INNER JOIN forum as f ON f.fid = t.fid AND f.fid in (:security)
LEFT JOIN watched as w ON w.tid = t.tid AND w.uid = :uid
ORDER BY something
) as b
GROUP BY something;
The other thing is that being returned objects just adds complexity and handling. The language I use has a perfectly nice native, iterable data type for returned database records (kind of like an array of dictionaries) and I'd rather just use that instead of an object middleman to satisfy some kind of OOP completionist fantasy.
CFML (i.e. ColdFusion) using the Lucee engine on the JVM. Don't believe the popular scorn: it's as good a language as any other for web development. Perhaps not as innovative as newer languages, but it's densly packed with pragmatic conveniences and (for better or worse) doesn't impose any particular architectural style upon you.
CFML has a "query" datatype that represents the rows and columns returned from a database combined with useful metadata and really neat features like n-level iteration where values repeat. To the programmer it's like a dictionary that magically changes its values by passing it to an iterator. Without the iterator it works like a dictionary of the first row. Or if you treat it like an array you can manually read any column in any row directly.
I agree. Don’t know why you’ve been downvoted. Your experience directly mirrors mine.
What I did find interesting was the pushback I got from my team when I told them that I wanted to drop the Java ORM (JPA). One guy, who I respect, was super critical of the decision. He was telling me that SQL was the reason we had a performance problem, but when I investigated I was astounded by how fast SQL was, even over JDBC. It was clearly the ORM that was slowing us down.
In his defence, when I was able to show him literally 100x performance improvement after moving a service to PL/PGSQL and MyBatis (Java), he changed his mind.
In most applications you're going to be doing both things, I remember Rail's ORM was always presented this way: it's the far better option for simple object relationships and CRUD style systems but you can still write raw SQL as needed for complex stuff or things requiring performance optimizations.
There's plenty of ways to manage complexity in software. Two or three different databases in a single app doesn't kill them, nor will two different DB interfaces.
In any sufficiently complex codebase you'll be doing more than this, however, and the work to support the ORM properly then outweighs the work of simply coding the raw SQL and preparing statements as appropriate.
Raw SQL also tends to be much more performant.