Extending Sequel

As I mentioned in my previous post, I love the Sequel library from Jeremy Evans! One thing in particular I love about the framework is how easy it is to extend with custom plugins and extensions.

One example I've developed for my day job is to create an extension for the BETWEEN/AND clause of SQL.

module Sequel
  module SQL
    class BetweenExpression < BooleanExpression
      module DatasetMethods
        def between_sql_append(sql, bet_expr)
          sql << '('
          literal_append(sql, bet_expr.expr)
          sql << ' BETWEEN '
          literal_append(sql, bet_expr.lower_bound)
          sql << ' AND '
          literal_append(sql, bet_expr.upper_bound)
          sql << ')'
        end
      end

      attr_reader :expr, :lower_bound, :upper_bound

      def initialize(expr, lower, upper)
        @expr        = expr
        @lower_bound = lower
        @upper_bound = upper
        freeze
      end

      to_s_method :between_sql
    end

    module Builders
      def between(expr, lower, upper)
        case expr
        when ::Sequel::SQL::BetweenExpression
          ::Sequel::SQL::BetweenExpression.new(expr.expr, lower, upper)
        else
          ::Sequel::SQL::BetweenExpression.new(expr, lower, upper)
        end
      end
    end
  end

  Dataset.register_extension(:between_expressions, SQL::BetweenExpression::DatasetMethods)
  Dataset.include SQL::BetweenExpression::DatasetMethods
end

Let's breakdown that example.

1. Because BETWEEN/AND is a filtering expression that yields a true/false answer I've decided to make the BetweenExpression a subclass of Sequel::SQL::BooleanExpression. There is also Sequel::SQL::GenericExpression which is used for things like mathematical expressions that could be used as projections or as filters (e.g. my_column + 2).
2. The internal DatasetMethods module follows Jeremy's pattern for encapsulating behavior. It is used when registering the extension as well as adding functionality to Sequel::Dataset.
3. An extension determines what state it needs, so the initialize message can be designed however you like.
4. In our case, we need to know what is being tested. It's an expression of any sort called expr. This expression can be a Symbol, a Sequel::SQL::Identifier, a Sequel::SQL::AliasedExpression, or any other type of expression that the framework knows how to literalize (e.g. (a + b) which we could write as Sequel[:a] + Sequel[:b]).
5. We also need to know the lower and upper bounds of the SQL type to which we're comparing. Again, these can be complex Sequel expressions, constants, symbols, etc.
6. The magic (yep, I sorta buried the lead) ... We define a between_sql_append method and then send the BooleanExpression.to_s_method message with :between_sql as its only argument. What gives? This is the convention in extensions. The framework will take that symbol and create an instance method on our expression that will dispatch to our between_sql_append message when our expression is being literalized. I believe you can define BetweenExpression#to_s_append(dataset, string) instead of using this hook, but I prefer to follow Jeremy's pattern.

I've not yet talked about two portions: building this expression and the literalizing it.

Sequel::SQL::Builders

The Builders module in Sequel eases the creation of various types of expressions without using Sequel's core extensions (e.g. Sequel.lit(...), Sequel.function(...)). For my purposes, I want to be able to create the BETWEEN/AND clause like this: Sequel.between(what, lower, upper), so I define the between method that takes an expression and the bounds.

When altering expressions in Sequel, it's common to do a bit of runtime type identification (RTTI) on the parameters being sent with the message and build up from there. In our case, if the expression given to us is already a BetweenExpression we'll use its expr as the expression we're testing against. For example, this is perfectly valid (though questionable): Sequel.between(Sequel.between(:a, :b, :c), :d, :e). This type of thing comes in handy when expressions are being passed around the system and altered based on the current context.

Literalization

BetweenExpression#between_sql_append tells Sequel how to append the given expression to a SQL string. It's important to remember that the context of this method is within an instance of a Dataset. Datasets dispatch to expressions to iteratively build up the SQL string that is eventually sent to the database.

Our method knows the basic bits of the clause (BETWEEN and AND), but it doesn't need to know how to create the SQL string for any of the expression's instance variables. Remember that the expr, lower_bound, and upper_bound instance variables could be any type of Sequel::SQL::Expression --- even another Dataset.

To make things easy on extension and plugin developers, Jeremy provides the literal_append message that receives the SQL string as its first argument and the "thing" you want to append to it. That message will take care of dispatching to a wide array of messages to create the proper behavior.

Conclusion

Writing this extension was pretty simple, and definitely beats creating literal strings: Sequel.literal("? BETWEEN ? AND ?", :foo, :bar, :baz). When possible, I prefer to build datasets with purpose-built expressions rather than mixing in raw SQL.

I'll be releasing this as a gem soon. You can see the fleshed out code with added behavior here.