7 ms·
I'm admittedly an ORM apologist [1], but a few of his points articulated as "deal breakers" aren't that bad imo: - "the pernicious use of foreign keys [...] li
by stephen 2mo ago
I'm admittedly an ORM apologist [1], but a few of his points articulated as "deal breakers" aren't that bad imo:
- "the pernicious use of foreign keys [...] links between classes are [...] foreign keys" ==> that just sounds like schema normalization, which is usually a good thing?
- "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!
Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.
- "dual schema dangers" ==> he's exactly right that database should own the schema definition, but then just codegen the entities from the db schema? That's your singular source of truth, no drift. You can do this with Hibernate, ActiveRecord, Joist, many ORMs.
- "Identities" ==> ironically I think ORMs (that use the unit of work pattern) actually have net-better DX here b/c you can hook up a graph of entities with just references.
I.e. hook up a book to its author w/o knowing their ids yet, which explicitly avoids the annoyance he mentions of doing a partial commit/going to the db to figure out "what value should I INSERT into in the book.author_id column?" (but my author is new) in the middle of your business logic that just wants to "create books".
- transactions ==> agreed that "transactions via annotations" ala JPA/Hibernate are terrible, but afaiu all "internet scale" apps these days do reads outside of transactions, and just use op-locking during the singular flush/commit step to the db.
Disclaimer I am sure I won't change anyone's minds :-)
Edit: in the HN comments, we're debating "the best way to generate SQL", which is fine, but imo it overlooks the biggest value for ORMs: enforcing business invariants.
I.e. yes a simple INSERT is trivial is write, "why have the ORM to that!", but are you going to enforce the same business logic in the 10 places you do `INSERT authors` in your codebase? And if the answer is "I write an single `insertAuthor` abstraction to enforce this" then you're half-way to writing an adhoc half-specified, bug-riddled version of what a reactive ORM like Joist will do for you. [2] :-)
[1] https://joist-orm.io/ https://joist-orm.io/
[2] https://joist-orm.io/modeling/why-entities/ https://joist-orm.io/modeling/why-entities/
- swasheck 2mo ago> Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction my experience is the exact opposite. People who love and advocate the merits of ORM insist that everything be executed through ORM because it introduces too much complexity for them to blend handwritten SQL with the ORM generated queries
- stephen 2mo agoFair point, both "pro ORM" and "anti ORM" camps are prone to extreme stances. I definitely don't agree with the "all queries must be executed through the ORM", and think that dogmatic stance has done a lot of damage to the ORM brand. :-/
- HelloNurse 2mo agoThey don't consider the ORM the second class citizen it actually is: an optional simplified alternative to normal queries, that can be used for the easy cases.
- hparadiz 2mo agoI've written/worked on several ORMs from scratch. ORMs are the industry standard. When I see posts like this I simply can't take them seriously. All they are saying is "I won't be a team player" and "I don't actually understand the subject matter". The reality is at a certain scale there's an entire orm team that optimizes everything. But even when there's no team involved there's no way you can write anything more optimized because I'm already at the computational limit of how far something can be optimized. There's no (good) ORM that doesn't let you simply put your own query in.
- hatefulheart 2mo agoWhat optimizations are you making here when at the end of the day performance is dictated by the schema, the query planner and the network?
- bot403 2mo agoI read it as "I've optimized the orm to be minimal overhead over raw sql a lot of the time".
- hparadiz 2mo agoI've actually benchmarked the overhead for my ORM against every major PHP orm that exists. https://the-php-bench.technex.us/runs/1 https://the-php-bench.technex.us/runs/1 But the speed is irrelevant as long as it's good enough. Notice Laravel's Eloquent at the bottom of the list yet thousands of projects are being built with it regularly.
- bluefirebrand 2mo ago> Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction. I've always heard a major selling point of ORMs is "You don't have to write the actual SQL anymore" Because of that, I tend to not trust people who use ORMs to even know how to write queries by hand in the first place
- stephen 2mo agoYou're right, that has been another "pro ORM" pitch that has gone awry and, taken to the extreme, is wrong imo. My nuanced articulation is "you don't have to write the _boilerplate_ SQL for the 90% of just-do-some-CRUD endpoints in your enterprise SaaS application, but you 100% need to 'know SQL' for the last 5-10% of ~reporting/analytics queries that the ORM is going to mess up".
- bluefirebrand 2mo agoPersonally I find the 90% boilerplate SQL is easy enough to write that injecting an ORM into the process doesn't make much sense But that's just me
- marcosdumay 2mo agoAKA making the easy parts easier while making the difficult parts harder.
- airstrike 2mo agoThe difficult parts are just literally a raw SQL string so how is that any harder?
- marcosdumay 2mo agoThat you somehow have to adapt the results into the same format the ORM uses. And has to adapt the parameters into taking data from the ORM. Or has to split your entire functionality from the ORM so you can actually access the database directly without one part of your code interfering with the others.
- hatefulheart 2mo agoI 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 2mo agoThe reason given to use raw SQL is for the performance not the perceived code clarity.
- hatefulheart 2mo 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 2mo 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 2mo agoSo there is no CPU cycles for the ORM itself? That’s free?
- marcosdumay 2mo ago> the huge majority of ORM-driven queries are "select * from table where id in ..." From my experience, you are mistaken on that. Those queries mostly come with some joins, either necessary or not to represent the object, and that often could be avoided if the data wasn't mapped into some standard object.
- hn_throwaway_99 2mo ago> "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed! This is exactly why I hate ORMs. As I always put it "ORMs make the easy stuff slightly easier, and they make the harder stuff way harder". If you're just using an OEM for the "select * from table where ID in ...", then you're saving practically nothing by using an ORM - just learn to write SQL, because as you put it, you're going to have to use it anyway for places where it falls over. There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs. In practice I've seen people try to use the ORM features first for places that need complicated SQL (which is a reasonable assumption), only to waste a boatload of time before concluding the ORM makes stuff harder.
- maxwellg 2mo ago> There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs. Query builders like these are my personal favorite from a productivity perspective! The point of a query builder is to dynamically build SQL statements that have many subtle variations (do we want to filter by EmailID or PhoneID here? What about a subquery? Did the caller want all results, or just results where $field=X?). They're basically one level above string templating for SQL generation, and often have niceties around ser/de and transaction management as you mentioned. Because they are primarily about query generation, it feels _very_ natural to pop off the hood and write raw queries directly when necessary. You can usually use the transaction management and ser/de parts with raw queries, too. My personal favorite in this field is knex.js.
- hn_throwaway_99 2mo agoKnex has its own set of problems. Again, SQL is a very powerful, well-known language and there are simpler tools that make it possible to break up and reuse queries. Years ago I was working on a project that used knex, then I serendipitously discovered slonik through this blog post, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41... (slonik has subsequently had lots of development since then). I decided to rewrite the entire persistence layer from knex to slonik over a long weekend and I'm so happy I did. I liked slonik so much that it was the only time I personally contributed to a programmer through GitHub Sponsors.
- bearjaws 2mo ago> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed! The issue is, your lowest value queries are always this type, then you get the 10-20 in any code base that are 100x more complex, and they are the ones your end users care about the most. You end up with a 80/20 principal in the wrong way, it's great at producing queries that represent 20% of the value of your app, and awful for the 80% that define the core value of it.
- jghn 2mo agoThe second issue is, if these queries are just "select * from table where id in ...", WTF bother with a library to abstract that away in the first place? It's trivially easy to handle this as SQL
- deleted 2mo ago[deleted]
- DanielHB 2mo agoThe main problem of mixing sql and orm together is that most orms don't provide a way to do raw queries in a type safe manner that plays well with non-raw-sql queries.