What ORMs have taught me: just learn SQL (2014) • Hacker News Board

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

#221

Post by celticmusic » Fri, Sep 20, 2019, 11:53 PM UTC

bob1029 wrote: post #109

joelS wrote: post #71

Surprised to see no one has mentioned query builders (like http://knexjs.org - no affiliation). All the niceties of not writing raw queries but no abstraction leaks... Although I have found for anything more interesting (recursive queries, etc) there is no escaping raw queries and every developer needs to bite the bullet and learn SQL.

I see query builders as a learning tool for fresh developers, and as a job aid for business analysts. For some this can be the final destination, but for any developer I would push hard to get them writing SQL by hand ASAP. A few weeks of suffering through DIY SQL is really the only way to fundamentally understand how the database is working for (or against) you. Once you learn it, it really does become like a second…

query builders are useful for building queries dynamically.

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

#222

Post by QuadrupleA » Fri, Sep 20, 2019, 11:57 PM UTC

As a refugee of Hibernate etc. I definitely agree with this. Python in particular already has data types that map nicely to relational data - dict, list, tuple, iterators - so mixing SQL calls directly in code works great.

Plus you get full access to the particular database's features, direct control & understanding of what's being executed when, easier query & performance tuning, no special 2nd pseudo-sql dialect to learn, no big extra stack of leaky abstractions to troubleshoot, etc.

One of the big pitches of ORMs is "you can change your DBMS mid project!" Which sounds cool and has its occasional applications but is something I've never actually needed in over 20 years of development.

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

#223

Post by hinkley » Fri, Sep 20, 2019, 11:58 PM UTC

stickfigure wrote: post #208

Beginning programmer: ORMs let me write code without learning SQL! Intermediate programmer: ORMs just get in the way! SQL isn't that hard after all. Advanced programmer: I write a lot of SQL, but I use ORMs to cut out most of the boilerplate.

The phrase you may be searching for is 'informed consumer'.

The world is full of tools that experts use because they understand what the tool is doing, and why this is a good thing... most of the time.

Having the tool is a poor substitute for the knowledge that led you to use the tool instead of doing it by hand. There are times where you would not do a thing by hand and so you don't ask the tool to do it, and there are times you ask the tool to do something you would never do by hand.

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

#224

Post by celticmusic » Fri, Sep 20, 2019, 11:58 PM UTC

bob1029 wrote: post #51

Swapping from EF6 to Dapper was one of the best choices we ever made with our project stack. It is so relieving to be able to hand-tune queries and transactions now. Initially, we were sold on the apparent simplicity of EF6, but as with many things there is a cost for an abstraction like this. In our case, the performance penalties and opaqueness were dealbreakers after a while. We saw an average speedup of 10x on al…

This is just another take on EAV (https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80...).

I'm unsure if it's better than the typical way of modeling EAV though, I'd have to think it through.

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

#225

Post by taffer » Fri, Sep 20, 2019, 11:58 PM UTC

celticmusic wrote: post #209

taffer wrote: post #169

Earlier quoted context omitted.

> Advocating for the use of SQL over an ORM in every case is like advocating for the use of Assembly over C in every case. Comparing SQL to Assembly is probably one of the worst comparisons you can make. SQL is a very high-level and powerful declarative language that I would compare, if at all, with functional languages and not with assembly. A few lines of SQL are counting, sorting, grouping, aggregating, and mergin…

an analogy is not about comparing, it's about communicating an idea. pointing out that the comparison is not perfect does not invalidate or refute anything. Every analogy, by definition, is not perfect. If the analogy were to ever become perfect, it would stop being an analogy and would instead be a tautology. "an ORM is like an ORM". IOW, the differences are WHY analogies are useful.

The point was not that the comparison was not perfect, but that it was utterly wrong. SQL is not at a lower level of abstraction than procedural code, but at a higher level. Abstraction should make complex things easier, not harder.

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

#226

Post by ratww » Sat, Sep 21, 2019, 12:00 AM UTC

QuadrupleA wrote: post #222

As a refugee of Hibernate etc. I definitely agree with this. Python in particular already has data types that map nicely to relational data - dict, list, tuple, iterators - so mixing SQL calls directly in code works great. Plus you get full access to the particular database's features, direct control & understanding of what's being executed when, easier query & performance tuning, no special 2nd pseudo-sql dialect to…

I worked in some projects where we had to support multiple databases. Entity Framework and a few Database Views helped us keep it agnostic. It was very common in the pre-cloud enterprise world.

Uber did it recently, too. They changed from Postgres to MySQL. But I don't know if ORMs helped them or not.

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

#227

Post by JesseAldridge » Sat, Sep 21, 2019, 12:01 AM UTC

This was my position for a while. ORMs introduce a layer of magic which obscures what's actually going on under the hood. I decided I would just make raw SQL queries and handle mapping data explicitly.

I quickly ended up with a lot of duplicated code. So then I thought, "Well ok, I should add a bit of abstraction on top of this..." I started coding some simple functions to help map the tabular data to objects. One thing led to another and suddenly I looked at what I had done and said, "Wait a minute..."

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

#228

Post by celticmusic » Sat, Sep 21, 2019, 12:02 AM UTC

taffer wrote: post #225

celticmusic wrote: post #209

Earlier quoted context omitted.

an analogy is not about comparing, it's about communicating an idea. pointing out that the comparison is not perfect does not invalidate or refute anything. Every analogy, by definition, is not perfect. If the analogy were to ever become perfect, it would stop being an analogy and would instead be a tautology. "an ORM is like an ORM". IOW, the differences are WHY analogies are useful.

The point was not that the comparison was not perfect, but that it was utterly wrong. SQL is not at a lower level of abstraction than procedural code, but at a higher level. Abstraction should make complex things easier, not harder.

you're still missing the point.

An analogy cannot be wrong because it is not comparing anything. it's communicating an idea.

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

#229

Post by matthewmueller » Sat, Sep 21, 2019, 12:03 AM UTC

stickfigure wrote: post #208

Beginning programmer: ORMs let me write code without learning SQL! Intermediate programmer: ORMs just get in the way! SQL isn't that hard after all. Advanced programmer: I write a lot of SQL, but I use ORMs to cut out most of the boilerplate.

I guess I'm an advanced programmer. You described my exact progression :-)

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

#230

Post by blacksoil » Sat, Sep 21, 2019, 12:04 AM UTC

The problem on the article is because the author seems to be using ORM as a complete replacement of SQL query. When it's used like that, for sure it's gonna be a hindrance.

How I use ORM is to help automating some of the things, while on top of that, I'd still write raw queries for the more complex things. For example, I create base tables using ORM, and then lay off all the joins and more complexity in a handwritten views. For query, I use ORM for something simple such as direct single-table queries or straight forward joins. When things get more complex such as joining a bunch of tables, or doing nested queries, I just use raw query.

The thing about ORM is that it should always be used as a complementary tool. Feel free to mix and match it with raw SQL queries as needed. Trying to do everything in ORM is just too much. One would end up spending too much time learning about the inner working of the ORM, instead of getting things done. So yeah, use it as a complement to raw queries, not as a substitution.