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.
Any developer that is scared of SQL should probably look for another line of work.
It's "hard" because every SQL implementation is so different. AR pretends you can change one line in a config file to change databases (they even brag about it), but I've never seen it work that way. It also doesn't help that AR just leaves many of its own features unimplemented for various databases, and doesn't even mention them in the docs.
I'm a bit scared of SQL because it's a pain to use, and I've used object databases with great success (and no pain). For complex queries, its pseudo-English syntax is more trouble than it's worth. And one of the most common uses today is full-text search, but SQL doesn't let you search that. (Officially, it doesn't even provide INDEXes; that's probably why CREATE INDEX syntax is not the same anywhere.) I don't understand why so many otherwise-great systems (like Rails) force you to use SQL.
To take another example of a standard implemented by different companies, I've taken nontrivial Common Lisp programs from one compiler to another, and almost never had problems. I would have thought that CL would be a much harder standard to get consistent than SQL, but apparently it's not.
OK, I guess that was kind of long and rambling. It boils down to:
1. It's a pain, both in terms of what it provides and what it doesn't provide, compared to every alternative I've used
2. Despite being an ISO standard, it's a different pain everywhere
3. Even libraries which exist only to make it more consistent and less painful don't help beyond fairly trivial tasks on the 1 or 2 most popular free implementations
If any other language had this property, I would not use it; SQL is no different.
I think the problem is as you say... the SQL standard is too small and hence anything outside the standard (but actually useful) is implemented differently on every platform.
However, two points. First, the diffs aren't that huge (the underlying idea is generally the same, with some caveats, it's just the syntax that must be juggled). Second, most places I know usually pick one or two database systems and then make a very long commitment to them. There is no reason the developer shouldn't be able to pick up whatever SQL flavor his shop is using.
My original point is that any developer worth his salt should be able to pick up new languages fairly quickly, and since SQL is like a mini-language, it should be learnable in mini-fairly-quickly time.
I don't think the problem is developers being afraid of SQL. There are still lots of Perl and PHP programmers who don't use ORMs. Instead, ORMs have been sold on the idea of database abstraction, and programmers have been made to feel that using raw SQL is bad practice.
I use Hibernate with Java at work. My AR and DM usage is with Ruby (and specifically, merb) at home. Wasn't aware that Java had anything like DM. Have a link handy?
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.
"SQL is a very nice, almost mathematical language which allows phenomenally powerful queries to be expressed simply and elegantly."
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.
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.
I think someone who can't get his head around SQL probably wouldn't make a very good addition to the developer team.
There's a famous paper calling ORM the "Vietnam of computer science":
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.
This is hyperbolic but it's true.
Instead of ORM I develop object-oriented tools to help with SQL manipulation. I also make use of data transfer objects (like most ORMs).
But I would never rely on an ORM to handle complex queries.
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.
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.
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.
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.
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.
Hibernate allows you to do this when necessary. I'm not married to Hibernate, but I do appreciate the speed and flexibility with which you can get an enterprise app up and running. I also know that you MUST write optimized queries on occasion, or face critical performance problems.
The problem arises when one stubbornly adheres to a paradigm; there no silver bullets, etc.
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.
Any developer that is scared of SQL should probably look for another line of work.