Extending Sequel
Tags: ruby, sequel
Published: 2023-04-12 13:59:50

As I mentioned in [my previous post](https://dev.to/jacaetevha/theres-sql-in-my-ruby-4mdb), I love the [Sequel library from Jeremy Evans](https://sequel.jeremyevans.net/)! 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.

```ruby
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`).
1. 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`.
1. An extension determines what state it needs, so the `initialize` message can be designed however you like.
1. 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]`).
1. 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.
1. **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](https://sequel.jeremyevans.net/rdoc/classes/Sequel/SQL/Builders.html) 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](https://github.com/jacaetevha/sequel-between).