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)

wozniak.ca

Post reply on HN
Print view View original

111–120 of 654 posts

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

#111

Post by wvenable »

daenz wrote: post #101
wvenable wrote: post #61

Earlier quoted context omitted.

ORMs let you drop into SQL whenever you need, usually in a way that is fully compatible with the model, so that's entirely false.

Running raw user SQL isn't a prerequisite of an ORM needed to make it an "ORM", it's a useful feature that most ORMs try to include because the authors recognize the many shortcomings. Also, by writing raw engine-specific SQL, you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. And by "drop into", this typically means writing custom stitching code that stitches the SQ…

> you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic.

The biggest selling point is a massive reduction in boilerplate code. Database agnostism is a feature that almost nobody ever uses, so who cares! Your proposed alternative is engine-specific SQL so you lose either way. At least with an ORM, you'd lose significantly less. You'd just have to deal with the places you used SQL. Which, in my experience, is pretty small and pretty specific.

> this typically means writing custom stitching code that stitches the SQL cursor results back into the models again.

I often feel like people who complain about ORMs have either actually never used one or used a poor one. As long as my query matches the structure of my object(s) I don't need any stitching code. And if they didn't match, I wouldn't write stitching code because that would be waste of time.

If I'm writing a custom query, I'm probably not looking to integrate into the model anyway -- if it's used for reporting I'd just take the results as is. If I'm writing a query specifically to get a matching model object (to manipulate) I'm going to get the whole model and no stitching would be required.

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

#112

Post by gfiorav »

I knew sql before using ORMs. It can never substitute that.

What it can do is abstract most common operations to do with your dB, and also help with mocking some tests.

I really don’t see the point in these articles. It’s not supposed to save you from learning SQL

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

#113

Post by wvenable »

daenz wrote: post #82
wvenable wrote: post #41

Earlier quoted context omitted.

That's not true. A simple job might be 3 lines of code in an ORM to update a record. In SQL that will be a lot more code especially if that's wiring up a foreign relationships. With SQL you will also have a lot of uncheckable strings containing code. Simple tasks are done maybe thousands of times in any one application. It's the complex tasks are rare.

>A simple job might be 3 lines of code in an ORM to update a record. In SQL that will be a lot more code especially if that's wiring up a foreign relationships. That's not a fair comparison unless you include the time and effort involved in creating your ORM models, before you can even write those 3 lines of ORM code. The effort to construct those models isn't even fully amortized over your project, since it must be…

Creating an ORM model is so simple the only time I've even given it a second thought is when composing this reply.

It's no more difficult than building tables by hand. There isn't any extra work that you wouldn't also have to do when not using an ORM.

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

#114

Post by iamleppert »

I too have gone back to SQL after working with ORMs for 10+ years. Having worked with them on a wide range of projects and teams, I can say, without any reservation, they are not worth it.

Not worth it for the small projects nor the large projects. They significantly complicate the development workflow and add another layer of (often times, cumbersome) abstraction between the user and the data.

If you encounter any issues (and better pray you don't), expect to spend hours stepping through reams of highly abstracted byzantine code, all the time feeling guilty when you just want to open a database connection and send the SQL string you developed and tested in minutes.

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

#116

Post by fsloth »

trjordan wrote: post #10

Code thinks in objects and functions and values and pointers. Databases think in tables and columns and rows and queries and indexes. If you don't pick an ORM to help manage this translation layer, then you'll end up re-implementing your own. Maybe this is OK, because yours will be simpler for quite some time. What else are you going to do? Stored procedures? Concatenated strings?

Code can think just fine in tables and columns as well.

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

#117

Post by ojglees »

ineedasername wrote: post #93

I work a lot with SQL, not at all ORM's. (I'm on the data extraction, modeling, analysis side). So what's the counter argument here, where to ORM's excel?

The counter-argument is that for a lot of applications you have a bunch of tables and a bunch of objects and there's a lot of repetitive, boring code to write just to get simple CRUD working. The hobby Java ORM tool I wrote (http://hrorm.org) only does simple CRUD. Everything else it leaves to you using JDBC and SQL, since the problems being discussed here are real. But for managing persistence of simple object models, I think ORMs are a big time saver.

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

#118

Post by qes »

invokestatic wrote: post #27

This topic pops up frequently here on HN and every time I’m shocked at how many people have issues with ORMs! I’ve been using Hibernate/Spring Data for several years now and never ran into any issues. If I need to write a complex query, I can easily write a @Query annotation in HQL and it neatly fits right in to the repository class. I also develop with query logging enabled so I have better understanding of the quer…

> I’m shocked at how many people have issues with ORMs

Simple inexperience. I'd bet most of those people are mid-level developers who have used ORMs enough to hit the rough edges but not enough, or with enough independent agency, to have worked through how to play to ORM's strengths while avoiding their weaknesses. People that were given a hammer and are just understanding that their hammer doesn't work very well to install bolts, and maybe don't have the authority to say maybe I should use a wrench or the flexibility to try this new crescent shaped idea and see if that works better.

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

#119

Post by timdev2 »

daenz wrote: post #101
wvenable wrote: post #61

Earlier quoted context omitted.

ORMs let you drop into SQL whenever you need, usually in a way that is fully compatible with the model, so that's entirely false.

Running raw user SQL isn't a prerequisite of an ORM needed to make it an "ORM", it's a useful feature that most ORMs try to include because the authors recognize the many shortcomings. Also, by writing raw engine-specific SQL, you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic. And by "drop into", this typically means writing custom stitching code that stitches the SQ…

> you automatically invalidate one of ORMs biggest selling points which is being SQL-database agnostic.

I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Maybe some commercial products still try (choose MS or Oracle!), but it's rare nowadays.

The primary selling point of an ORM is that it abstracts marshaling/un-marshaling rows to/from entities. Instantiating and persisting entities to relational storage.

> And by "drop into", this typically means writing custom stitching code that stitches the SQL cursor results back into the models again. It's rarely straightforward.

That's not typical in most uses I've seen. Far more typical are things like:

- Go straight to SQL for reporting, since that's what SQL does. Useful in reporting contexts, and also for list/filter UI screens.

- Use raw SQL to query a list of entity IDs for updating based on some complex criteria. Iterate over the identifiers and perform whatever logic you need to before letting the ORM handle all the persistence concerns.

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

#120

Post by syntheticcdo »

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…

I disagree. Knex isn't an abstraction over SQL itself, as much as an abstraction over the tedious process of writing literal strings, which let's the developer treat SQL queries as code.

My process is generally - write SQL to find the best query, then convert the SQL to Knex for integration.

As a simple example, say you are making a simple query to the db, but in some edge cases you need another column to be selected. Knex lets you do this:

    let query = KNEX("table").select("col_1")
    
    if(weirdCondition) {
      query = query.select("col_2")
    }

    const rows = await query
Which naturally compiles into either

    SELECT col_1 FROM table
or

    SELECT col_1, col_2 FROM table
Post reply on HN