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

21–30 of 360 posts

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

#21

Post by grangerg »

It depends on what you're after.

I'm very comfortable in SQL, and would prefer to write my queries. But our team has grown, and we've found that developers say they know SQL, but they really don't. In general, I've found that it's not the syntax that messes people up. There's a significant mental "jump" between the usual procedural coding paradigm and the "set based" paradigm offered by SQL. Some people just never catch on.

So we're going to start using an ORM (Entity Framework Core 2) so that the "mere mortal" developers can pitch in. I know there's a way to run raw SQL, so I know that when we find spots where the ORM fails, we can just rewrite it with some good SQL if we decide that's best.

But maybe that'll never happen? We've been trying to do simpler SQL stuff as of late so that the heavier lifting in the system is done by the application/client instead of the database (bottleneck). As the database sees simpler, less unique queries, more stay in the plan cache, indexes are more reliably hit as expected, and performance increases.

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

#22

Post by default-kramer »

I've found ORMs (such as Entity Framework) great for operations that can be described as "find a single thing by PK and update it." For read operations I've favored this strategy: "Imagine the ideal result set for the task at hand. Use SQL to deliver that result set. Do the rest of the work in your app language of choice."

Edit: I guess I should clarify that I would favor using any library that maps result sets to lists of objects. And I would consider that to be part of "do the rest of the work in your app language of choice."

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

#23

Post by putlake »

blaisio wrote: post #14

I think saying "Just use SQL" is probably a bad idea. You'll most likely end up implementing an ORM anyway, or you will end up with your model code mixed up everywhere with your views. I do think a lot of people use ORMs as a crutch, which sucks. Also, ORMs often provide too much abstraction, forcing people who actually know SQL to relearn how to do everything the way the ORM happens to like it. I should not have to…

AlphaZero's chess strategies turned out to be quite different from how humans have traditionally come up with chess heuristics/strategies. I wonder what paradigms will be used by AI that does computer programming. Will it organize code into some bits of functional programming, some OOO? Will it structure things into MVC? My guess it whatever AI does will be completely inscrutable to us. It may be optimized for efficiency rather than understandability, which humans need for maintainability.

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

#24

Post by Shorel »

I do agree every one who needs to get data from an SQL database should know SQL, and mostly, should know about indexes and how and why queries can be slow.

But a query builder is extremely useful and in any complex application, if you don't use one, you end up bulding one yourself, which may not be a very good idea if you don't understand things like query injection.

So learn SQL, learn ORM, and choose in a case by case basis.

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

#25

Post by wasted_intel »

djrobstep wrote: post #16
jtchang wrote: post #13

Why are we even having this debate? There are some ORMs that bring so much value to the table that you'd be stupid not to use them. Example being Django's ORM or SqlAlchemy. Also be specific in what ORM you are comparing to raw SQL. Are you talking about Hibernate or SQLAlchemy. Are you talking about a query builder? Good ORMs help tremendously with maintainability and security. They also let you drop down to raw SQL…

I've used SQLalchemy a lot. I've even written a keyset paging extension for SQLalchemy. But recently I've switched to writing stored procedures and calling them directly, instead of going through an ORM for everything... And it's so much easier.

Keeping logic in the database like that means you can’t version those procedures alongside the rest of your code. That’s a pretty big downside.

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

#26

Post by jes5199 »

nine_k wrote: post #19

Object -relational mapping is in many cases an excessively leaky abstraction. I try keep away from it. A DSL for writing SQL in a nice, composable way is a useful thing. Some libraries, like SQLAlchemy, provide both levels, not insisting on using the object mapper.

SQLalchemy is kind of a pain in the ass to actually use, though. The DSL doesn't feel very Pythonic, it's weird and confusing. The way it traverses the Object graph when loading associations between models is magical and opaque and I could never predict when it was going to automatically work and when it wouldn't.

I actually would rather be writing Ruby on Rails, because it's _less magic_ than SQLalchemy.

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

#27

Post by dfee »

pbecotte wrote: post #11
leeoniya wrote: post #4

query builders are the sweet spot.

I agree- Sqlalchemy without the ORM is pretty much the best code I've seen.

How does the ORM detract from sqlalchemy? I’ve been very happy with it for years, and I’m not clear on where an advantage is explicitly not using mappers.

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

#28

Post by craigvn »

blaisio wrote: post #14

I think saying "Just use SQL" is probably a bad idea. You'll most likely end up implementing an ORM anyway, or you will end up with your model code mixed up everywhere with your views. I do think a lot of people use ORMs as a crutch, which sucks. Also, ORMs often provide too much abstraction, forcing people who actually know SQL to relearn how to do everything the way the ORM happens to like it. I should not have to…

> You'll most likely end up implementing an ORM anyway,

This is a really good point. Many people start with the "no ORM" philosophy, realize their application needs some way to map the SQL to the code, time passes..., they have implemented their own half-baked ORM.

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

#29

Post by lykr0n »

jes5199 wrote: post #26
nine_k wrote: post #19

Object -relational mapping is in many cases an excessively leaky abstraction. I try keep away from it. A DSL for writing SQL in a nice, composable way is a useful thing. Some libraries, like SQLAlchemy, provide both levels, not insisting on using the object mapper.

SQLalchemy is kind of a pain in the ass to actually use, though. The DSL doesn't feel very Pythonic, it's weird and confusing. The way it traverses the Object graph when loading associations between models is magical and opaque and I could never predict when it was going to automatically work and when it wouldn't. I actually would rather be writing Ruby on Rails, because it's _less magic_ than SQLalchemy.

True, kinda. I love SQLalchemy and it does feel different to use than every other python package I've used, but I'm not sure if I could do it better.

It's pure magic.

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

#30

Post by brucephillips »

qaq wrote: post #18
wybiral wrote: post #9

I prefer to use SQL directly as well. But some points to be made in favor of ORMs (some of them, anyway): * Multiple backend support to handle different SQL engines. * Minimized risk of accidental injection. * Migrations.

Migrations are orthogonal to ORMs.

No. Migrations can be auto generated from Model changes.
Post reply on HN