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

41–50 of 360 posts

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

#41

Post by programmarchy »

wasted_intel wrote: post #25
djrobstep wrote: post #16

Earlier quoted context omitted.

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.

Sure you can. SQL is just plain text, so keep all your create table scripts in your repo, along with deploy and rollback scripts that you’d use to extend or migrate your schemas.

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

#42

Post by grv07 »

grangerg wrote: post #21

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

how your dev. can write optimal query with ORM if they are not good in SQL.

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

#43

Post by zzzeek »

> 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 query composition, providing abstraction for database-specific and driver-specific quirks, providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL, so you'll end up inventing that part yourself without an ORM (I recommend doing so, on a less critical project, to learn the kinds of issues that present themselves). None of those things should be about "hiding SQL", and you need to learn SQL first before you work with an ORM.

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

#44

Post by sethammons »

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.

The one that bit me with sqlalchemy is joins. You have to structure your sqlalchemy object in a specific way to do joins. Usually I write my sql query then spend half an hour trying to convert it to sqlalchemy.

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

#45

Post by mythrwy »

andrewstuart wrote: post #17

I'd rather learn the ins and outs, problems and issues, highs and lows, of SQL rather than an ORM. ORM require just as much investment in time and even then you still need to learn the sql to get the ORM to do what you want it to do. SQLAlchemy on Python is a truly fine piece of software but in the end it was much simpler and felt more powerful for me to write the SQL. And not even hard BTW. I only have a limited amo…

I prefer SQL to ORMs as well for exactly the reason you state. It's more comfortable and it's less of a mystery.

But you don't always get to pick what code you'll be working with and if you are working with others in a web framework pretty good chance you'll be learning an ORM anyway.

Also really long SQL statements are no fun. Like debugging a whole program written in one line. I think sometimes there is a temptation to get excessively clever with SQL.

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

#46

Post by jjoonathan »

brucephillips wrote: post #12

> ORMs, however, encourage this use and often make writing precise projections as tedious as they are in SQL. "As tedious as SQL" is not an argument to use SQL instead.

Every extra abstraction layer comes at the cost of complexity, maintenance, and capability, and therefore inherits the burden of proving its worth. ORMs frequently claim to be less tedious than SQL. Failure to deliver on that promise is a reason to cut them out.

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

#48

Post by [deleted] »

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.

[deleted]

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

#49

Post by other_herbert »

0xCMP wrote: post #35
wasted_intel wrote: post #25

Earlier quoted context omitted.

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.

I mean you could, but it’d need to be through some kind of migration system to update it.

Flyway for Java... For stored procedures you use the repeatable syntax that way it checks the checksum of the file and if it doesn't match what is in the migration table it will run it... Easy and always up to date... That's with Java anyway...

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

#50

Post by flukus »

Someone1234 wrote: post #20
adamnemecek wrote: post #15

I don't hear people complaining about say LINQ. Maybe it's just that your language and/or ORM or integration thereof suck?

LINQ isn't a ORM. I assume you mean Entity Framework? EF definitely has the foreign key issue. We have around a thousand tables, we tried to generate the classes for all of them including foreign keys, problem is that when you create a context that references even only a single table, it will load everything that is foreign keyed including siblings of siblings of siblings, until you run out of memory. Only way around…

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.

Post reply on HN