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

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

#341

Post by Avamander » Sat, Sep 21, 2019, 2:17 AM UTC

tomtomtom777 wrote: post #236

725686 wrote: post #37

Each time I see someone complain about ORMs I remember Greenspun's tenth rule[1], which adapted to ORM would be: "Any sufficiently complicated program contains an ad-hoc, informally-specified, bug-ridden, slow implementation of half of a decent ORM." ORMs are hard for a reason. Using an ORM doesn't mean you can't or shouldn't use plain SQL where the situation calls for it. You can mix and match perfectly fine. [1] ht…

What you are saying exactly highlights what the author is missing: That if your application has some logic, you will eventually have to map your database rows to your in memory typed structures. It sucks. Sometimes it sucks less if you map your queries as well, sometimes it sucks less if you stick to SQL and only map your results, sometimes it sucks so much you're better off with a no-sql solution. But when using a r…

Um, PL/PGSQL and et al for stored procedures exist, OOP is optional.

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

#342

Post by dymk » Sat, Sep 21, 2019, 2:19 AM UTC

rmilejczz wrote: post #299

dymk wrote: post #246

Earlier quoted context omitted.

But you shouldn’t live with a hand-rolled pseudo ORM that stumbled into existence when there’s developed alternatives

An in house solution is almost always better than an external dependency

Opposite; an in-house solution is almost always worse than an external dependency when that dependency is something as important to get right as an ORM.

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

#343

Post by scarface74 » Sat, Sep 21, 2019, 2:20 AM UTC

nullspace wrote: post #303

rmilejczz wrote: post #299

Earlier quoted context omitted.

An in house solution is almost always better than an external dependency

Hah... I’m probably falling for Poe’s law here, but anyways... there are certainly cases where in-house is better than external dependency - specifically when your team knows the tech domain better than anyone external can... but in general well-maintained (preferably open source with a community, or a well funded company) external dependencies are almost always better. They usually would have the years of fixing edg…

There is hardly ever a time where an in house solution is better than a third party one for cross cutting concerns. Most of the packages are open source.

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

#344

Post by misterdoubt » Sat, Sep 21, 2019, 2:20 AM UTC

mst wrote: post #290

setr wrote: post #255

Earlier quoted context omitted.

Tbh you could easily claim that the explicit goal, mapping to objects, is incorrect. The real value is to reduce the damage of the SQL language itself — the unnecessarily ordered clauses, the arbitrary inconsistencies in syntax, the worthless parser errors, the lack of any static typechecking — which cause so much code bloat and debug headaches. There are two reasons to use the ORM: to not learn SQL, and to generate…

An ORMish thing that considers its primary purpose to enable metaprogramming SQL is actually useful. In http://p3rl.org/DBIx::Class perl has had such a thing for over a decade now. It makes me cry that nobody's ever adequately cloned it into other languages. Eventually I'll probably do so myself.

I guess I don't know enough Perl to understand how this differs dramatically from something like SQLAlchemy in Python.

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

#345

Post by jtdev » Sat, Sep 21, 2019, 2:22 AM UTC

SQL is to much software development what rivets, bolts, or even beams are to the construction of skyscrapers... if you’re going to be in the software business, you should really get comfortable with SQL, after which you’ll likely find the friction and headaches caused by ORMs to be a deal breaker.

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

#346

Post by jillesvangurp » Sat, Sep 21, 2019, 2:23 AM UTC

iamleppert wrote: post #114

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

I've ripped out broken ORM on multiple projects with over-engineered domain models designed by people with no apparent knowledge of how to do a proper database design. This is the key problem with ORM. It leads to lots of unnecessary joins just so you can pretend databases do inheritance or all those tiny objects you will never query on need dedicated tables with indexed columns. It's stupid. It's also stupidly slow, fragile, and hard to maintain such systems.

I've been on a project where we had 20+ tables. After I made the point that the sole purpose of this database was producing json documents through expensive joins that were indexed and searched in Elasticsearch (i.e. this was a simple document db), we simplified it to a handful of tables with basically an id and a json blob; got rid of most of the joins and vastly simplified the process of updating all this with simple transactions and indexing this to elasticsearch with a minimum of joins and selects.

We also ripped out an extremely hard to maintain admin tool that was so tightly coupled to the database that any change to the domain made it more complicated and hard to use because the full madness of the database complexity basically leaked through in the UI.

ORMs don't have to be a problem but they nudge people into doing very sub optimal things. When the domain is simple, the interaction with the database should be simple as well. We're talking a handful of selects and joins and simple insert/update/delete statements for CRUD operations. Writing this manually is tedious but something you do only once on a project. With modern frameworks, you don't end up with more lines of code than you'd generate with an orm. All those silly annotations you litter all over the place to say "this field is also a column" or "this class is really a table" get condensed in nice SQL one liners that are easy to write, test, and maintain.

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

#347

Post by scarface74 » Sat, Sep 21, 2019, 2:27 AM UTC

sjwright wrote: post #233

stickfigure wrote: post #208

Beginning programmer: ORMs let me write code without learning SQL! Intermediate programmer: ORMs just get in the way! SQL isn't that hard after all. Advanced programmer: I write a lot of SQL, but I use ORMs to cut out most of the boilerplate.

Even more advanced programmer: writing the ‘boilerplate’ queries out manually takes barely more time than composing them in an ORM, means less indirection, saves me a major dependency, and encourages me to think intelligently about each query no matter how boilerplate they might seem at the surface. Super-advanced programmer: allowing my database structure to be influenced by the needs of an off-the-shelf ORM will ma…

Until you have a grid with a filter in your UI where the user can create a dozen different queries on the fly....

Why does your database structure have to be structured by your ORM?

The only ORM that I have used are LINQ based ones and they can model any database relationship.

Don’t get me wrong, my first instinct when starting a project is to use Dapper - a Micro ORM written by Stack Overflow that just maps a sql query result to object and doesn’t generate sql.

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

#348

Post by rhacker » Sat, Sep 21, 2019, 2:28 AM UTC

wvenable wrote: post #30

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

You're going to love this: about the time I left one place I worked one top engineer did a meeting on using Hibernate for... drum roll... analytic queries. My brain fried with questions of why, how is this better, and this is definitely the result of someone that hasn't touched real SQL in a while and just wanted to use the same hammer for everything.

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

#349

Post by gernak » Sat, Sep 21, 2019, 2:28 AM UTC

wvenable wrote: post #30

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

>You can (and should) use them for simple queries.

This is not a very compelling argument to use ORMs. It is saying "it makes easy things easier". This doesn't really buy you much value. The simple things are already simple. Bringing in a very large, complicated external dependency to make simple things simpler, is not a good idea.

>If you're loading data into objects then you're just creating your own personal ORM anyway.

By this definition, the Postgres driver I use for node is an ORM as it takes a row of string/type identification codes and turns them into javascript objects and javascript types. It isn't an ORM though, that is not what an ORM does. An ORM converts one paradigm into a completely different paradigm, which is why it fails and is a terrible idea.

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

#350

Post by SmirkingRevenge » Sat, Sep 21, 2019, 2:29 AM UTC

stickfigure wrote: post #208

Beginning programmer: ORMs let me write code without learning SQL! Intermediate programmer: ORMs just get in the way! SQL isn't that hard after all. Advanced programmer: I write a lot of SQL, but I use ORMs to cut out most of the boilerplate.

There are less drastic ways to reduce SQL boilerplate, though - such as simple SQL dialect implementations, in your language of choice. I'd almost always prefer that kind of interface, when there's a decent one to use.

Of course, your database may come with features that can help too (views, udfs, etc)

I'd generally only reach for an ORM in one circumstance - when my team already knows it well, and can move fast with it. It should also be popular, so that its likely new team members already know it, and can move fast with it. Otherwise, you're just putting unnecessary obstacles in front of your team, in most cases.