David Heinemeier Hansson, the man behind Ruby on Rails, has the best take yet on O/R Mapping frameworks and SQL:

 

 

            But Isn’t Sql Dirty?

 

            Ever since programmers started to layer object-oriented systems on top of relational databases, they’ve struggled with the question of how deep to run the abstraction.  Some object-relational mappers seek to eradicate the use of SQL entirely, striving for object oriented purity by forcing all queries through another OO layer.

           

Active Record does not.  It was built upon the notion that SQL is neither dirty nor bad, just verbose in the trivial cases.  The focus is on removing the need to deal with the verbosity in those trivial cases but keeping the expressiveness around for hard queries – the type SQL was created to deal with elegantly.

.

            Therefore, you shouldn’t feel guilty when you use find_by_sql() to handle either performance bottlenecks or hard queries.  Start out using the object-oriented interface for productivity and pleasure, and the dip beneath the surface for a close-to-the-metal experience when you need to.

 

           

                                                              David Heinemeier Hansson, Agile Web Development with Rails

 

 

Not sure about the pleasure comment, but I do strongly agree with him that O/R mappers need a way to expose the native query capabilities of the underlying store – in the case via SQL.  Following this design tenant has an interesting consequence in that the object query language does not need to be as expressive as SQL nor have all of its functional capabilities – the user of the framework can always use SQL if the object query language does not support the required functionality.  Without those constraints the O/R framework design can be focused on supporting a simpler more pure object query language.