Comment by ekidd | Hacker News Reader

Comment by ekidd | Hacker News Reader

The post specifically complains about ActiveRecord, and claims that the abstraction of ORM breaks down not for 20% of projects, but close to 100% of them.

This certainly doesn't match with my experience, and I've worked on dozens of projects using ActiveRecord. Some of these were hairy production projects with strict performance requirements and complicated schemas.

About 90% of the time, the ORM does the right thing. It creates, updates and deletes individual records, and it generates entirely reasonable queries. And it does this without requiring dozens or hundreds of lines of SQL.

You'll get much better performance out of ActiveRecord if you know a few tricks, however:

- Read the SQL queries in development.log to make sure they're sane, and use New Relic to find slow queries in production.

- Use 'include' to load a model and its associated objects in 2 queries instead of 1+N queries.

- Use 'select' to load only the necessary columns from your tables.

- Don't try to force it: If you hit something weird, never hesitate to fall back to SQL. ActiveRecord provides a dozen different places where you can inject small SQL fragments into your queries, or you can just call 'find_by_sql' and do everything by hand.

If you start out with basic ORM models, benchmark your database, and add some raw SQL as you scale, you can get reasonable rcesults with ActiveRecord. If it breaks down for 100% of projects, you either have a weird definition of "breaks down", or you're doing it wrong.

What you just describe is exactly what he defines as breaking down. You may not consider this a big deal but he is right that instead of just learning SQL or an ORM you have to learn/use both. The abstraction does break down somewhere for close to 100% of projects in my own experience, usually for exactly the reasons he is describing. Saying it's no big deal doesn't change the fact that each of the points he makes in his post do indeed happen.

Now I'll grant you that this may not personally bug you that much. It bugs me quite a bit though.

If you use a relational database and believe you don't need to know sql, you're delusional.

If you are using a relational database, know sql, and want to eliminate a TON of boilerplate, use an ORM.

I don't know of many ORM's that claim "Use us and you won't have to know SQL". That's just a straw man argument.

If you define breaking down in the terms mentioned by the comment you are replying to, you are playing a semantics game to frame the debate to be: this ORM sucks because I can't use it without knowing sql.

no. I define it thus. "If I already know SQL why should I have to learn this ORM?" SQL already works and does what I need. What does an orm give me that makes the headache of figuring out when I need to shift to naked SQL worth it? Why try to hide the SQL when its just going to leak out to me in non obvious ways later anyway. That is the authors point as well I think.

As the parent wrote, an ORM allows you to eliminate a TON of boilerplate.

Since most ORMs I've worked with are not really as complicated to learn as you try to make it sound, the trade-off is clear.

EDIT: fixed a typo.

I'm really not trying to flame here, so bear with me. :)

It's just that in software, the argument "If I already know A, so why should I have to learn B" isn't really useful. Given that software is often about pushing down complexity, one of our main tools is building a "B" so that we don't have to do "A".

I know how to write socket code in C, but I'm glad for libraries that don't force me to think about that level--I can pop up a level or two. Examples continue ad infinitum.

On the other hand, I can buy the argument "B isn't really very good at abstracting A; I've tried to use B, and every time I use B, I end up just doing A anyway. So B isn't useful."

But with ORMs, my experience isn't so black-and-white. I find them very useful a lot of the time. When I run into problems, I can switch to doing SQL directly. That's an okay tradeoff for me. I can certainly understand if others don't find that tradeoff as useful.

Doesn't look like a flame to me :-)

My point was more B doesn't in my experience abstract A well enough to justify its use in anything other than toy or really simple apps that won't live very long or won't have much maintenance. I don't mind an upfront cost if it means I won't be pulling my hair out at 2am trying to make some database call take less then 10 minutes to complete. (Exaggerating there but you get the idea.)

If B doesn't do a great job abstracting A then the time spent learning B feels wasted to me.

(Edit : extrapolate to abstract)

"What does an orm give me that makes the headache of figuring out when I need to shift to naked SQL worth it?"

ORM has lots of benefits. but having to learn less is not one of them. Biggest one is that you don't need to write/maintain a separate code base (sql) for CRUD operations. that's BIG benefit even if you get to use it on 40% of your database interactions. Something as stupid as not worrying about creating the right data conversions and logical types to store data in, will avoid a ton of bugs from intermediate programmers.

This article was posted ages ago.

No one is forcing you to do anything, you can write your own database access layer if you like. Often developers who do this end up using parts of their code across different projects, basically they write their own ORM.

Or you can use a pre-built ORM, as far as I am concerned for me this is the saner choice. I really doubt I could write something on my lonesome that is as secure, performant and has the features of ActiveRecord in a non trivial amount of time.

But the important part is that whichever path you go down, if your project is growing, complex or needs to scale, you need to know your stack. You need to be able to modify or reconfigure each part. You need to understand at a high level what each part of the stack does, and be able to learn exactly what it does at a low level quickly should the need arise.

The answer is: because you often want ONE PLACE through which queries are sent, and you often want this place to understand the STRUCTURE of the query, in order to do things like sharding, for example.

Sometimes, this one place is actual a MySQL proxy. Check out ScaleDB, which they just launched - it looks promising.

But other times, it will be in your own app. That is the ORM :) But in that case I wonder how often you really need relational data stores.

You will actually arrive at some sort of ORM if you start abstracting your CRUD operations into ONE PLACE. Basically ORM is the product of DRY.

That "place" is called the query optimizer, there's one in every RDBMS. We've done what you call "sharding" (partitioning) that way since the 1990s.

mysql query optimizer does SHARDING?

and it's not a bottleneck? it's shared nothing?

You're not just eliminating a ton of boilerplate, you're also eliminating set oriented thinking, which is key to working with relational databases effectively and efficiently.

I find ORMs useful for simple CRUD but that's so little of what I do that I mostly don't bother adding a huge framework to my code just to do some CRUD.

A set oriented DB API that cuts down on boilerplate is the way to go in my view. And that is simple enough to create on a weekend.

That's just not true, though. Most ORM's I've used use the set as the primitive element. You declare your schema in terms of sets with relationships. Then you join on relationships. So if you want to find all artists whose name contains 'foo' that have released an album that starts with bar, you would write $albums->search({ name => { -like => 'bar%' } })->search_related('artists', { name => { -like => '%foo%' } }). This looks a lot like the SQL that you would write for the query, except that you don't have to write it and marshal the results back to some format that your application understands. And, the object you get back isn't a set of rows from the database, it's a description of what query you want to run. So you can pass this object around and further restrict or expand the set of results you'll get back when you actually start asking for row data.

This works out very nicely for things like web applications where the currently-logged-in-user can only manipulate his own data. You can write your model so that it takes the current user and restricts all data to his rows so that your application doesn't even see data that the user is not allowed to see. It's really quite wonderful and something that raw SQL queries simply don't provide enough abstraction to handle flexibly.

Ultimately, people dislike ORMs for the same reason they dislike things like CoffeeScript. They're not mentally comfortable delegating code generation to a computer program -- that's their job. And so, they will complain whenever they are looking at code that's not exactly what the lower level of the system will see. But in general, that's due to inexperience rather than actual dislike.

I used ORMs for a long time and I have written my own many years ago, so I understand them well and I have learned to dislike them for most tasks. But I should have been more specific in what I mean by set oriented.

The point of working with sets of tuples is that you can apply any set operation to a set of tuples and what you get is a set of tuples. You can use selection, projection or union and you still get a set of tuples. The recursive nature of this principle makes it so powerful and simple. Kind of like Lisp.

ORMs introduce a conceptual barrier between objects defined by domain classes created at design time and generic containers used to hold the result of projections and aggregate functions. If you join over two classes and project over some of their attributes you lose all the properties of the original classes, including any attribute accessor methods. Same thing with aggregate functions and grouping.

Even if the result of a query involving projections or aggregate functions happens to have the exact same attributes as another existing class, you don't get objects of that class and hence none of its methods. The tuples don't compare equal to objects of that class either because they don't share their type.

Different ORMs have various features to partially paper over these issues. I don't want to get caught up in a debate about what ORMs can or cannot do because powerful ORMs like hibernate can do almost anything and everything is optional.

The point is that they discourage me from working in a set oriented fashion because they make it hugely more complex than it needs to be. They create a conceptual mess and then they want me to learn a lot of tuning techniques to fix what is broken as a result of that mess. It makes no sense to me.

There's more to database programming than just concocting queries. Once you've built a query you want, you have to run it and handle the results somehow. Most object-oriented programs are going to use an object for this. So it makes sense to just write your whole program in terms of set operations on top of predefined objects which map pretty nicely to the database's idea of objects.

You are confirming what I said originally. What you call "handle the result" is quite frequently something that can be done in a set oriented fashion using orders of magnitude less code.

But what ORMs encourage you to do is to load a bunch of objects and then use procedural code to do the real thing.

I fully understand that not all types of algorithms and datastructures lend themselves to set oriented thinking. I work mostly with those nowadays. But for those cases it makes no sense to use RDBMS at all.

  But what ORMs encourage you to do is to load a bunch of 
  objects and then use procedural code to do the real thing.

The exact same thing happens when you directly express the query in SQL. You load a bunch of objects and post-process them, to do what SQL can't do. Unless you count the various languages you can use in stored procedures in RDBMS's, but those aren't set-oriented SQL either.

If I want to do something that SQL can't do I don't use SQL. Using SQL is not an end in itself.

My rule of thumb is pretty simple. I use whatever takes fewer and/or simpler lines of code unless it's a lot slower.

The raging scalability debate is a different matter. I'm sure if you're Google there are good reasons to write more lines of code in order to scale better. I'm not Google so I can prioritize productivity over scalability.

The current Ruby ActiveRecord is based on Arel, which is a relational algebra. Everything in Arel is necessarily set-oriented and the results of expressions in the ActiveRecord ORM language are the exact objects that went into the set, as long as they are truly set operations.

  Same thing with aggregate functions and grouping.

Why do you expect the resulting object of an aggregate over source objects to have the same kinds of methods? That makes no sense at all. What should the 'name' of a record return when you've aggregated over the salary of many employees? That makes no sense in the original relational algebra, whether expressed in SQL or ORM, either. Aggregating is not a set operation and there part of the false dichotomy comes in: SQL isn't quite as pure and easy to understand as ORM opponents would have you believe.

Exactly, encapsulation as a combination of data and methods makes no sense if data is transformed, joined in different ways, grouped, etc. Some methods have to become invalid. Others, like accessors, may still be logically valid but are lost because they are linked inextricably to a class type even though they may depend only on the attribute value itself.

The goal of a query is not to process sets of objects. The goal is to answer a question. The ORM gives you two choices. Either limit yourself to answers that can be expressed as objects of existing classes or get something that is fundamentally different from all other domain objects - a generic tuple of values.

You lose the homogenous, recursive transformation capability that the relational model provides.

In terms of simplicity, I suggest you take a few queries involving joins, group by, having and aggregates and translate them to procedural code. I've done that a lot. It's very eye opening. Functional languages using homogenous data structures like lists or generalized sequences can be similarily productive for data that fits into memory. That is a more and more viable alternative to SQL in my view, but OO systsms and querying/transformation just clash badly.

> Ultimately, people dislike ORMs for the same reason they dislike things like CoffeeScript

This is not a good comparison. With ORM, you started with Python/Java/C#/etc + SQL, and ended with Python/Java/C#/etc + SQL (a lot less). There is nothing new added.

With CoffeeScript, you started with JavaScript, and ended with JavaScript + a perlish language.

There is the problem for me. ORMs (well maybe Entity Framework with LINQ) don't attempt to be an abstraction. They are more like sprintf. Knowing sprintf doesn't free you from knowing what numbers look like as strings. ORMs don't free you from knowing SQL. They free you from having to write some tedious shelping rows to objects.

Although he did specifically call out Ruby on Rails I rather think he meant ActiveRecord the pattern [0] rather than ActiveRecord the implementation [1]. Ruby on Rails' ActiveRecord has come a long way and actually implements proper relational algebra through Arel [2] now.

[0] http://martinfowler.com/eaaCatalog/activeRecord.html

[1] http://ar.rubyonrails.org/classes/ActiveRecord/Base.html

[2] https://github.com/nkallen/arel

The alternative and more flexible approach is to use a software package with the Data Mapper pattern. It differs in that one is no longer tightly coupled to the actual database tables. Instead, you can create whatever views of the data make sense to your application. Even though it seems like a higher level of abstraction, you actually have greater access to all the features SQL provides, almost in the form of an SQL builder.

One example of the Data Mapper pattern is in python's SQLAlchemy. Another is in perl's DBIx::ObjectMapper.

"Don't try to force it: If you hit something weird, never hesitate to fall back to SQL. ActiveRecord provides a dozen different places where you can inject small SQL fragments into your queries, or you can just call 'find_by_sql' and do everything by hand."

I'm comfortable working like that, but it is exactly what the article is criticizing. Sure, it works - but the whole idea of an ORM was supposed to be avoding that.

I believe ORMs want to save you from writing tons of repetitive queries, not to save you from ever having to touch a line of SQL.

At least that is what I believe, I have never seen an ORM advertising itself with "NEVER WRITE ANOTHER LINE OF SQL AGAIN".

No, the idea behind an ORM is to make the common case easy and safe.

"But the whole idea of an ORM was supposed to be avoding that."

Maybe some ORM zealots said that in the past, but that certainly isn't the common conception now. ORMs as they're actually used properly today allow devs who know SQL really well to eliminate code dup. If you can't drop into SQL quickly, stay away from ORMs, you're going to hurt yourself and others.

Reply on news.ycombinator.com