3 ms·
I have seen many ORM enjoyers argue the point about “you can just use SQL!” but I have never once seen an ORM enjoyer allow it, much less do it themselves in an
by hatefulheart 4mo ago
I have seen many ORM enjoyers argue the point about “you can just use SQL!” but I have never once seen an ORM enjoyer allow it, much less do it themselves in an actual codebase. They will time and time again prefer you write 100 lines of Typescript/Python for what could be achieved with 15 lines of SQL.
- nfw2 4mo agoThe reason given to use raw SQL is for the performance not the perceived code clarity.
- hatefulheart 4mo agoI’m not sure why you thought I meant code clarity and not performance? It’s clear in all cases the correct SQL query will be more performant. Confused at what you’re evening trying to say here. Are you suggesting that 100 lines of application layer code is easier to understand than 15 lines of SQL?
- airstrike 4mo agoThe correct SQL query will be more performant than what? The correct ORM call will build the same correct SQL query. ORM is ultimately SQL
- hatefulheart 4mo agoSo there is no CPU cycles for the ORM itself? That’s free?
- airstrike 4mo agoIt's 2026. CPU goes brrr. It's absolutely trivial compared to the query execution time.
- sgarland 4mo agoProfile your code sometime; I assure you, with a properly indexed query, the actual query time is insignificant compared to everything else, unless your app is Rust, C, Nim, etc. The overwhelming majority of OLTP queries I see running on massive prod systems execute in < 1 msec. More time is spent in network RTT than execution, let alone the ORM parsing the result.
- nfw2 4mo ago1. Network transfer time depends on size of data payload and will basically always dwarf cpu operations unless something is seriously messed up 2. Query performance is dependent on the query and table size. They won't all be < 1msec. Not everything can be an indexed O(1) lookup 3. Generally speaking, network RTT and query performance is going to dwarf time for ORM to parse the result 4. A raw SQL driver ALSO needs to parse the result if you want to do anything with the data in the general purpose language
- nfw2 4mo ago1. Because you referred to lines of code as the way to suggest SQL is obvious better, not performance 2. No, my point was that talking about code clarity was a distraction because to talk about lines of code as a determinant of performance is clearly wrong. 3. Tangentially, yes, if some behavior takes 100 lines of general purpose code to express, I would rather read it in the general purpose language than in SQL even if the SQL was fewer lines. It's hard to imagine why this would ever be the case though.
- baq 4mo agoIf you never used a CTE, maybe… The reason to use SQL is to get what you need out of a database. Performance is orthogonal to that.
- nfw2 4mo agoObviously, this means using raw SQL instead of an ORM, as the article was discussing the trade-offs of the two and wasn't a 101 course on what SQL is
- jghn 4mo agoTo make matters worse, most of the time I've successfully argued a project to just use SQL instead of an ORM, what has happened is that people over time built a home rolled ORM in the development language. It's like people can't just let go.
- hparadiz 4mo agoThis is inevitably what happens every single time so just use an ORM and stop being stubborn.
- jghn 4mo agoThe problem is that "ORM" does a lot of heavy lifting as a term and can mean different things to different people. Like yes, obviously, one needs some sort of SQL -> data structure transition on the boundary (using "object" overfits to OOP!). But that can be extremely light weight. Let people write SQL, have a thin layer to pull the results back out into the appropriate data structures, and move on.
- hparadiz 4mo agoEvery good ORM lets you write SQL. Mine for example has a getByQuery and getByWhere as standard methods. An ORM isn't just writing queries for you it's also handling type casting from lang primitives to SQL and back. In 99% of crud rest apis there should be no need to write your own SQL though.
- pjmlp 4mo agoWorse, that code will be executed on the receiving end, and waste a bunch of network traffic.
- zadikian 4mo agoAnd then the 100 lines of JS/Py ends up being way slower than the manual SQL, plus the autogen'd SQL part of it is slow, plus you can't even get the SQL query to profile without running the actual thing with prints.
- hatefulheart 4mo agoYou got it in one, small world huh?
- 7bit 4mo agoGreat anecdote. Doesn't validate your claim
- hatefulheart 4mo agoLooks like I’m not the only one, check the thread.
- 7bit 4mo agoStill just anecdotes. Who cares about those
- hatefulheart 4mo agoYou’re on a forum where people share anecdotes, so presumably, you? Are you dumb or are you just pretending? I’m going to guess the former!
- smw 4mo agoEven the 'worst' of the ORMs (according to the people in these threads) makes this very easy: users = User.find_by_sql(<<~SQL) SELECT users.*, COUNT(posts.id) AS posts_count FROM users LEFT JOIN posts ON posts.user_id = users.id GROUP BY users.id HAVING COUNT(posts.id) > 10 SQL users.first.posts_count # => 17