Monday, September 18, 2006

A Ruby DSL for generating SQL

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

6 comments:

  1. Anonymous4:21 PM

    Would you care to comment on why this is better than just using SQL? I tried but can't think of any reason to do something like this rather than "to stay in ruby".

    ReplyDelete
  2. Anonymous4:54 PM

    If you need to alter the SQL based on any conditions. For example:

    Insert.into[:table1].values do
    Select[id].from[table2].where do
    equal :foo, :bar unless something.nil?
    end
    end

    We asked ourselves the same question when we were working with code similar to this. However, in the end it made the code easier to work with due to various conditional statements.

    ReplyDelete
  3. Anonymous6:27 AM

    SQL is already a DSL. It's a language this is specificly designed for querying databases.

    I think this example neatly captures the main problem I have with most "in-language" DSLs - I believe they are popular because:

    a) the tools are not available, or people are too lazy or unskilled, to implement a proper grammer and parser (not the case in this example).

    b) there isn't enough good tool support, or perhaps libraries/language features, for developers to work in multiple real languages in one project. This would involve the ability to not only write code in multiple languages, but also to move data / variables / conditions between them and to be able to refactor across the board.

    ReplyDelete
  4. Anonymous7:04 AM

    Chris,

    I basically agree with everything you stated.

    SQL is a DSL. However, I cannot use that DSL in Ruby so I decided to put together something that I could use without having to work with strings. For example, I prefer our DSL to the following code:
    sql = "insert into table1 values sql select id from table2"
    sql << " where foo = bar" unless something.nil?
    Of course, our DSL is simply building the string underneath.

    Perhaps in the future someone with implement Linq in Ruby, ActiveRecord will cover all cases where I need to create SQL statements, or someone will implement an even better solution. But, until then, we are stuck using strings to represent code for other languages.

    ReplyDelete
  5. Anonymous8:19 AM

    For sure. Although I don't think linq is a great solution either - it's still another 'in-language' DSL for doing what SQL is already good at.

    The problem you've encountered is a good example of the second reason I suggested - that there isn't good techniques for integrating between languages well. Integrating between languages by building grammer strings to feed into a parser just isn't nice.

    But I do think that in-language DSLs are just stop-gaps until a good solution for these sort of issues is found.

    ReplyDelete
  6. Hello, not sure this is the correct forum for this sort of question, but I'm having a bit of an issue with an Inert.into statement. I have the hash:

    a ={"col1"=>"did you get the TPS report?", "col2"=>23.5, "col3"=>"mr magoo", "col4"=>4}

    and then I run Insert.into[:foo][a.keys.to_sql].values[a.values.to_sql] but I'm getting a sql statement like :

    insert into foo ('col1', 'col2', 'col3', 'col4') values () ('did you get the TPS report?', 23.5, 'mr magoo', 4)

    Notice the values() and then the actual values ('did you get the TPS report?', 23.5, 'mr magoo', 4)

    What am I doing wrong?

    BTW, this is awesome Jay!

    ReplyDelete

Note: Only a member of this blog may post a comment.