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

141–150 of 654 posts

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

#141

Post by daenz »

wvenable wrote: post #113
daenz wrote: post #82

Earlier quoted context omitted.

>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.

  cursor.execute("""
    UPDATE employees
      INNER JOIN merits ON employees.performance = merits.performance 
      SET salary = salary + salary * percentage;
  """)
Please show me how much simpler and fewer lines that would be in ORM code + model definitions.

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

#142

Post by lemmsjid »

I've followed ORM like/hate flamewars as long as I've been in the industry, and I think I'll be following them for as long as I continue. It's really an interesting and ultimately irresolvable tug of war between abstraction, automation, and a number of other issues.

I think people who have used ORMs a long time do not see them as a SQL replacement or as a total database abstraction layer, but as an automation tool for CRUD operations, with some capabilities of doing interesting things with querying, and potentially a methodology for managing database schema and migrations (depending on the tool). At their best, ORMs can tremendously reduce one's requirements for unit tests in particular areas because they have enough structural metadata to typecheck (automatically or compiler time) all the way down to forms in interfaces.

BUT, without a doubt, ORMs are indeed a tremendously leaky abstraction. That said, I would argue that every data store interface is, in that sense, a leaky abstraction. No matter the datastore you're using, be it RDBMS or a specialized NoSQL / search store, you need to learn the hows and whys of how it is structured. Not only should a professional learn SQL, but they should get at least a layperson's knowledge of how the database underneath the SQL works. However, the argument still stands, because when debugging a complex ORM query, you are debugging how it puts SQL together, and then you need to debug what the generated SQL is doing. So you're now multiple layers away from the actual thing you're managing.

Hence the 'final form' of my day-to-day ORM stance: I like to have an ORM around because of the massive automation around entity manipulation, but I think of the ORM in terms of the database and how the database should be structured, rather than as a generic domain model that happens to be mapped to some behind-the-scenes database. Furthermore, I believe it is quite valid to drop down to SQL for the very complicated stuff, especially if you want to trust to the query planning of the database. Once you do, you've broken the total safety of the abstraction, hence my ultimately seeing ORMs as automation tools rather than abstraction interfaces. I have no blame for people who refuse to utilize them, though I'd argue in a design meeting for their use, and hope I wouldn't have to take on all the CRUD queries if I lost the argument, because I'd start writing code to generate them, which then would become a terribly under-engineered faux-ORM :).

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

#143

Post by kbenson »

Advocating for the use of SQL over an ORM in every case is like advocating for the use of Assembly over C in every case.

In both cases, one is a higher level abstraction over the lower level capabilities, which can provide a quite large gain in usability and ability to easily understand what is going on at the level you are working at, for the loss of hand optimizing at a low level to get just what you want in every case.

Similarly to with Assembly and C, you can often drop to the lower level as needed for speed or other very specific needs.

In both cases a good understanding of the lower level language will help you both know when it's appropriate to drop to a lower level for performance or for a special feature, and when it doesn't matter because the ops/SQL generated is rear optimal anyways or the gains are almost definitely less than the problems caused from a maintainability perspective.

I'm perfectly happy to use an ORM for 95% of my DB needs. Just the query builders that generally ship with them are worth the price of their inclusion IMO (at least for the good ones), as it can greatly simplify queries that are variable based on different parameters you may have per run.

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

#144

Post by bob1029 »

hdfbdtbcdg wrote: post #107
bob1029 wrote: post #97

Earlier quoted context omitted.

Care to elaborate on which aspect noted above is adverse from your perspective? I would be happy to provide more context and explain in more detail some of the reasoning involved in our decisions.

> Deserializing a JSON blob to/from a column into/from a model containing 1000+ properties in complex nested hierarchies > complex, rapidly-shifting business models Your business logic classes have 1000+ properties. And you plan to not migrate them when the schema changes but leave many instances with old versions of the schema sitting in the datastore. Your application logic is going to get nasty!

The other aspect here is that the lifetime of these objects is very brief for our application. Typically 10-60 minutes. Schema changes, while breaking, are tolerable along business cycle boundaries.

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

#145

Post by wvenable »

daenz wrote: post #141
wvenable wrote: post #113

Earlier quoted context omitted.

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.

cursor.execute(""" UPDATE employees INNER JOIN merits ON employees.performance = merits.performance SET salary = salary + salary * percentage; """) Please show me how much simpler and fewer lines that would be in ORM code + model definitions.

You would need to show me your DDL statements because that's the equivalent. I can generate the model from the database or the database from the model.

It's not much more effort to type "create table employees" with all the fixings as it is to type "class employees" with all the fixings.

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

#146

Post by ianamartin »

BerislavLopac wrote: post #22

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

That's only a pattern though, and it's not actually coupled to the ORM. It's a convention. It's a bad habit.

No one should do that.

The second pattern is also awful. Why would you do that.

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

#147

Post by neilobremski »

Whether or not you love/hate ORM, I think the advice at the very end is still applicable: "If you’re using an RDBMS, bite the bullet and learn SQL." This knowledge will help you write SQL if that's what you're doing or debug the SQL that the ORM writes for you. Either way ... learning SQL is a good thing!

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

#148

Post by delusional »

wvenable wrote: post #41
daenz wrote: post #32

Earlier quoted context omitted.

If the argument is that it's the right tool for simple jobs, then by definition it won't save a lot of effort.

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.

Yeah, your "uncheckable strings" (which could be checked by spinning up a testing database) is replaced by 700k lines of code that, although tested, is still full of bugs.

The Hibernate repo is 700 thousand lines of java code!

There's a tendency amongst some (especially "enterprise" programmers) to forget that dependencies are also just code. If they break, you are on the hook too. You own that complexity.

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

#149

Post by taffer »

tracker1 wrote: post #121

On a recent project, it was a weird inversion in terms of access... in order to keep the middle tier thin, and meet requirements that all data access happen through stored procedures... we pretty much standardized an interface with one input parameter (@json) and two output parameters (@result, @errorResult). In the end, all input/output was JSON and the database handled all data internally. I don't really like it mu…

I always like to link these articles when the topic comes up:

https://sivers.org/pg

https://www.vertabelo.com/blog/business-logic-in-the-databas...

https://www.martinfowler.com/articles/dblogic.html

There is also this video on how to properly architect these kinds of applications: https://youtu.be/PMPW024NIDE?t=1052

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

#150

Post by Glyptodon »

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…

I've always thought the "being SQL database agnostic" theory of ORMs was more about a development team being able to choose from some common choices than about apps being portable in practice.
Post reply on HN