Toxic Elephant

Don't bury it in your back yard!

A DSL for making SQL-like DSLs

Posted by matijs 14/04/2007 at 14h42

Setting the stage

Some time ago, I came across SQLDSL, a DSL for building SQL queries. The benefit of using a DSL over plain old string concatenation is that syntax is checked before the database server is hit. Unfortunately, SQLDSL does not deliver. It will happily accept

q = Insert.into[“frot”][“zop”][“blob”].values(“kng”).values[“kgn”]

resulting in

` q.to_sql

=> “insert into ‘frot’ (zop) (blob) values (‘kng’) values () (kgn)”


which is hardly acceptable SQL.

A metasolution

The problem is that each chained call operates on the same class, so the calls can be repeated, even when that’s not appropriate. Instead, each step in the building of the query should return an object of a different class, having only methods appropriate at that stage. Something like this:

` class Insert

def [](*fields)
  return, fields)


class InsertIntoFields

def values, @fields)




Of course, this quickly becomes tedious, with all the initialize methods having to be made, etc. Boring, boring. Let’s do some metaprogramming.

First, I need a quick way to create a class with a certain number of instance variables, that are set in the call to new(). That sounds like Struct, but the classes that creates have too many methods that would conflict with a DSL for SQL (like values()). So instead, I took a small, simplified part of Mauricio Fernandez alternative: SuperClass (specifically, I didn’t want any accessors for the fields, or named parameters):

module SuperClass def*fields, &blk) k = do defined_instance_variables = fields.clone class << self; attr_reader :defined_instance_variables end define_method(:initialize) do |*a| if fields.size != a.size raise ArgumentError, "wrong number of arguments (#{a.size} for #{fields.size})" else fields.each_with_index {|f,i| instance_variable_set("#{f}", a[i])} end end end k.instance_eval &blk if blk k end end

(Annoyingly, there’s no way to dynamically create methods with arbitrary arity, without resorting to eval-ing a string.)

The heart of this metaprogramming is this module:

module DSLChain def self.create(*fields, &blk) k =*fields) k.extend(DSLChain::Link) k.instance_eval &blk if blk k end module Link def add_step(func, field=nil) div = defined_instance_variables fields = div.clone fields << field unless field.nil? n = DSLChain.create(*fields) define_method func do |a| vals = [] fields.each do|f| if div.include?(f) vals << instance_variable_get(“@#{f}”) end end vals << a if a.size > 0 end n end end end

Stepper::Step#add_step(func, field) adds a method called func that creates an object of a new anonymous class, optionally adding a field called field. It returns the new anonymous class, so theses calls can be chained.

This means we can do the following:


Insert …

Insert = DSLChain.create(:table) do Insert.into creates an instance of this class. def self.into(table) end

  add_step(:[], :fields).
  add_step(:[], :values).class_eval do
def to_sql
  cn = table.column_names
  @fields.each {|f| cn.include?(f) or raise "Invalid field: #{f}"}
  raise "Incorrect number of values" unless @values.size == @fields.size
  res = "insert into #{table.to_s.downcase} ("
  res << @fields.join(", ")
  res << “) values (”
  res <<{|v| v.to_sql}.join(", ")
  res << “)”

end `

In the middle there, each call to add_step(func, field) creates a new intermediate anonymous class that results from a call to func on the previous one. The result is that after creating an Insert object with Insert.into, the call sequence is forced to be

Insert.into(SomeTable)[“some”, “column”, “names”]. values[“these”, “are”, “values”].to_sql

Aside One: For this to work, Insert.into takes some object that has a method column_names, like ActiveRecord objects. I’m also assuming appropriate to_sql methods to be defined on String and Numeric)

Aside Two: I changed the syntax a little bit from SQLDSL, since I like it better that way.

The original example then results in:

` Insert.into(Frob)[“id”][“zonq”].values(“kng”).values[“kgn”]

=> undefined method `[]’ for #<#:0×30050220 @fields=[“id”], @table=Frob> (NoMethodError)


More examples

If you want two options, you can do this:

` Sample2 = DSLChain.create(:one) do k = self.add_step(:foo, :two) k.add_step(:bar, :three).class_eval do def to_s “A bar: #{@one}, #{@two}, #{@three}” end end k.add_step(:baz, :four).class_eval do def to_s “A baz: #{@one}, #{@two}, #{@four}” end end end“one”).foo(“two”).bar(“three”).to_s

=> A bar: one, two, three“one”).foo(“two”).baz(“four”).to_s => A baz: one, two, four `

Where to from here?

This was mainly a proof-of-concept, of course. There is now a DSL to make DSLs. I haven’t even begun to create the DSL for the select statement, and insert is far from complete. Doing the conditions of the where clause is yet another matter, although ez_where could prove useful for that.

On the other hand, I may not get round to adding all those parts. The metaprogramming was the best part, anyway.

On the gripping hand, wouldn’t it be nicer to have a DSL that actually looks like Ruby’s array operations, such as grep and map? That would be so much more beautiful.

Tags no comments no trackbacks


Comments are disabled