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

151–160 of 354 posts

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

#151

Post by hn_throwaway_99 »

maxwellg wrote: post #84
hn_throwaway_99 wrote: post #48

Earlier quoted context omitted.

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

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

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

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

#152

Post by vova_hn2 »

I don't like the title, it implies that the only reason for using an ORM is not knowing SQL, which is obviously not the case.

Every time I tried to do a project without an ORM, using only raw SQL, I inevitably ran into:

- serialization/deserialization boilerplate. Like, having to manually map values returned by the DB library to object (or named tuple, or structure) properties

- poor code reuse, having multiple very similar queries that have just one small difference

- extra pain in changing DB schema. Adding a field requires to go and manually edit many queries

Anti-ORM crowd never gives a good answer to these issues.

Instead, they push strawman attacks like "oh, you only use ORM, because you can't write raw SQL". I can absolutely assure you that this is not the case. Every time I use an ORM (SQLAlchemy mostly, the one mentioned in the article) I am 100% sure what SQL do I want it to produce and what SQL will a particular ORM invocation produce.

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

#153

Post by toast0 »

hparadiz wrote: post #141
toast0 wrote: post #130

Earlier quoted context omitted.

> Someone that uses an ORM knows when they shouldn't use them That's not been my experience. But admittedly, I've usually been brought in when the slow query is killing the database. Then I look at the query that nobody with any subject matter knowledge would have written, come up with an alternate query that will give either the same result or something close enough. Sometimes I have to then dig in and figure out ho…

People focus on the query writing aspect of ORMs too much. That's not that primary reason you use an ORM. It's primary purpose is to hydrate objects in the runtime. If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack. I can unit test that handling once across the entire code base. Very few ORMs are aware of how the data is ind…

> I expect someone who uses an ORM to understand SQL well.

From experience, I don't. ORMs are usually sold as 'learn this instead of learning SQL'. For many, the ORM creates the tables, alters the tables, and queries the tables; they don't see SQL and they don't know SQL. When that works, it works, but when it falls apart, they have to debug the SQL and the abstraction layer. I'd rather have fewer unnecessary abstraction layers.

> If I pull a datetime from SQL there's a lot of value in having a single piece of code handle that datetime the same way across the entire stack.

There's value there, datetimes are very complex, but the rest of the stuff it comes with obscures the value IMHO.

> Obviously you are encountering code made by people who don't understand this but the problem isn't the ORM. They would have made that mistake with or without an ORM.

It's hard to write the kind of complex queries I've seen by hand, and I like to imagine if you out how to do that, you'll also know why it's slow and not need my help... But the ORM is part of the problem, because when you've written bad queries by hand, and I give you a better query (or sequence of queries), it's easy to apply. When you've done it with an ORM, you may not even know where the query is made.

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

#154

Post by lelanthran »

ahartmetz wrote: post #137
sulam wrote: post #132

The argument that really hits home for me, after 30+ years in this industry, is stored procedures. The “Stored Procedures are Evil” argument to me is an artifact of an industry that promotes treating engineers and infrastructure as entirely interchangeable and anything that gets in the way of that is Evil(tm). But what working at Salesforce in the 2000’s taught me is that you can do really amazing things if you’re wi…

I haven't used stored procedures yet, but even ON DELETE CASCADE is super convenient and I suspect somewhat underused by SQL scaredy cats.

> I haven't used stored procedures yet, but even ON DELETE CASCADE is super convenient and I suspect somewhat underused by SQL scaredy cats.

Sooner or later you are going to hit enter, wait a few seconds and say "oops!"

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

#155

Post by ElectricalUnion »

zbentley wrote: post #142
ibejoeb wrote: post #129

Earlier quoted context omitted.

Wouldn't you consider defining the schema doing the domain modeling? I think ORMs do too much. I want to control the querying, or, more precisely, I want to control the SQL that goes to the planner. The good ones largely do allow for this, but I can't think of one that has innate support for vendor-specific features. What I do appreciate is that they handle the boilerplate like managing connections, preparing stateme…

> Wouldn't you consider defining the schema doing the domain modeling? No, because if the schema is the only reference for data models, developers on any sufficiently large team will come up with extremely widely varied queries to access equivalent information. Those are more likely to be incorrect (someone with domain expertise on one set of tables might miss that authoritative data needs to be joined/queried from e…

> developers on any sufficiently large team will come up with extremely widely varied queries to access equivalent information.

Ah yes, the famous database integration anti-pattern.

> but ORMs also get you to that point without requiring as much ongoing discipline, care, and feeding.

[citation needed]

The fact that you have being practising "database integration" won't suddenly disappear just because you used a ORM. In fact I expect even worse database integration from your average ORM user, as people that uses ORM blindly often don't care (to their own detriment) about "silly issues" like data provenance or persistence mechanical sympathy.

At some point I expect the DBAs of such database integration nightmares will have to start handling stuff like column-level security and row-level security to prevent naive users from shooting themselves in the foot.

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

#156

Post by sshine »

runevault wrote: post #101
geophile wrote: post #97

I used to love ORMs so much that I built one for Java, in the early 90s, and it was one of the main offerings of a startup that I joined. I have come around 180 degrees. My rethink started when a developer at a Wall Street bank said: having Oracle on my resume is valuable. Having your ORM on my resume is not. And then there’s the “now you have two problems” dynamic. You not only have to write high-performing queries,…

ORMs are so incredibly finicky. I still remember using old Linq-to-SQL (not Entity Framework) and I had to write the linq query in the reverse order of what I expected or it created 3 nested subqueries instead of just joining the tables together. That was when I learned to instantly double check every ORM query I wrote.

> Linq-to-SQL (not Entity Framework) and I had to write the linq query in the reverse order

I remember those times! Had to write the LINQ, see what it compiled to, redo, until the query was efficient. Abuse LINQ subtleties in how it generated JOIN predicates since it only supported equality. Something about finding an equivalent way of expressing a query with sub-selects that is also computationally equivalent. All so I can get my efficient SQL without writing SQL. So silly.

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

#157

Post by ahartmetz »

wavemode wrote: post #144
ahartmetz wrote: post #137

Earlier quoted context omitted.

I haven't used stored procedures yet, but even ON DELETE CASCADE is super convenient and I suspect somewhat underused by SQL scaredy cats.

ON DELETE CASCADE is horrendously unsafe unless you have full understanding of the entire data model - which is unlikely for the average employee within a large organization with a gigantic database. (And it's also rare to be permanently deleting data when working in such a context, so the convenience doesn't matter that much.)

It's in the context of "SQLite as local data storage for an application", and I am absolutely sure that entries in a cross-reference table make no sense anymore when one of the linked objects is gone, or entries in an auxiliary data table when the principal object is gone.

I am not using ON DELETE CASCADE to be clever - the referenced data is genuinely required.

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

#158

Post by talon8635 »

swasheck wrote: post #17
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…

> 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

Believe what you want, but I would consider myself one of those allegedly mythical people

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

#159

Post by jemiluv8 »

I’d go with a balanced view: you need them both for any non-trivial product. I was recently reviewing a PR that renamed a model, I wanted to understand what happened under the hood. Turns out that mariadb had a rename table operation forever ago and that was used by the orm under the hood. So no need to backup the prod table. Just run migrate and be done with it.

PS: I still exported the table before deploying this fyi.

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

#160

Post by sandreas »

ElectricalUnion wrote: post #150
sandreas wrote: post #106

Earlier quoted context omitted.

Additionally I think the migration management that most ORMs support are also a good thing. Defined and type-safe forward and backward strategies are helpful in most cases, especially if you'd like to support more than one DBMS. I personally think that ORMs are good for management and simple CRUD cases, QueryBuilders are good for managing more complex queries while still being secure / type-safe and for everything el…

> ORMs are good for management and simple CRUD cases I for one think that "simple CRUD cases" is bullshit, those applications don't exist. In practice, System-of-Records systems are rare. (and should be, their value are inversely proportional of how many of those you have in your overall system). Because if it was "just simple CRUD", one would use the database directly? Databases are already capable of handling CRUD…

If the ORM is capable of validation or integrates with such a component, i personally think that it integrates well for these parts of an APP, where simple datamanagement is required... E.g. adding, editing and deleting DB records, that need forms and validation.
Post reply on HN