Friday, March 02, 2007

Ruby: sqldsl new version

I recently put sqldsl to work on my current project. Part of putting it into our codebase was extending it to support some additional syntax. The new syntax support was extracted and put into the 1.2.2 release of sqldsl.

The 1.2.2 release adds a few minor features and a significant change.

The where block now allows you to specify conditions using traditional ruby syntax.
Select[:column1].from[:table1, :table2].where do
table1.column1 >= 1
table1.column2 <= 20
table1.column3 = table2.column1
table1.column4 = 'foo'
This change creates very nice looking code in the where block, but I'm not entirely sure it's a step in the right direction. While it looks great in the above example it also allows you to use variables, which can be confusing.
some_val = 1
Select[:column1].from[:table1].where do
column1 = some_val
end.to_sql #=> "select column1 from table1 where column1 = 1
In the above example column1 and some_val look very similar, but create very different output in the generated sql string. The alternative is to use the existing equal syntax.
Select[:column1].from[:table1].where do
equal :column1, some_val
In the previous example it is very clear that column1 is a column and some_val is a variable. The problem with this solution is that it begins to fall down when you consider <=, >=, etc. To support conditions with these operators we would have needed to implement the following (arguably ugly) syntax.
Select[:column1].from[:table1].where do
less_than_equal :column1, some_val
When faced with this decision we opted for the example that simply allowed ruby syntax.

The not_null method was added to the options available in where conditions.
Select[:column1].from[:table1].where do
not_null :column2
end.to_sql #=> "select column1 from table1 where column2 is not null"
The is_not_in method was added to String, Numeric, and Symbol.
Select[:column1].from[:table1].where do
:column1.is_not_in do
end.to_sql #=> "select column1 from table1 where column1 not in (select column1 from table2)"
Table and column aliasing was also added in this release.
Select[:column1 => :total].from[:table1 => :results].to_sql #=> "select column1 as total from table1 as results"
sqldsl 1.2.2 is available via a gem install.
> gem install sqldsl


  1. Jay - I've (sadly just) begun to explore DSLs using the techniques that you've described here and in your infoQ article. I'm getting "warning: parenthesize argument(s) for future version" with code I've copied directly from your examples. How are you dealing with that?

  2. Hello David,
    You can solve this problem like I show at

    In practice, I haven't had the issue. The work I did previously ran via eval'ing the code from a web page so the warnings were hidden in the web server logs.

  3. Doesn't ignoring the warning leave you in the risky position that the code you're writing won't work with future versions?

    I haven't been able to find anything that suggests this warning should not be taken seriously. Am I missing something?

  4. Sure, it's a risk, but I hope that the widespread usage of DSL style syntax will be reason enough to discourage the warnings from ever becoming errors. =)

  5. Hello David.
    Why sqldsl don't support Range? IMHO Range is very native for is_in query. Look at this:
    Select[:id].from[:table1].where{is_in :id, 1..5}

    Actually it's pretty simple to add Range support - I just made a copy of array.rb - range.rb, with this (actually I changed only class name and comment :-):

    class Range
    # call-seq: range.to_sql -> a_string
    # Returns a string by collecting all elements, calling +to_sql+ on each one, and
    # then joins them with ', '.
    # (1..4).to_sql #=> "1, 2, 3, 4"
    def to_sql
    self.collect { |element| element.to_sql }.join(', ')


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