What ORMs have taught me: just learn SQL (2014) • Hacker News Board

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

#21

Post by RangerScience » Fri, Sep 20, 2019, 9:30 PM UTC

daenz wrote: post #14

ORMs lure you in with a false sense of neat abstraction. They have nice intuitive examples on their home pages. But then you use them in the real world, doing gnarly queries, and you realize that doing anything powerful and fast in the ORM requires its own completely separate abstractions, which are often difficult for the uninitiated to follow. It's also often a big pain to debug the raw SQL that gets compiled after…

Which ORMs do you work with?

Whenever I get to the point where I'd do something gnarly with a query, I just fall back to SQL statements with results that the ORM can parse; or just dump directly into data structures and go from there, even if the originating tables still have ORMs.

If your framework/library/language doesn't let you skip the ORM - or populate the object with the results of a custom query - well, I'd say that's a failing of that ORM, not of ORMs in general.

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

#22

Post by BerislavLopac » Fri, Sep 20, 2019, 9:31 PM UTC

My personal gripe about ORMs is that they have two main usage patterns, and each of them has major drawbacks.

The first pattern, common in frameworks like Django, is to cram the business logic into the ORM instance objects. This creates a tight coupling between the two separate concerns (business logic and data persistence), and will cause problems as soon as the two structures deviate from each other.

The second pattern is to keep the ORM as a purely data storage layer, which is much more flexible but commonly causes duplication of nearly identical structures in separate layers.

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

#23

Post by whitehouse3 » Fri, Sep 20, 2019, 9:31 PM UTC

> In these cases, I’ve elected to write queries using a templating system and describe the tables using the ORM. I get the convenience of an application level description of the table with direct use of SQL. It’s a lot less trouble than anything else I’ve used so far.

This feels like good balance. I want to express my database schema via OO classes. It eases db migrations as the application grows if you use tools like Alembic (same developers as SQLAlchemy).

But use care to avoid SQL injection risk with template queries. SQLAlchemy makes this easy using .bindparams, as does .NET via SqlCommand.

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

#24

Post by james_s_tayler » Fri, Sep 20, 2019, 9:31 PM UTC

What ORMs have taught me:

Use an appropriate combination of ORMs and raw SQL.

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

#25

Post by blackflame7000 » Fri, Sep 20, 2019, 9:32 PM UTC

spullara wrote: post #6

Even if you use an ORM you should probably know SQL so you can use them efficiently. Also, if you use an ORM, make sure it "starts with database schema" not with objects. Otherwise the mismatch will probably be horrible for you. A dozen years ago I published this article on generating your domain objects from the database, still a reasonable thing to do if you want the added query expressiveness the ORMs have over mo…

A feature of Netbeans I always liked was the ability to create a Hibernate Java Object directly from a database table. It does make sense to me to start at the DB first because the application is just a broker between the front end and the DB so it's more flexible to make the adaptations there.

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

#26

Post by RangerScience » Fri, Sep 20, 2019, 9:32 PM UTC

thenewnewguy wrote: post #12

jackschultz wrote: post #8

When I started web work ~9 years ago, it was with Rails and ActiveRecord, which turns out to be incredibly good for basic queries and pretty basic apps. So good to the point where I never bothered to go too far into SQL until years later, which was a mistake. When doing work in python, I don't feel it has a comparable ORM, to where I kind of write my own files that have things like finders and updaters and creators.…

Current rails developer here, I've found that using ActiveRecord for basic queries and moving to SQL for more complex stuff to be a very good combination. I agree that ORM users should learn SQL, but SQL and an ORM are not mutually exclusive.

Yep, also current rails dev here and this is also what I do :)

Learning SQL with an ORM does remind me a bit of learning memory management with a garbage collector: helps you make better choices as to how you put things together, even if you never directly use the know-how.

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

#27

Post by invokestatic » Fri, Sep 20, 2019, 9:32 PM UTC

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 queries actually ran on the DB. I think it really boils down to using the right tools, the right way.

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

#28

Post by conroy » Fri, Sep 20, 2019, 9:33 PM UTC

If you use Go and PostgreSQL, I’ve been working on a tool to help you “just use SQL” called sqlc[0]. It generates methods and structs for your queries, automatically. It’s made a dramatic difference in my day-to-day workflow.

[0] https://github.com/kyleconroy/sqlc

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

#29

Post by Roboprog » Fri, Sep 20, 2019, 9:33 PM UTC

Yep. I’m an ORM-hater, too. Feels like “leaky obstruction”, to me.

But there’s got to be some reason people keep embracing it, even if I’m not one of them.

Possible reasons, which you can rephrase as productivity virtues, if you are so inclined:

* I don’t spell well

* I don’t test much

* I don’t care how much time and bandwidth it uses

* I only know Java (or C#, or whatever)

Sounds despicable to me, but could be many small scale Enterprise projects, I guess.

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

#30

Post by wvenable » Fri, Sep 20, 2019, 9:33 PM UTC

daenz wrote: post #14

ORMs lure you in with a false sense of neat abstraction. They have nice intuitive examples on their home pages. But then you use them in the real world, doing gnarly queries, and you realize that doing anything powerful and fast in the ORM requires its own completely separate abstractions, which are often difficult for the uninitiated to follow. It's also often a big pain to debug the raw SQL that gets compiled after…

You don't use ORMs for gnarly queries -- that's not what they are for! They are for making manipulating the entities easier -- reading the data out of the database in a way that makes easy to modify.

You can (and should) use them for simple queries. You have a list of entities you want to query and filter, that's going to be fine. Joins are fine. But if you're doing some complex analysis, an ORM is the wrong tool. That doesn't mean it's a poor abstraction, or difficult to follow, or something to be avoided. It's not the right tool for that job. For the job it's designed for, it's going to save a lot of effort.

SQL is great for analysis -- it's pretty much what it's designed for. But for bringing data into your app and modifying it, SQL is cumbersome and verbose. If you're loading data into objects then you're just creating your own personal ORM anyway.