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)

woz.posthaven.com

Post reply on HN
Print view View original

221–230 of 360 posts

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

#221

Post by toomanybeersies »

bmarkovic wrote: post #159

It's "Active Record" style ORMs like Hibernate that are the culprit, and the way many developers utilize them to avoid any contact with the realities of RDBMs which leads to data access antipatterns which lead to poor performance (multiple needless queries per request etc.). Another thing people need to really give up on is the pipe dream of switching databases -- you're not going to do it. I've never seen one single…

At my previous job we practically exclusively used Activerecord (the Ruby version), we had maybe half a dozen queries that were hard coded SQL.

We also managed to switch from MySQL to Postgres with about 1 minute of downtime on a 16 GB database.

This was a reasonably mature codebase, about 3 years old.

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

#222

Post by spdionis »

dizzystar wrote: post #191
pmontra wrote: post #170

Earlier quoted context omitted.

+1 for these three points. But I'm really surprised every time people tell me they look at the schema as defined into the ORM instead of at the table in the database. I'm really jaw dropped the few times I know somebody doesn't even know SQL, only the ORM. Maybe they look at it as if it were the reaction of somebody that thinks you must know assembly if you want to program Ruby, Python or Node (I don't.) Still, if yo…

Woah, who are you interacting with that not knowing SQL is rare? In my experience, nearly no one knows SQL, and the attitude seems to be that learning it at all is a waste of mental bandwidth. On the other hand, I wonder if knowing none at all is better than knowing a little.

On the other hand learning javascript frameworks... /s

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

#223

Post by jgraham »

andrewstuart wrote: post #93
zzzeek wrote: post #43

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating q…

zzzeek - can I take this chance to praise your work on SqlAlchemy. People say there's not enough thanks given to open source developers... here's thanks to you. It's the work of a craftsman.

I'm pretty sure that SqlAlchemy has caused more grief and frustration than any other single library. After all if I didn't know about the excellence of SqlAlchemy, maybe I wouldn't get so cross when I have to do anything non-trivial with the Django ORM ;)

Joking aside, SwlAlchemy is very impressive software, and zzzeek deserves all this praise and more. Every time that there's one of these anti-ORM articles I feel like SqlAlchemy pre-emtively addressed all the substantive criticisms in its flexible, well layered, design.

(And in the interests of fairness, Django's ORM is also very good at making simple things simple).

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

#224

Post by scarface74 »

ZenoArrow wrote: post #215
scarface74 wrote: post #209

Earlier quoted context omitted.

Just use stored procedures? Then you lose the ability to do unit testing without a database dependency, it's a lot easier to rollback code than to rollback code and stored procedures as one and you don't get full visibility on what the code is doing just by looking at the source code.

> "Then you lose the ability to do unit testing without a database dependency" Not really, you just mock the database calls in the code you're unit testing.

If all of your business logic is in the stored procedures, what are you actually testing?

And I realize that being able to test queries without database dependencies, only really applies to a few languages that treat queries as a first class citizen in the language like C# and Linq where you can mock out your actual Linq provider - replace the EF context with in memory List - and still test your Linq queries.

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

#225

Post by scarface74 »

pjmlp wrote: post #214
scarface74 wrote: post #202

Earlier quoted context omitted.

So the easiest way to avoid using an ORM is to create your own ORM? What AI do you need? You map your tables to objects and relationships between objects via FK relationships.

I can get very creative with SELECTs, making use of Prolog style queries, which are fully done server side on the database. Most ORMs will download all the data and evaluate them on the client side, with code that is even more convoluted that the SQL one and thus with less performance.

If your ORM is downloading all of your data and querying client side, then your ORM "is doing it wrong".

Entity Framework and any other Linq to data provider translates the Linq expression to the native language of the source data and does it server side.

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

#226

Post by red_admiral »

My 0.02 BTC on the matter:

Object-oriented programming 101 assumes that all your objects are in memory, in a graph, so you can do things like person.getFriends()get(0).getName() [assuming the person in question has >0 friends]. Each step in the graph is essentially a pointer dereference, costing a constant effort.

(If your data is small enough to fit in memory, that's what you should generally be doing. People who use hadoop for half a GB of data are usually doing it wrong.)

A relational database assumes that all your data fits on disk, but only a subset of it will be in RAM at any one time (and you generally have a network round trip every time you change that subset). This means you need a completely different way of thinking; this difference is sometimes called the "object-relational impedance mismatch". This is not to do with SQL and OOP just being different APIs for the same thing, they are designed for very different use cases.

ORM tries to pretend that this difference doesn't matter, and works quite well in simple cases when it really doesn't matter.

My standard example why it sometimes does matter: PersonDAO.fetchAll().size() is silly because it forces the database to fetch all Person objects, send them over the network, your application creates the necessary objects for them - and then you throw it all away again because all you needed was the number of people. PersonDAO.count() is much better, even if you have to implement it yourself.

If you don't like the syntax of SQL, sure - use a query builder. In C# or Java you can even get some kind of type safety that way. But you need to understand the difference between an object graph and a relational database to use either of them efficiently, long before you get to advanced ideas such as window functions.

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

#227

Post by walshemj »

scarface74 wrote: post #209
zkomp wrote: post #133

Earlier quoted context omitted.

No. You should really wind up with a DAL. Define some stored procedures for accessing and working on the data and use only stored procedures. No need for ORM, and no inline sql logic in your application code.

Just use stored procedures? Then you lose the ability to do unit testing without a database dependency, it's a lot easier to rollback code than to rollback code and stored procedures as one and you don't get full visibility on what the code is doing just by looking at the source code.

Not in my experience though our DBA was very good (oh my first boss was Dijkstra he mentioned down the pub one lunch time)

And would you not have your IDE on one monitor and your SQL IDE in another so you could look at both sets of code.

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

#228

Post by mattmanser »

ZenoArrow wrote: post #213
mattmanser wrote: post #196

Earlier quoted context omitted.

I've been writing C# professionally for ~12 years at this point. I'm extremely comfortable with SQL, the first startup I worked for for 3 or 4 years in the mid-2000s did amazing things with it and was extremely anti-ORM. We did things like write SQL that would automatically get translated into XML, which we'd combine with xslt to create dynamic pages. Yes, I've hit major problems with the EF (including one on Friday…

> "And screw switching to Core until they've sorted out lazy loading. Lazy Loading can also screw you, but again, it's just wonderful when you use it right." Are you referring to EF Core? I was considering learning it. What's this lazy loading issue? It's not one I've heard of before.

As far I understand it, it's half finished and progress has been super-slow:

2016 - https://weblogs.asp.net/ricardoperes/missing-features-in-ent...

2018 - https://github.com/aspnet/EntityFrameworkCore/wiki/Roadmap

Basic functionality like group by, lazy loading, etc. is missing. By the look of it you can't even load custom types from hand-crafted queries, which is pretty ridiculous.

Haven't really been keeping that up-to-date with it. I personally feel the whole Core thing has been a massive cluster-fuck for their existing customers.

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

#229

Post by zimpenfish »

scarface74 wrote: post #206
zimpenfish wrote: post #188

Earlier quoted context omitted.

> A query builder aids you at constructing queries ... that you understand and can be sure are sensible. > while an ORM builds queries for you ... that you have to hope are sensible. That's one of the biggest flaws of the ORM for me - you have limited visibility of what it's doing to your DB.

How so? I can log the the sql being generated and look at the logs. Something I should be doing either way.

If you have to log the generated SQL to understand what's happening, you're already behind the curve.

And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place?

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

#230

Post by pjmlp »

scarface74 wrote: post #225
pjmlp wrote: post #214

Earlier quoted context omitted.

I can get very creative with SELECTs, making use of Prolog style queries, which are fully done server side on the database. Most ORMs will download all the data and evaluate them on the client side, with code that is even more convoluted that the SQL one and thus with less performance.

If your ORM is downloading all of your data and querying client side, then your ORM "is doing it wrong". Entity Framework and any other Linq to data provider translates the Linq expression to the native language of the source data and does it server side.

I know EF, which is why my comment also mentions "code that is even more convoluted that the SQL one and thus with less performance".

LINQ only allows for a fraction of what is possible with SQL, and good luck having the best queries generated out of it, if the RDMS doesn't happen to be SQL Server.

Post reply on HN