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

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

#351

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

delusional wrote: post #157

yawaramin wrote: post #99

Earlier quoted context omitted.

> ORMs are hard for a reason. Yes, the object-relational impedance mismatch. It's the classic case of having a hammer (OOP) and trying to make everything look like a nail.

I would actually let OOP off the hook here. I think what did the harm in this case was the java generation. The generation of programmers that were told that in the future they would only have to write the "business logic", and everthing else would just happen. They were taught javabeans, orms, gigantic frameworks. They completely forgot that their code actually needed to execute, and no one cared about their "busine…

I seriously don’t think I have worked with a single developer in 20+ years that didn’t at least know simple sql and joins

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

#352

Post by zmmmmm » Sat, Sep 21, 2019, 2:32 AM UTC

Writing SQL is a lot of work, not type safe (in the programming language static typing sense), often not re-usable, and hard to test. There are libraries that solve these parts for you (or at least help with them). For me the optimum is libraries that do only these parts but actively try to NOT introduce any new layer of abstraction over the database model itself. The problem is that in this whole ORM discussion, the baby gets thrown out with the bathwater.

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

#353

Post by Noumenon72 » Sat, Sep 21, 2019, 2:35 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

Ye. Static typechecking is the only thing in his list that I really care about, since you can "git gud" at SQL and not be bothered by the syntax/ordering/parser concerns. jOOQ is exactly what I want to bridge the gap between Java and the DB.

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

#354

Post by ct520 » Sat, Sep 21, 2019, 2:35 AM UTC

scarface74 wrote: post #338

chii wrote: post #312

Earlier quoted context omitted.

A micro ORM is just an ORM, written well and modularly. It isn't a middle ground - it's choosing to use a well written library. A lot of people conflate ORM's leaking because of poor designed library with ORM being a bad abstraction in general.

One of the most popular Micro ORMs for C# is Dapper which is used by Stack Overflow. There is no real abstraction. You write standard SQL and it maps your recordset to an object. You know exactly what code is running. There are extensions that will take a POCO object and create an insert statement and I believe updates, but where ORMs usually get obtuse and do magic are Selects. It’s hard to generate a suboptimal Ins…

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

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

#355

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

gernak wrote: post #349

wvenable wrote: post #30

Earlier quoted context omitted.

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

Eventually if you’re working with an object oriented language or if you have to convert the result to JSON, you’re going to have to convert one paradigm to another.

Unless you’re programming in assembly, everything you do is being converted into another paradigm that is what every compiler and interpreter does.

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

#356

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

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.

I am not picturing this update taking more than 3 lines of code with SQL either. Here is an update in one of my projects with names changed:

  await client.query(`
    update table set c1 = $1, c2 = $2 
    where id = $3 and userId = $4
    `, [c1, c2, id, userId]);

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

#357

Post by james-mcelwain » Sat, Sep 21, 2019, 2:38 AM UTC

philwelch wrote: post #324

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.

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 proliferate data classes, but that depends on the application.

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

#358

Post by danenania » Sat, Sep 21, 2019, 2:40 AM UTC

GiorgioG wrote: post #335

danenania wrote: post #332

The way I'd put it is: "learn SQL then use an ORM". If you understand exactly what SQL is being generated and what the implications are, you can use an ORM effectively and benefit from it in many ways (readability, security, composability, etc.). You'll also know when to use it and when it's better to just drop down into SQL. It's when people don't really know what the ORM is doing underneath that they get in trouble…

And therein lies the problem. These ORMs (by and large) have become so complex and abstract that it's nearly impossible to understand what they're doing underneath (I'm looking at you Entity Framework (in .NET)) You wind up having to have a deep understanding of the ORM and SQL, at which point I would argue why bother with the ORM at all? For .NET, I'm much happier with a very very thin layer over the base .NET datab…

"You wind up having to have a deep understanding of the ORM and SQL, at which point I would argue why bother with the ORM at all?"

I agree that ORMs are often too complicated, but imho it's still worth it to learn and use one. I'm using ORM loosely though in that I don't think heavy 'Object' and 'Mapping' layers are so important. Mainly you just need a reasonable way to parameterize and compose queries so you can avoid injection and deduplicate logic in a sane way. So I guess my argument is more "use a library" than "use an ORM".

In my experience everyone who says they'll use pure SQL ends up adding gnarly string building logic at some point because the duplication gets out of control. It's better to just find a decent lib that works at whatever level of abstraction you're comfortable with and use that.

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

#359

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

falcolas wrote: post #155

timdev2 wrote: post #119

Earlier quoted context omitted.

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

> I haven't heard anyone talk seriously about database-agnosticism since the very early 2000s. Do you use the same database engine for your unit and integration testing as you do production? I don't. I use sqlite for unit and local integration testing, and aurora-mysql for production. As a side note, I quite literally can't use aurora-mysql for local unit and integration testing. It doesn't exist outside AWS.

There is no syntactical difference between Aurora/MySQL and regular MySQL until you start interacting with S3.

Also if you’re using only the subset of MySQL that is supported by SQLite, you’re missing out on some real optimizations for bulk data loads like “insert into ignore...” and “insert...on duplicate key update...”. Besides that, the behavior of certain queries/data types/constraints are inconsistent between MySQL and every other database in existence.

Finally, you can’t really do performance testing across databases.

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

#360

Post by mamcx » Sat, Sep 21, 2019, 2:45 AM UTC

setr wrote: post #255

mapgrep wrote: post #205

Earlier quoted context omitted.

Ya I’ve heard this one a lot. It’s kind of funny to say and does humorously underline the complexity of the problem but people take it seriously. So to take it seriously for a second: There was no good reason to be in Vietnam; even taking the stated rationale as a given, which many people did not, it was a concern many levels removed from the actual safety or functioning of American society. ORMs in contrast achieve…

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…

> What we really need is a less shitty version of SQL.

I think the same. In my spared time I building a relational language (http://tablam.org ..accept more help!) starting even lower: Without a rdbms.

I work in the past with FoxPro, and was possible to build a full app with UI and reports and all stuff you can imagine with a database-oriented language. You code the UI on fox, query with fox, make triggers with fox, etc...

I'm looking in capture the same essence.