Friday, March 09, 2007

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.

  • Additional characters required in simple statements.
    "select column1 from table1 
    where name = '#{person_name}'"

    # becomes

    Select[:column1].from[:table1].where do
    name = person_name
  • 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

  • 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
  • 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)
    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.


  1. How do you handle OR conditions? That's the biggest stumbling block in my thinking since Ruby's going to short-circuit dsl evaluation if you use the normal operators.

    Also, I'm curious why you chose #= instead of #== for equality. It seems a little jarring?

  2. sam,
    I've not needed an OR condition yet. I hadn't really thought about it. I wouldn't add it to the library until I (or someone else using the lib) needed it. I could picture it working like:

    Select.all.from[:table1].where do
    column1 = 1
    end.or do
    column1 = 'Y'

    Like I said though, I haven't needed this, so I haven't thought much about it.

    As far as using #=, I was/am trying to follow SQL syntax as much as possible to keep the barrier to entry as low as possible.

  3. Jacob Fugal10:16 PM

    I'm curious also how you manage "not" and "not equal". Ruby doesn't allow you to redefine the "!" operator (and "a != b" is implicitly converted to "!(a == b)")...

    Jacob Fugal

  4. If you'd chosen just black on black text, this blog wouldn't been much less readable. ;) Fortunaly Opera has user mode button to turn off CSS...

    But the sqldsl thing is nice (I think that constructing sql from raw strings is evil - somewhere you'll surely forget the sql_escape).

  5. Jacob,
    I haven't needed not equal yet. After spending about 15 minutes thinking about it, I would probably use the <=> operator since in SQL I would likely use <>. It's not quite the ideal scenario, but it's close enough that I can sleep at night. Of course, I'd love to hear better suggestions.

  6. Jacob,
    I could also use:
    table1.column1.not_equal table1.column2

  7. I noticed in the RDOC that you added an OrWhereCondition. But from the RDOCs it looks like you do not wrap the generated SQL in paranthesis ... that u emit ...
    or = 1 and foo.baz = 2
    instead of
    or ( = 1 and foo.baz = 2)

    It would seem that this wrapping would be necessary to prevent logical ambiguity when combining and's and or's.

  8. Bill,
    Good catch. That's why I dislike adding features instead of extracting them. I'll update the docs and change the code.
    Thanks, Jay

  9. Hi Jay!
    Good work! Probably I'll use SQLDSL in one of my projects (MetaRails) but I've a question: Did you know how to handle JOIN sentences?


  10. hello spejman,
    I haven't added join support, but I have been thinking about it.

    I expect it will work like this:

    table1.column1 = table2.table1_id

    a) does that appear to be a suitable solution
    b) what types of joins are you looking for? (e.g. inner, left outer?)

  11. what I really need is a kind of inner joins nesting method, because one of the compotents of MetaRails is a kind of query generator.

    We can have sentences like:
    INNER JOIN t3 c ON b.id2 =
    WHERE c.attr1 = "foo" and b.attr1 = "foo2"

    Does your proposed method support this kind of sentences?

    Thanks for your attention. If you are interested in this query generator, please contact with me and I'll give you instructions of how try a demo.

  12. (after I make the addition) you should be able to do this with the following syntax.

    select.all.from( do =
    end.inner_join( do
    b.id2 =
    end.where do
    c.attr1 = "foo"
    b.attr1 = "foo2"

  13. It will be great to have these additions. I think that will be enough.



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