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

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

#471

Post by spion » Sat, Sep 21, 2019, 7:28 AM UTC

jasonkester wrote: post #450

I don't know. I've been building database backed stuff for 25 odd years now, and I've never experienced this Object/Relational Impedance Mismatch that everybody talks about in any of my designs. I sometimes wonder if it's just the approach I take that has ended up dodging that bullet somehow. My initial design is always done in the database. Whether it's a little feature or a green field new project on a blank sheet…

"impedance mismatch" I believe typically refers to the following:

* the fact that the records you can create with SQL are an unbounded combination of every field in every table; in addition, any aggregate or functions applied as well as any renamed fields will further enrich the set of row classes you are able to generate.

* the fact that with (standard) SQL you cannot create anything other than lists of records, whereas objects are directed cyclic graphs

* the fact that objects assume unbounded access to the said directed cyclic graph as if it were in memory, which is a mismatch with optimal SQL querying patterns (the n+1 queries problem)

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

#472

Post by elsjaako » Sat, Sep 21, 2019, 7:40 AM UTC

inimino wrote: post #380

rmilejczz wrote: post #299

Earlier quoted context omitted.

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

This is correct. An in-house solution is a solution developed in-house for your specific problem , which no one else has ever had exactly. The more specific the need, the more the benefit of the made-to-measure solution. The alternatives are something your organization didn't develop, which may be better, but you don't know how to use it, or may be worse, but you don't know that when you pick it, or may be slower, bu…

Isn't this an argument against using any library at all?

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

#473

Post by Too » Sat, Sep 21, 2019, 7:41 AM UTC

noisem4ker wrote: post #259

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…

Something like jOOQ? https://www.jooq.org

That looks fantastic. No magic mumbo jumbo mapping, just a simple type safe sql. Both syntax safety (no need to remember which of WHERE and HAVING comes first) and type safety on all fields. It's not advertised in the examples on the front page but i also take for granted sql injections are completely impossible since all data goes into functions and are not string formatted, without the mess of having to remember the order of arguments as with prepared statements.

Anyone got tips on similar frameworks for other languages than java and for other dbs.

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

#474

Post by hackerbabz » Sat, Sep 21, 2019, 7:43 AM UTC

Is it so hard to say ORMs are great for 90% of grunt work but sometimes you need to write your own SQL?

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

#475

Post by timmy-turner » Sat, Sep 21, 2019, 7:55 AM UTC

gmac wrote: post #467

kccqzy wrote: post #439

Earlier quoted context omitted.

There is a big difference between just writing helper functions to construct SQL and convert data types, and OO-style magical auto-persisted objects. The latter is what I don't like about ORMs but the former is fine. I feel that this is an important distinction to make. As an example, the sqlalchemy docs[0] make this very clear: there's an ORM, but there's also just a core expression library that simply helps you con…

Agreed. Helpers (and indeed types) can make working with SQL an actual pleasure. You do need to learn the SQL, though. (My TypeScript/Postgres solution, in this vein: https://github.com/jawj/mostly-ormless/blob/master/README.md ).

Wow this is great! Very well written README.

What just blew me away is the thing with the `JOIN` and the `to_jsonb(authors)`, all with complete typing support for the nested author object. I was actually looking to use a classical, attribute driven query generator (with the sort of chaining API everyone is used to: `tableName.select(...coumns)` etc.) for my next project involving to maybe replace/wrap/rewrite a Rails app and its ORM with Typescript and Node. Maybe I'm trying this instead I'm already half sold. Just worried about forcing colleagues having to learn SQL instead of using a fancy wrapper.

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

#476

Post by gernak_ » Sat, Sep 21, 2019, 7:59 AM UTC

scarface74 wrote: post #429

gernak wrote: post #394

Earlier quoted context omitted.

You can only use a fraction of the features of a SQL database when you use an ORM because they don't translate to the new paradigm. When I am using a high level programming language, it is merely helping me do things like manage memory. I am not constantly wishing I could drop down and work with pointers and so on. It is a foundational paradigm that builds on the top of the one before it. ORMs just present a differen…

There are plenty of scenarios in certain verticals where you need to control memory management like games and others where you need to program in assembly. Garbage collection is an incompatible paradigm where you need to control when memory is allocated and freed. Back in the day when I was doing C, there were times when we just couldn’t get the speed we needed from the compiler. I wrote inline assembly. Does that me…

An ORM is not a high-level version of SQL though. A more accurate metaphor for an ORM would be like a converter from one high level programming language that is object oriented, to another high level programming language that is functional.

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

#477

Post by yegor256a » Sat, Sep 21, 2019, 7:59 AM UTC

You may find this relevant: https://www.yegor256.com/2014/12/01/orm-offensive-anti-patte...

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

#478

Post by philwelch » Sat, Sep 21, 2019, 8:00 AM UTC

james-mcelwain wrote: post #357

philwelch wrote: post #324

Earlier quoted context omitted.

Let's talk about how this works in reality. In ActiveRecord, there's a method called find_by_sql. You can't call it directly; it's a class method on an ActiveRecord model. So you have to choose which of your ActiveRecord models should be used to instantiate the rows of your result set. (What if your result set doesn't really match any of your models? Pick one arbitrarily.) Your SQL has some extra columns. What happen…

> What if your result set doesn't really match any of your models? Pick one arbitrarily. I don't want to sound like the ORM defender, but I'm not sure I understand. This sounds like a deficiency of Ruby and the ActiveRecord record model. In Java, for example, you'd just write a new POJO for your query, which isn't exactly difficult. There are no "smart methods" or whatever. It is a valid criticism that this can proli…

I was writing in terms of what I actually understand and have used—which doesn’t include any Java ORM. In fact if there are Java ORMs that consist solely of POJOs which are populated by raw SQL queries, I would gladly use them!

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

#479

Post by Rapzid » Sat, Sep 21, 2019, 8:01 AM UTC

Trisell wrote: post #407

ct520 wrote: post #354

Earlier quoted context omitted.

So.. pattern I see emerging. Use orm for the common stuff and execute sql for complicated queries (like reports)

That’s how I’ve done it on my last two projects. We used TypeORM for the standard repeated simple queries, and then wrote custom SQL for our complicated queries that the ORM failed at and then just executed them with the ORM. It was really nice and made for easier table refactors because we didn’t have to go through and audit every query that was calling that table.

TypeORM is a step in the right direction for JS ORMs bit it's like 1/8th of the way there IMHO. A nearly fully typed ORM is possible now with Typescript and of course proxy's are out now.. TypeORM was doing too much ADHOC string building under the covers as well. I believe a SQL AST is the way to go. It can be transformed and compiled to database specific SQL allowing for things like predicate push down, optimization, and a sane way to implement db specific optimizations and extensions.

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

#480

Post by sword_smith » Sat, Sep 21, 2019, 8:03 AM UTC

I use Entity Framework for C# and I have grown to appreciate it. I get libraries for in-memory databases which makes it easy to write thorough unit tests, the `Include` function uses join to include foreign key objects in an optimal manner, and scaffolding tools make it easy to map from SQL to C# classes. The resulting SQL from the Linq expressions is logged which makes it easy to see what is going on, if you already know SQL. The only problem is that I think the writeback to the database of manipulated or new objects is not clear but that might just be because I haven't read the documentation for that thoroughly.