Why use SQL builders? Arel v. Sequel v. T-SQL

Question

I'm trying to understand the benefits of building SQL via an object-oriented builder DSL vs. parameterizing a raw SQL string. After researching/implementing the same query three ways, I notice that the raw SQL is by far the easiest to read. This begs the question, "why jump through a hoop?" Why not just declare and use raw SQL?

Here's what I've come up:

First, I guess it makes the SQL more portable as it could then be utilized by any DB with an adapter. I guess this is the biggie, right? Still, isn't most T-SQL intelligible to most databases?

Second, it provides a query object that can be reused--as the basis for other queries, named-scope chaining, etc.

What's the main return on investment you realize by building your SQL instead of declaring it?

def instances_of_sql(ttype_id) #raw sql
  ttype_id = get(ttype_id).try(:id)
  ti   = get('tmdm:type-instance')
  inst = get('tmdm:instance')
  type = get('tmdm:type')

  self.class.send :sanitize_sql, [%{
    SELECT t.*
    FROM associations a
    JOIN roles type    ON type.association_id = a.id AND type.ttype_id = ?
    JOIN roles inst    ON inst.association_id = a.id AND inst.ttype_id = ?
    JOIN topics t      ON t.id = inst.topic_id
    WHERE a.topic_map_id IN (?)
    AND a.ttype_id    = ?
    AND type.topic_id = ?
  }, type.id, inst.id, self.ids, ti.id, ttype_id]
end

def instances_of_sql(ttype_id) #sequel
  ttype_id = get(ttype_id).try(:id)
  ti = get('tmdm:type-instance')
  ir = get('tmdm:instance')
  tr = get('tmdm:type')

  DB.from(:associations.as(:a)).
    join(:roles.as(:tr), :tr__association_id => :a__id, :tr__ttype_id => tr[:id]).
    join(:roles.as(:ir), :ir__association_id => :a__id, :ir__ttype_id => ir[:id]).
    join(:topics.as(:t), :t__id => :ir__topic_id).
    where(:a__topic_map_id => self.ids).
    where(:a__ttype_id => ti[:id]).
    where(:tr__topic_id => ttype_id).
    select(:t.*).sql
end

def instances_of_sql(ttype_id) #arel
  ttype_id = get(ttype_id).try(:id)
  ti   = get('tmdm:type-instance')
  inst = get('tmdm:instance')
  type = get('tmdm:type')

  #tables
  t    = Topic.arel_table
  a    = Association.arel_table
  tr   = Role.arel_table
  ir   = tr.alias

  a.
    join(tr).on(tr[:association_id].eq(a[:id]),tr[:ttype_id].eq(type[:id])).
    join(ir).on(ir[:association_id].eq(a[:id]),ir[:ttype_id].eq(inst[:id])).
    join(t).on(t[:id].eq(ir[:topic_id])).
    where(a[:topic_map_id].in(self.ids)).
    where(a[:ttype_id].eq(ti[:id])).
    where(tr[:topic_id].eq(ttype_id)).
    project('topics.*').to_sql
end

I totally appreciate named scopes and see how chaining them can be beneficial. I'm not worried about accessing related records via a model. I'm purely talking about building a complex query.

Rainproof

"Second, it provides a query object that can be reused--as the basis for other queries, named-scope chaining, etc." That is one reason I use Sequel, as well as the fact that dataset methods assigned to models allows me to write short, descriptive Ruby code that may sometimes produce complex SQL. The best use, however, that I have run into is in a complex search page where a variety of UI elements allow me to slowly and conditionally tweak the query as I process the search options. – Panlogism

Spot on with the question. I often find myself spending 10% of time writing the actual query and 90% of the time translating it to ORM's syntax, most of the problems having with grouping and aggregate expressions, combined with tables with multiple aliases. – Soricine

@Phrogz: You should have made that an answer, as tweaking the queries using Ruby is indeed one of the biggest advantages of ORMs, IMO. Much easier when you have it in chained methods than carving it with strings. – Soricine

Answer 1

The link that @Kyle Heironimus gave to Nick Kallen's thoughts on Arel had this line:

You'll note the use of the derived table in the subselect. This is terrible, in my opinion. Only advanced SQL programmers know how to write this (I’ve often asked this question in job interviews I’ve never once seen anybody get it right). And it shouldn’t be hard!

Well, Kallen puts this down to the lack of closure under composition in SQL. That may be true in some cases, but my experience is much more prosaic - that most devs are terrible at SQL. They only know the most basic things, these basic things are mis-used as they try to search for procedural solutions in a set based language. I had to argue the benefits of the database being in 3NF at one company I was at, with all the other devs, they just didn't get it. Talented guys (most of them:), but no clue about SQL or databases.

Put it in C# or Ruby or Python <insert language of choice> and the devs are happy again. They can stick with procedural/OO thinking and produce code that looks good to them.

I know this won't earn me any votes, probably quite the opposite, but it's my view. Arel looks interesting BTW.

---

As an addendum to the comments I've made above, over six months on and having used the Sequel library a lot during that time, I can say that it is indeed a beautiful thing, and now I feel that I would use it ahead of using straight SQL. Not only is it incredibly powerful and allow me to do simple and advanced things without too much head scratching (there'll always be some) it can output the SQL it has used, and it will also allow me to drop down into SQL if I feel I need to.

This doesn't in any way nullify my comments about most dev's understanding of SQL, (I was recently told, by a dev that gives talks to others, that normalisation was a relic of a time when storage space was expensive... oh dear!) just that the development of the Sequel library has obviously been done by those who really understand databases. If you know SQL and db design etc then it gives you more power more quickly. I can't say the same of the other ORM's I've used, but perhaps others would think differently.

Arnuad

I'm proficient with SQL. The trouble is I usually start out with SQL and then convert it to builder syntax. This is the "hoop" I'm referring to. – Rainproof

@Mario - I was making a general point, I hope you don't take it to mean a criticism of you. Sorry if it appears that way. I think if you're proficient with SQL then stick with SQL, although I do remember LINQ generating some SQL that was faster than my attempt (in a lighthearted bet with a colleague). You could always use it for comparison or to get you started, but going from SQL to the builder syntax sounds hard! :) – Arnuad

Answer 2

You have pretty much hit on the reasons already.

Here are thoughts from the creator of Arel.

Cancellate