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)

woz.posthaven.com

Post reply on HN
Print view View original

71–80 of 360 posts

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

#71

Post by niftich »

hamburglar wrote: post #64
nine_k wrote: post #19

Object -relational mapping is in many cases an excessively leaky abstraction. I try keep away from it. A DSL for writing SQL in a nice, composable way is a useful thing. Some libraries, like SQLAlchemy, provide both levels, not insisting on using the object mapper.

The DSL for your database is called SQL.

This is a quip that misses the parent's point. SQL isn't very composable, because its syntax requires infix notation, position-dependent phrases, separators, and the like. The abstract syntax tree of SQL is much more valuable than its syntax, which is essentially just a bad English serialization that resembles a natural language sentence.

A DSL which manipulates queries and then produces syntactically valid SQL is tremendously valuable.

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

#72

Post by Someone1234 »

bloaf wrote: post #32
Someone1234 wrote: post #20

Earlier quoted context omitted.

LINQ isn't a ORM. I assume you mean Entity Framework? EF definitely has the foreign key issue. We have around a thousand tables, we tried to generate the classes for all of them including foreign keys, problem is that when you create a context that references even only a single table, it will load everything that is foreign keyed including siblings of siblings of siblings, until you run out of memory. Only way around…

Wait, it ran out of memory because it couldn't hold the class structure?

Essentially yes. It created giant object map with millions of objects in it and hit a memory limit (rightfully so, it wouldn't have been usable anyway). It would start at the initial table, get the siblings, then the siblings of the siblings, and so on until it was trying to generate an object for every property of almost every table we had (even if we only referenced the original table).

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

#73

Post by 3riverdev »

(Bias: I'm one of the Hibernate ORM committers.)

Hibernate (and presumably any ORM) was never intended to be a complete abstraction of anything-SQL. Like others have mentioned here, an understanding of SQL must be had before using an ORM. The ORM is one piece to the puzzle, not a shield to prevent you from having to touch SQL.

One pattern I typically use is a take on CQRS: Hibernate for writing/updating/fetching/deleting a single instance of deeply-relational object model, SQL (I like jOOQ) for larger-scale fetches and any bulk actions.

Shameless plug for a write-up I put together last year: https://www.3riverdev.com/hibernate-orm-jooq-hikaricp-transa...

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

#74

Post by exclusiv »

alexandercrohde wrote: post #61
zzzeek wrote: post #43

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating q…

I think there are 3 main reasons ORMs came into common use: 1. As a reaction to common SQL injection from poor libraries not implementing parameterized queries. (2004 or so) 2. Novice engineers not wanting to learn SQL (look I learned how to make a blog in RoR, and I like mongo!) 3. As a theoretical abstraction above the data-store (as though you might someday be able to switch the data-store beneath the ORM) 1 has b…

I'd add #4 - it's just generally faster and more enjoyable to develop with a good ORM.

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

#75

Post by cryptonector »

Some people, when faced with a database problem, choose to use an ORM on top of an RDBMS. Those people now have three problems.

The problems are: the original problem, the expressive and performance disaster that is every ORM ever, and the layered and hidden RDBMS whose peculiarities nonetheless always find a way to leak through the ORM abstraction.

Fun stuff.

Do what TFA says: just learn SQL.

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

#76

Post by orf »

alexandercrohde wrote: post #61
zzzeek wrote: post #43

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating q…

I think there are 3 main reasons ORMs came into common use: 1. As a reaction to common SQL injection from poor libraries not implementing parameterized queries. (2004 or so) 2. Novice engineers not wanting to learn SQL (look I learned how to make a blog in RoR, and I like mongo!) 3. As a theoretical abstraction above the data-store (as though you might someday be able to switch the data-store beneath the ORM) 1 has b…

For 3: if you want to distribute a library or service and have no control over the data source.

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

#77

Post by jeswin »

eru wrote: post #70
jeswin wrote: post #57

The post is from 2014, so I won't be too harsh here. The author's problem is with some specific flavors of ORM he's used, and shouldn't be generalized. Hibernate's expressiveness is/was crippled by Java itself. C# ORMs on the other hand are way better because they benefit from LINQ which adds queries natively into the language. Other more expressive languages have excellent ORMs as well. The objective of ORMs is not…

Is LINQ actually an ORM? It looks like a DSL to do relational stuff in C#, but I don't see where the 'O' part of ORM fits in especially not in your examples to show off LINQ's power and convenience.

LINQ is not an ORM. My examples were showing easy, lazy evaluation. My C# is rusty, but the examples mostly hold I think.

The following gets executed on iteration (of expensiveOrders), the lambda is compiled into a Function and run on each item in orders:

  IEnumerable orders = ...;
  const expensiveOrders = orders.Where(o => o.total > 100)
The following gets compiled into an expression tree, which an ORM can analyze and convert to SQL. Basically it's just "Code as Data".

  IQueryable orders = ...;
  const expensiveOrders = orders.Where(o => o.total > 100)
The language's ability to treat code as data allows the programmer to express queries in native language syntax and pass it to an ORM (such as EF or Linq to Sql) for execution on a data store.

Add: Where does the 'O' part fit in? You build a entities (in plain C#) with relationships to each other, and you could do stuff like:

  //Pseudo-code
  db.Customers.Where(c => ...)
  db.Save(customer);
Edit: clarified - executed on iteration, not immediately.

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

#78

Post by yorwba »

eru wrote: post #65
alexandercrohde wrote: post #61

Earlier quoted context omitted.

I think there are 3 main reasons ORMs came into common use: 1. As a reaction to common SQL injection from poor libraries not implementing parameterized queries. (2004 or so) 2. Novice engineers not wanting to learn SQL (look I learned how to make a blog in RoR, and I like mongo!) 3. As a theoretical abstraction above the data-store (as though you might someday be able to switch the data-store beneath the ORM) 1 has b…

For 3: the proper abstraction for relational data is, surprise, a relation. I used to work in an environment where we had relations as full first class data structures. They are very pleasant to work with. We actually had 'relation-object-mappers', ie when we had to interact with other systems that didn't use relations, we often mapped them to relations internally to make them play nicer.

How were those relations represented? If I understand correctly, you were not just wrapping tables in a database, but using some other backing implementation. What were the most common operations, and what was the performance like?

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

#79

Post by erikpukinskis »

zzzeek wrote: post #43

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating q…

Ok, let’s break this down:

> Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks

None of that requires an ORM. A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM.

> providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL

So this is the real ORM juice. And ya, without an ORM you have to do this by hand.

So here’s the question: How well can an ORM map to your data model out of the box? In a lot of cases this is where the mess comes from. You need to set up basically a low level AI that can figure out the “right” thing to do as call sites all over your codebase are making arbitrary queries on a huge API surface.

And so the argument against ORMs is that it will take less time and be less error-prone to write bespoke loaders that take rows and build your data model than it will be to configure that AI such that it can do the “right” thing with arbitrary queries.

(If you don’t like the word “AI” here, use “expert system” instead.)

Post reply on HN