Ruby: Why sqldsl?

I recently wrote about introducing sqldsl into my current project. This led to a few conversations on what are the pros and cons of using sqldsl.

Cons:

• Additional characters required in simple statements.

  "select column1 from table1 
  where name = '#{person_name}'"
  
  # becomes
  
  Select[:column1].from[:table1].where do
    name = person_name
  end

• Another library creates a larger barrier to entry when dealing with a foreign code base.

Pros:

• Objects know how to quote themselves.

  1.to_sql                     #=> "1"
  "foo".to_sql                 #=> "'foo'"
  :column.to_sql               #=> "column"
  [1, 'foo', :column].to_sql   #=> "1, 'foo', column"

• Syntax Highlighting
• Better than string concatenation to generate SQL statements.

  "escaped_values = hash.values.collect { |value| sql_escape value }
  insert into #{hash.keys.join(", ")} values #{escaped_values.join(", ")}"
  
  # becomes
  
  Insert.into[hash.keys.to_sql].values[hash.values.to_sql]

• Much better support for conditional statement building.

  "select column1 from table1, table2
  where table1.column1 = table2.table1_id
  #{"and table1.column2 >= #{quantity}" if quantity > 0}"
  
  # becomes
  
  Select[:column1].from[:table1, :table2].where do
    table1.column1 = table2.table1_id
    table1.column2 >= quantity if quantity > 0
  end

• Fail without requiring a database trip for referencing tables in the where statement that do not appear in the table list.

  Select[:column1].from[table1].where do
    table1.column1 = table2.column1
  end  #=> Raises an error stating that table2 does not exist in the from clause.

• Open for extension where necessary. For example, ActiveRecord::Base.execute could be changed to take a block.

  class ActiveRecord::Base
    alias original_execute execute
    def execute(arg, &block)
      original_execute block.call.to_sql(connection)
    end
  end

  If this change were made, the sqldsl library could use the connection to create vendor specific sql. This allows for things like changing sql to be prepared statement compatible when the oracle adapter supports this in the future. The point is, as long as your sql is nothing more than a string it is not able to be easily modified. However, an object representation of your sql could be evaluated in various contexts to produce different results.