On my past 2 projects I've needed the ability to create ad-hoc SQL statements. These statements, following creation, are executed by using ActiveRecord::Base.connection.execute. On the first project we largely used strings to represent our required SQL. However, on our second project we decided to create a DSL that allowed us to stay in our comfortable Ruby world. That DSL is a bit more heavy-weight; however, I took the experience I gained from that project and put together a light-weight version that I've released on RubyForge.org.

The idea of the DSL was to keep as similar to SQL as possible. Below are examples of both the actual SQL and Ruby version that allowed us to generate the SQL.

Select column1 from table1 
where column2 = 12
and column 3 = 13

Select[:column1].from[:table1].where do
  equal :column2, 12
  equal :column3, 13
end

insert into table1 (column1, column2, column3) values (10, 'book', 'start')

Insert.into[:table1][:column1, :column2, :column3].values(10, 'book', 'start')

update table1 set column1 = 12, column2 = 'book'
where column1 = 10

Update[:table1].set[:column1=>12, :column2=>'book'].where do
  equal :column1, 10
end

delete from table1 where column1 = 12

Delete.from[:table1].where do
  equal :column1 = 12
end

The DSL does not contain any field validation or verify that the columns exist in the specified tables or any other magic. Currently, it's sole purpose is to allow you to easily write SQL with Ruby code.

If you are lucky you may never have any need for such a library; however, if you use find_by_sql on ActiveRecord::Base or select_values, select_value, or select_one on ActiveRecord::Base.connection you may want to give the DSL a look.

To install SQL DSL you can:

gem install sqldsl

Once installed all you need to do is require 'sqldsl' and all should be well.

For more information check out the SQL DSL documentation on RubyForge.org