

Should ORMs Insulate Developers from SQL? - muriithi
http://www.codecommit.com/blog/database/should-orms-insulate-developers-from-sql

======
Xichekolas
No.

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.

~~~
ken
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.

~~~
ken
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.

~~~
Xichekolas
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.

------
joe24pack
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.

~~~
huherto
That's true. May be SQL is really going to be the 100 year language.... at
least for business applications.

------
fleaflicker
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.

------
Tichy
Quote from the article:

"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.

------
davidw
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.

------
mattjones
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.

------
Hexstream
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.

~~~
dgabriel
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.

