Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What ORMs have taught me: just learn SQL (2014)

wozniak.ca

Post reply on HN
Print view View original

71–80 of 354 posts

Re: What ORMs have taught me: just learn SQL (2014)

#71

Post by jghn »

hparadiz wrote: post #43
jghn wrote: post #37

Earlier quoted context omitted.

To 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.

This is inevitably what happens every single time so just use an ORM and stop being stubborn.

The 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.

Re: What ORMs have taught me: just learn SQL (2014)

#72

Post by dools »

I always disliked ActiveRecord, but I figured ORMs don't have to be ActiveRecord. I created this library 14(!) years ago not too long before this article was written https://github.com/iaindooley/PluSQL

The idea is that you like SQL, but it gets repetitive writing joins and accessor code. I had always hoped it would catch on as a pattern: no boilerplate, automatic mapping to objects in your code of any query (whether generated by the ORM or passed in as a raw query) and easy to override/dynamically build bits of the query as you pass the object around.

Re: What ORMs have taught me: just learn SQL (2014)

#73

Post by prmph »

I'm not sure why people have not hit on the following hybrid architecture that works so well for me.

I make use of table-valued db functions (IMO the most underrated feature of relational DBs) to define virtual relations/tables. I implement a set of CRUD db functions per entity. Then, on the app side, I define (or generate) DTO types representing these virtual relations. Finally, I use a custom ORM I wrote myself, which defines a general and consistent storage API, to talk to the db functions, using the DTO types.

The advantages of this approach are numerous, some include:

- I have full control of the SQL that goes into constructing the virtual table, I can leverage all the goodness of SQL here. I can even define multiple virtual relations per physical table, or read-only relations, etc, all by implementing the appropriate sets of CRUD db functions

- On the ORM side, I have all the goodness of static typing, a consistent API for all CRUD methods, a full fluent query DSL, etc

- Since, unlike tables or views, db functions can be passed arguments, i am able to layey all kinds of goodness on top of the basic CRUD actions, like audit info passing, custom upsert strategies, some level of record-based authorization, etc

But this architecture does require you to know and write SQL. IMO the value of ORMs do not lie in avoiding SQL; it lies in the capability to express consistent SQL at a higher level of abstraction, but you still need to understand your SQL.

Re: What ORMs have taught me: just learn SQL (2014)

#74

Post by hparadiz »

dools wrote: post #72

I always disliked ActiveRecord, but I figured ORMs don't have to be ActiveRecord. I created this library 14(!) years ago not too long before this article was written https://github.com/iaindooley/PluSQL The idea is that you like SQL, but it gets repetitive writing joins and accessor code. I had always hoped it would catch on as a pattern: no boilerplate, automatic mapping to objects in your code of any query (whether…

That's a query writer. Not an ORM.

Re: What ORMs have taught me: just learn SQL (2014)

#75

Post by jcgl »

marcosdumay wrote: post #52
stephen wrote: post #28

Earlier quoted context omitted.

You'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".

AKA making the easy parts easier while making the difficult parts harder.

No? ORMs don’t preclude writing raw SQL, so it’s just making the easy parts easier while leaving the difficult parts the same.

Re: What ORMs have taught me: just learn SQL (2014)

#76

Post by 7bit »

hatefulheart wrote: post #26
stephen wrote: post #15

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 tabl…

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.

Great anecdote. Doesn't validate your claim

Re: What ORMs have taught me: just learn SQL (2014)

#77

Post by hatefulheart »

zadikian wrote: post #61
hatefulheart wrote: post #26

Earlier quoted context omitted.

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.

And 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.

You got it in one, small world huh?

Re: What ORMs have taught me: just learn SQL (2014)

#78

Post by jghn »

bearjaws wrote: post #65
stephen wrote: post #15

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 tabl…

> 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 gre…

The 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

Re: What ORMs have taught me: just learn SQL (2014)

#80

Post by hatefulheart »

7bit wrote: post #76
hatefulheart wrote: post #26

Earlier quoted context omitted.

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.

Great anecdote. Doesn't validate your claim

Looks like I’m not the only one, check the thread.
Post reply on HN