Should ORMs Insulate Developers from SQL?

No.<p>I use ActiveRecord and DataMapper every day at home (and Hibernate at work), so I know the benefits (although I consider Hibernate more of a hinderance than a help after using the AR model). What I don't get is why everyone acts like SQL is hard or something. It's like one level up from HTML on the difficulty scale and one level below any real programming language. The advantage of the ORM is that 90% of the time you get what you want in an abstract way. It's not to protect you from SQL.<p>Any developer that is scared of SQL should probably look for another line of work.

No, developers should not insulate themselves from SQL and databases. ORMs in my opinion are a handy way to abstract away some of the common boilerplate, but I'll never understand why some people hate SQL and relational databases. I've written wonderfully clean multi-layered reporting queries which in a screenful of lines accomplish the same amount of work that several hundred if not thousands of lines spread over half a dozen or so files/classes will accomplish. Yes, relational databases and SQL aren't always the best answers, but for large homogeneous datasets they are really really handy. Of all the programming languages that I've used professionally, I've used SQL almost every single work week. No, I'm not a DBA even though I probably could be if I needed to.

There's a famous paper calling ORM the "Vietnam of computer science":<p><i>It represents a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy.</i><p>This is hyperbolic but it's true.<p>Instead of ORM I develop object-oriented tools to help with SQL manipulation. I also make use of data transfer objects (like most ORMs).<p>But I would never rely on an ORM to handle complex queries.

Quote from the article:<p>"SQL is a very nice, almost mathematical language which allows phenomenally powerful queries to be expressed simply and elegantly."<p>I agree and that answers the question for me. I definitely prefer SQL over the unreadable code/SQL hybrids that are otherwise suggested in the article. I never liked the Hibernate Criteria, either - although sadly all other Java developers I met preferred them to HQL.<p>HQL (Hibernate Query Language) is OK - it is so close to SQL that it is very easy to learn, and reduces some of the verbosity. It is nice to be able to SELECT FROM object rather than having to list all the required fields.<p>I think someone who can't get his head around SQL probably wouldn't make a very good addition to the developer team.

Seems like an 80/20 situation: they should make 80% of cases easy or easier, yet also make it easy when you hit that 20% where you'd be better off going directly to SQL.

Constructing queries actually isn't one of the things ORMs do very well, so far. What they do well is provide a layer of logic for things like validation, triggers, and security.<p>Whatever backend your persistence system uses, you'll probably always want to take advantage of tools that are geared to that. If it happens to use flat files, there will probably be situations where you'll want to use grep, or cp, or cat. If it happens to use an RDBMS, there will probably be situations where you'll want to use SQL. I imagine this will be the case for a long time.<p>So far, I've been dubious of query-generating ORMs (except for basic CRUD), but I'm guessing that that's partly because they've been awkward. They've been getting better though. And I think the quality of the SQL-generating abstractions, and the languages they're written in, makes a big difference. If the abstraction is less powerful than SQL it will be annoying. If it's more powerful it won't be.<p>How could it be more powerful? By being written in a powerful language; by starting with a low-level, comprehensive mapping to SQL expressions and then using the power of the programming language to build up from there. I know that some Lisp packages offer such mappings, but I don't know much about working with them. It would be interesting to have a play though.

Frankly, I'm getting wary of ANY abstraction layer that tries to insulate you completely from the stuff below.<p>You know that rule that all abstractions leak? I think it's not about fixing all leaks, it's about making them leak in a sensible manner that you won't trip over when you do "common" stuff but will still let you access the full power when you need it.