News for hackers. Stuff that matters.
Re:OrmHate by RyanMcGreal on Tuesday June 26, 2012 @10:28AM (#4161979)
> There is a lot of truth to these charges, but such charges miss a vital piece of context. The object/relational mapping problem is hard.

I suspect that context is precisely what underlies the common critiques of ORMs. Those people who best understand the inherent object-relational impedance mismatch tend to be the very people who conclude that the effort isn't worth it.

In my admittedly anecdotal experience, I have found that ORMs are the most useful for the most trivial queries. For anything complicated, I find it easier to drop into SQL and write the query directly than to compose an equivalent object using the ORM - and that becomes increasingly so as the query gets more complicated.

Re:OrmHate by davesims on Tuesday June 26, 2012 @11:09AM (#4162262)
> In my admittedly anecdotal experience, I have found that ORMs are the most useful for the most trivial queries.

True, but 'trivial queries' generally cover the majority of use cases within your average CRUD application, and that's exactly why ORMs are useful -- you don't end up re-typing 'select bar from fu where id = 1' or 'select * from fu, bar where fu.bar_id = bar.id' etc., etc.

Beyond that, most of the common joins are trivial as well, and can be abstracted away, and a lot of those queries end up being optimized according to known SQL patterns for the common use cases, so you can avoid n+1 and so forth. For these work-a-day queries, ORMs work just fine.

That said, there are tons of situations where ORMs make no sense at all, like complex migrations, large OLAP-style analytic queries, etc. For these, it's important to keep your SQL chops up and know the pitfalls. So, I use ORMs where they work well, and drop into SQL all the time where they don't.

The trick, as with most things, is finding the right balance: don't try to abstract away complex, singular use cases, but also don't duplicate trivial code all over your code base where the ORM has done the work for you.

Re:OrmHate by crazygringo on Tuesday June 26, 2012 @12:34PM (#4162772)
But ORM's don't actually help for the trivial queries.

I generally find myself writing functions to automate the trivial ones: get_single_field_by_primary_key(), get_row_by_primary_key(), get_values_by_field(), etc. There are maybe 4 or 5 of these. An entire ORM is total overkill.

As for the common joins, in my experience they rarely are that trivial. Sometimes it's a left join, sometimes an inner join. Sometimes there's a limit or not. Sometimes there's an aggregate function.

If you find yourself writing the same query structure 20 times, then create a function for it. But I've never found that to be the case in projects I work on.

I just personally find that ORM's are total overkill in the situations where they can be usefully applied. So the disdain for them kind of makes sense.

Re:OrmHate by gnaritas on Tuesday June 26, 2012 @12:52PM (#4162872)
> I generally find myself writing functions to automate the trivial ones: get_single_field_by_primary_key(), get_row_by_primary_key(), get_values_by_field(), etc. There are maybe 4 or 5 of these. An entire ORM is total overkill.

Making a homebrew data layer isn't avoiding an ORM, it's building one.

> If you find yourself writing the same query structure 20 times, then create a function for it.

What do you think ORM's do? That is what they do, they provide a standard API for all the common queries.

Re:OrmHate by crazygringo on Tuesday June 26, 2012 @01:52PM (#4163208)
No, ORM's provide object-relational mapping, and all sorts of functions for building up queries as part of that. They don't provide an API for common queries at all. I don't even know what "all the common queries" would be... beyond get-a-value-for-pkey, every app is totally different in the kinds of queries it needs.

What I described isn't a homebrew data layer or building an ORM, it's just a few helpful shortcut functions for queries.

Re:OrmHate by ubernostrum on Tuesday June 26, 2012 @02:10PM (#4163331)
I hate to break it to you, but there are about as many definitions of "ORM" as there are of things like "functional programming".

What you have described would be considered, by many ORM critics, to be ORM.

Re:OrmHate by crazygringo on Tuesday June 26, 2012 @03:10PM (#4163644)
Aw, MAAAAN... Oh well, OK...
Re:OrmHate by gnaritas on Tuesday June 26, 2012 @09:40PM (#4165094)
> every app is totally different in the kinds of queries it needs

There is more to SQL than queries. Every app need insert, updat, and delete, and a basic select 1 and select many matching a predicate. Those can all be automated; that's what ORM's do, they automate the repetitive simple stuff.

More complex queries that strain the ORM's abilities can be done in SQL in a view, and then mapped in by querying the view with the ORM. Or you can just drop into raw SQL when necessary. Every app is not different, they are largely all very similar.

Re:OrmHate by Spooky23 on Tuesday June 26, 2012 @11:06AM (#4162237)
> In my admittedly anecdotal experience, I have found that ORMs are the most useful for the most trivial queries. For anything complicated, I find it easier to drop into SQL and write the query directly

As a recovering DBA, I want to jump out of my seat and say "Yes, exactly!"

In many situations, "simple" queries can easily cover 90% of what you actually need, and simple queries are often an order of magnitude less expensive than even modestly complicated ones.

If your devs are in the habit of writing lots of SQL, they'll often decide to do things like write one complex query to avoid the need for a few trivial queries. From a developer point of view, that makes sense, but the result is that you end up chewing lots of database resources. Databases tend to become scarce resources quickly, and it's usually very expensive to scale up.

In my previous experience, the ORM helped reduce needlessly complex queries, and freed up resources on the DBA side of the house to help optimize the complex stuff that needed to be there.

Granted, I was a DBA a long time ago, and resources were far less plentiful back in the good old days. But I think this is still relevant whenever you get into "big" databases.

Re:OrmHate by sjwright on Tuesday June 26, 2012 @01:38PM (#4163117)
> If your devs are in the habit of writing lots of SQL,

> they'll often decide to do things like write one complex

> query to avoid the need for a few trivial queries.

As someone who single-handedly maintains a large codebase and a large database for a large website, I can assure you you've hit the nail on the head but completely missed the point.

Unless you're writing a hello world style to-do list, trivial queries tend to be the vast minority of the application logic needed. Most queries need some degree of tuning, whether that be to pull out specific rows, associate related data, summarise, or sort. Do this right, and you can achieve in a single query what would take multiple, dozens, perhaps hundreds of round trips to the database if written with "trivial" queries.

And let's not forget, it's particularly rare for the "complex" query to be more complex than the application code needed to implement the equivalent task. SQL isn't always beautiful, but it can often express ideas more succinctly than most general purpose languages. One GROUP BY can save you from writing big loops. One INNER JOIN or subquery can save you from subsequent database calls. One HAVING clause can save you pages of twisted logic.

A "complex" query generally avoids the need for a few trivial queries that are comparatively inefficient, both in terms of resources and latency. The goal is to allow the database to do the hard work of extracting every byte you need and nothing else. Databases are good at this shit, and even with a good ORM you're often forced to compromise or maintain two worlds within one app.

---------

That said, I acknowledge that there are multiple legitimate points of view on this debate. I tend to find that the SQL vs ORM debate tends to fall depending on the sort of code that's being written, and the sort of logic that's needed. Desktop apps tend to suit ORMs more readily than web apps. Smaller and self-contained apps tend to survive ORMs more gracefully than broad-scoped apps.

Nothing can excuse stored procedures though. Useless garbage.

Re:OrmHate by Spooky23 on Wednesday June 27, 2012 @08:42AM (#4166603)
I agree with you too, which may sound strange, but I think the difference is in the teams. I think in your case (and many others), my position doesn't make sense. Why? Because you're competent and grok programming and databases, which is an amazingly rare. Also, you're working on a really small team.

In a bigger or more distributed team, you need to have strong standards and governance when many people are sharing one resource. Otherwise, you run into a "tragedy of the commons" scenario where the whole business suffers.

Re:OrmHate by maratd on Tuesday June 26, 2012 @10:40AM (#4162058)
> In my admittedly anecdotal experience, I have found that ORMs are the most useful for the most trivial queries.

Bingo. In other words, non-relational queries work well and relational queries don't. When I'm joining multiple tables and looking for specific rows/objects that match multiple restrictions, ORM turns into a migrane of epic proportions. SQL was specifically designed to solve that exact problem. Abstracting away from SQL will simply increase the pain factor.

Re:OrmHate by gnaritas on Tuesday June 26, 2012 @12:54PM (#4162880)
> When I'm joining multiple tables and looking for specific rows/objects that match multiple restrictions, ORM turns into a migrane of epic proportions.

Perhaps you're doing it wrong. Put the complex query in a proc or view, and use the ORM to map the result set into memory. Using an ORM doesn't mean not using SQL when it works better.

Re:OrmHate by y0ghur7_xxx on Tuesday June 26, 2012 @03:48PM (#4163790)
>> When I'm joining multiple tables and looking for specific

>> rows/objects that match multiple restrictions, ORM turns into a

>> migrane of epic proportions.

>

> Perhaps you're doing it wrong. Put the complex query in a proc or

> view, and use the ORM to map the result set into memory. Using an ORM

> doesn't mean not using SQL when it works better.

that's what he sad: use sql when it works better. if you put it in a proc, a view or your application makes no difference.

Re:OrmHate by jerf on Tuesday June 26, 2012 @10:53AM (#4162151)
That's the view I've come around to. The more I learn, the more I see that the ORM problem isn't just hard, it's effectively unsolvable. They don't map. There is no such map. It turns out to be a general principle that just because something is basically impossible doesn't mean that you can't write code that sort of kind of does it mostly most of the time, but the usually when that happens you end up with an enormous endless tar pit of a mess that even after massive resource expenditure still has lots of quirky problems and doesn't really work. It's better to build your system on foundation that doesn't involve solving an impossible problem.

Another example: "Multitasking" in Mac OS prior to OSX. No amount of hackery can turn a cooperative multitasking system into a preemptive one... but you sure can make a big mess trying. There's a lot of other examples if you look around. Look for a big, endlessly-growing mass of code that never seems to stabilize no matter how many resources are poured into it, and you'll probably find it's trying to do something impossible deep down in its core.

Re:OrmHate by dasil003 on Tuesday June 26, 2012 @10:32AM (#4162009)
And yet many ORMs such as ActiveRecord let you do this trivially, maintaining as much of it's functionality as you want, such as mapping column types to appropriate pl types. In my experience, even if you had a policy of all queries being written explicitly in SQL by all developers and encapsulating these into your own data access layer, you would still have quite a bit to gain from the well-tested community boilerplate in one of the available ORM packages.
Re:OrmHate by davesims on Tuesday June 26, 2012 @11:13AM (#4162290)
This. Use ORMs for what they do well, and move into straight SQL when it makes sense.
Re:OrmHate by debacle on Tuesday June 26, 2012 @10:33AM (#4162012)
> Those people who best understand the inherent object-relational impedance mismatch tend to be the very people who conclude that the effort isn't worth it.

You're showing a severe bias.

> I have found that ORMs are the most useful for the most trivial queries.

That's really what ORMs are for. Three joins, max. Any more than that and you really can't trust the ORM. It's a computer, after all, not a DBA.

Re:OrmHate by mping on Tuesday June 26, 2012 @10:59AM (#4162200)
I strongly disagree with this. I'm consulting for a project where I cannot discuss technical decisions about database modelling; sometimes we need to query 20/30 tables or more in a tx, with very complicated mappings 5+ levels deep. If I was to write the sql for this, I'd take 10x the time and it would be wrong anyway.

Of course, this is one end of the spectrum where the ORM just enable me to shoot in the foot faster. I've also worked in the other side of the spectrum where I could use an ORM and generate exactly the sql query I wanted.

At the very least, ORMs can save you substantial time because of typesafe queries (if they are typesafe) and expressiveness (specially on join syntax).

My teacher used to say that you can solve half of your problems with an abstraction layer; I think for lots of cases an ORM does just this.

Re:OrmHate by jerf on Tuesday June 26, 2012 @11:16AM (#4162302)
"If I was to write the sql for this, I'd take 10x the time and it would be wrong anyway."

SQL is the problem here. It's so hard to write reusable, composable fragments of SQL that we've all just internalized the idea that we have to write the query you're referring to from scratch every time, which is a bizarre and annoying intrusion of 1970s software engineering into our 2012 world. You almost certainly have patterns of access you are using, and if you could define those in a more sensible query language, and easily compose them together later, basically DRY out your queries, you might not find this such a daunting prospect, and might not need to use an ORM to paper over this deficiency.

It's SQL that's the real, core problem here, and I mean the language qua query language, not the relational model, not object orientation, not even the impossible mapping thereto, it's that SQL is a terrible, terrible language. We should stop elevating it to the status of One True Query language. If SQL didn't suck so hard it wouldn't be so necessary to try to solve the impossible mapping problem in the first place.

Re:OrmHate by debacle on Tuesday June 26, 2012 @11:28AM (#4162390)
I would agree with you. It's impossible to write a query that can both be composable and also use all of the features of the SQL server's optimizer.
Re:OrmHate by jerf on Tuesday June 26, 2012 @12:34PM (#4162767)
I've done the "kick a design around in my head as I'm driving home" on this problem a few times, and the optimizer is always what ends up stopping me dead. I can create a new query language, I can write the compiler to turn it into an equivalent SQL query, but I have no interest in the monstrosity the language would become if it tried to give you enough control over the resulting SQL to satisfy optimizers. A query language that produces uselessly slow SQL is itself useless, so it's hard to get up the motivation to start a project like this when there's basically no way I can produce something usable.
Re:OrmHate by lucian1900 on Wednesday June 27, 2012 @05:21PM (#4169308)
I've found tools like SQLAlchemy do let you compose queries pretty well.

I've been told ClojureQL has a similar property.

Re:OrmHate by mping on Wednesday June 27, 2012 @05:06AM (#4166166)
Interesting remark. Although I really can't see any other good alternative at the moment.
Re:OrmHate by nollidge on Tuesday June 26, 2012 @11:49AM (#4162510)
I've often felt that SQL was terrible, but I don't know what would be better.
Re:OrmHate by jules on Tuesday June 26, 2012 @12:51PM (#4162870)
Relational algebra or Datalog.
Re:OrmHate by debacle on Tuesday June 26, 2012 @11:10AM (#4162268)
I guess I shouldn't have made such generalizations about how many joins, and I'll fully admit that I've written a 10+ join query in an ORM and the SQL it generated was just fine.
Re:OrmHate by RyanMcGreal on Tuesday June 26, 2012 @10:53AM (#4162154)
In other words, ORMs are at their most useful when solving the least challenging problems. To which we must ask: why bother?
Re:OrmHate by debacle on Tuesday June 26, 2012 @10:59AM (#4162195)
I'm going to make an argument here, and you might disagree but that's fine:

If you don't understand the purpose of an ORM, you haven't worked on a sufficiently large enough software project or don't fully understand what the definition of an ORM is.

If you need to write a report of any fashion, do not use an ORM. You can help write the report with your ORM of choice, but at some point you need to get down to the language that the database speaks because you're doing the kind of set mathematics that ORMs aren't made for.

BUT, if you need to add a user permission, and you're doing anything other than:

$user->addPermission( Permissions->get( 'CAN_EAT_CAKE' ) )

you're doing something wrong.

Re:OrmHate by Philadelphia on Tuesday June 26, 2012 @11:47AM (#4162495)
But that doesn't require or imply an ORM. That's just a matter of having an object model.
Re:OrmHate by stevenwei on Tuesday June 26, 2012 @01:57PM (#4163241)
If your object model at some point gets persisted into a database, then you're going to need to write code to (load from/save to) the database. At which point, if it walks like a duck, and quacks like a duck...
Re:OrmHate by debacle on Tuesday June 26, 2012 @11:59AM (#4162596)
If you have a database, any object model is at some level an ORM.
Re:OrmHate by nathan_long on Tuesday June 26, 2012 @11:44AM (#4162483)
>> In other words, ORMs are at their most useful when solving the least challenging problems. To which we must ask: why bother?

1. I disagree with your assertion. Ruby's ActiveRecord has scopes that make it very easy to compose complex queries from pieces; start with a simple query, then conditionally add clauses like where, having, joins, group, order, etc. I'm looking at code right now where mixing and matching clauses like that was crucial. It would have been a nightmare to do in raw SQL.

2. Even if you were right, "least challenging" != "least annoying." Washing clothes is a trivial problem, but I'm glad to have a machine do it. Same for writing the same boilerplate SQL 30 times a day.

Re:OrmHate by Daniel_Newby on Tuesday June 26, 2012 @11:20AM (#4162341)
Because most tasks are simple. If you need to write SQL to put the three most recent blog postings on the company homepage, you're doing it wrong.
Re:OrmHate by brazzy on Tuesday June 26, 2012 @10:34AM (#4162021)
Any good ORM will let you write your query directly, and still save your the work of mapping the resultset to a collection of objects.
Re:OrmHate by yatendra on Tuesday June 26, 2012 @10:49AM (#4162119)
I agree. There are some micro ORMs in .Net world that do exactly this and lately I have started using Dapper http://code.google.com/p/dapper-dot-net/ that came out of stackoverflow and I really enjoy using this over nhibernate.
Re:OrmHate by lucian1900 on Tuesday June 26, 2012 @11:00AM (#4162203)
And tools that help one write SQL queries more easily (like SQLAlchemy) are so much more useful than ORMs.

The fact that SQLAlchemy also has an ORM built on top of those tools only sweetens the deal.

Re:OrmHate by RyanMcGreal on Tuesday June 26, 2012 @11:38AM (#4162448)
Heh. I use SQLAlchemy, but I don't use the associated ORM.
Re:OrmHate by jakejake on Tuesday June 26, 2012 @11:53AM (#4162549)
That's exactly how I feel, in fact I was inspired to write an article about it when this topic came up on HN a few weeks ago http://verysimple.com/2012/05/14/re-orm-haters-in-defense-of...

As an ORM author I may be a bit biased, but I really think that, like all things, ORMs are a tool that can be used or abused. That's no reason to throw them out. It must have snuck up on me that there was even an anti-ORM thing happening!