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.

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.

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:Pros: