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

151–160 of 360 posts

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

#151

Post by blaisio »

zkomp wrote: post #133
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…

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.

I mean, stored procedures are fine, but I don't think that actually solves anything? Except maybe for reducing the amount of SQL code you have to send back and forth and (in some databases) allowing for a few more optimizations?

If you use stored procedures, all you've done is move part of the model into the database, so you have to update the stored procedures as part of a deployment. You still need to have the SQL code written out somewhere, and you still need to have something in the application code that knows which procedures exist and how to use the data they return in business logic.

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

#152

Post by jbergens »

int_19h wrote: post #112
avmich wrote: post #107

Earlier quoted context omitted.

> A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. What's the difference? To me, an ORM is largely a query builder.

Is it a good one, though?

Bascially all in-house built query builders I've seen has been much worse.

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

#153

Post by eecc »

Yup, take those Repository designs, where you can’t join on two tables unless stars align very specifically. Of course there are very good technical reasons for that, but isn’t this because the abstractions are leaking onto each other? What’s the added value of the extra layer then?

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

#154

Post by useerup »

Someone1234 wrote: post #67
flukus wrote: post #50

Earlier quoted context omitted.

Sounds like you disabled lazy loading and that forced everything to be loaded at once. That's a user error, not a EF fault. This is a common theme I've seen with people blaming ORM's for being slow, it's the devs not using them appropriately more than the ORM's themselves. Not to say that they don't have their own issues.

With or without lazy loading enabled the result was the same. Generating the structure took seconds and went OOM with enough tables. LazyLoading impacts what data is retrieved from the database (or more to the point when), this is a structural issue before a query was even sent to the database. It would die while generating the query, not sending the query or populating the result. You likely should have asked for mo…

I have been working with EF for years now. It has it quirks - but this is not something that I have ever experienced, nor have I heard of anything like it before today.

What I have heard of is traversing the entire graph and causing cascading loading of navigational properties. Yes, I have done that. Something like AutoMapper will do that to you, if you are not careful. Been there and done that.

How did you determine that it OOMed while generating the query?

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

#155

Post by rickycook »

needusername wrote: post #139
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…

> providing abstraction for database-specific and driver-specific quirks That is quite theoretical. My PRs for fixing non-spec compliant behavior in pgjdbc get rejected because they might break some ORMs (mostly Play). My PRs for adding MariaDB sequence support to Hibernate get rejected because there are additional MariaDB features that Hibernate doesn't support as well.

it might be theoretical in hibernate, but sqlalchemy does a TON of this stuff

as an example: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalch...

heaps of this kind of thing are nicely dotted around the code so you don’t have to deal with weird driver quirks, maps “Text” column type to whatever it needs to be in your given database to have an unbounded text blob

id say that’s far more than theoretical

EDIT: typo

EDIT 2: also, on DB specific features, SQLA supports a bunch (not all). eg: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalch...

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

#156

Post by eecc »

jeswin wrote: post #77
eru wrote: post #70

Earlier quoted context omitted.

Is LINQ actually an ORM? It looks like a DSL to do relational stuff in C#, but I don't see where the 'O' part of ORM fits in especially not in your examples to show off LINQ's power and convenience.

LINQ is not an ORM. My examples were showing easy, lazy evaluation. My C# is rusty, but the examples mostly hold I think. The following gets executed on iteration (of expensiveOrders), the lambda is compiled into a Function and run on each item in orders: IEnumerable orders = ...; const expensiveOrders = orders.Where(o => o.total > 100) The following gets compiled into an expression tree, which an ORM can analyze and…

Linq looks like a free monad: you declare the program as a data structure and the runtime implementation just interprets it on the go (something you could - not so easily - try to do with a strategy pattern)

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

#157

Post by href »

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.

+1 SQLAlchemy has had a solution for every problem I threw at it in 5 years of heavy use!

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

#158

Post by Zancarius »

nerdwaller wrote: post #145
andrewstuart wrote: post #93

Earlier quoted context omitted.

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.

+1 - personally I really like how SQLAlchemy doesn’t abstract away the database so much where all modeling and power is lost. He and the contributors have done an excellent job.

I've always been impressed by zzzeek's near-omnipresent participation. Years ago, I had a question about SQLAlchemy that he'd answered on the mailing list which amazed me given the relative rarity of developer interaction. Yet here we are almost a decade later and he's still answering questions directly, but on many more platforms. I'm actually not convinced he ever sleeps.

I wish more people aspired to be like him, because you know there's no more authoritative answer when you run into a Stack Exchange post and he's offering up his assistance.

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

#159

Post by bmarkovic »

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 case of people actually utilizing ORM to actually change RDBMs they store data in.

Not all ORMs are like that and the best solutions are the ones like Knex/Objecion where an ORM (Objection in this case) is nice abstraction for single-object access/writing and underlying SQL builder (Knex) is fully exposed and used for everything else.

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

#160

Post by rickycook »

alexandercrohde wrote: post #61
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…

I think there are 3 main reasons ORMs came into common use: 1. As a reaction to common SQL injection from poor libraries not implementing parameterized queries. (2004 or so) 2. Novice engineers not wanting to learn SQL (look I learned how to make a blog in RoR, and I like mongo!) 3. As a theoretical abstraction above the data-store (as though you might someday be able to switch the data-store beneath the ORM) 1 has b…

so, i think #3 is less useful for switching out the actual DB server itself in an ongoing project, but i’ve found it immensely useful in a couple of ways:

- replaced the DB driver mid way through a project to a slower, but more complete implementation. this went flawlessly, because it’s all so generic and in the end the same DB under the hood, so a simple change (unless you don’t use an ORM where i could see it being a nightmare)

- started a new project where i had to use MSSQL, which I’d never used before, but i’m a big fan of postgres/sqlalchemy. other than ODBC oddities, it was really simple to write the new app with all the same patterns i was used to with things like update on write, lazy joins, constraint deferral, and i think most importantly would be MIGRATIONS! huge help to have the same migration framework that i was used to

Post reply on HN