DEV Community: Jason Rogers †

Extending Sequel

Jason Rogers † — Wed, 12 Apr 2023 13:59:50 +0000

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.

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).
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.
An extension determines what state it needs, so the initialize message can be designed however you like.
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]).
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.
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.

There's SQL in my Ruby

Jason Rogers † — Thu, 06 Apr 2023 21:33:04 +0000

I love the Sequel library from Jeremy Evans (so much better than Rails' AREL). I've used it as my ORM-of-choice since 2008. When leveraging Sequel I almost always use the DSL, but there are times that I want to use bare SQL. When that happens, I almost always use HEREDOCs and my own version of String#squish.

dynamic_sql = <<~SQL.squish
  WITH date_set AS (
    SELECT * FROM some_func(:code)
  ), expanded_date_set AS (
    SELECT id
      , ds.start_date
      , ds.end_date
      , count(*) FILTER(WHERE lower(v) = ANY(:numerator_filter)) a
      , count(*) FILTER(WHERE lower(v) != ANY(:denominator_filter)) b
    FROM my_data md
    JOIN date_set ds USING(id)
    WHERE code = :code
    AND ds.start_date BETWEEN md.start_date AND md.end_date
    GROUP BY 1, 2, 3
  ), vals AS (
    SELECT id, start_date, end_date
      , CASE WHEN a = 0
        THEN 0::FLOAT4
        ELSE (a::FLOAT4 / b)
        END AS val
    FROM expanded_date_set
    ORDER BY 1, 2, 3
  ), ranges_per_val AS (
    SELECT id
      , val
      , merged_ranges(
          array_agg(
            daterange(start_date, end_date, '[)')
          )
        ) ranges
    FROM vals
    GROUP BY 1, 2
  ), unnested AS (
    SELECT id, val, unnest(ranges) AS r
    FROM ranges_per_val
  )
  SELECT id
    , lower(r) AS start_date
    , upper(r) AS end_date
    , val
  FROM unnested
  ORDER BY 1, 2, 3
SQL
DB[
  dynamic_sql,
  code: 'some-code',
  numerator_filter: Sequel.pg_array(%w[foo]),
  denominator_filter: Sequel.pg_array(%w[bar baz]),
]

(some editors (e.g. VS Code, IntelliJ) might even give you SQL syntax highlighting in that HEREDOC)

If I want to tweak that SQL it's fairly easy to copy and paste it into a database REPL like psql. In this case, I'd only need to make a few edits in the REPL to replace the placeholders with real values.

Yeah, but...

That may be somewhat of a contrived example, but it's nice to know that you can create dynamic SQL so easily.

A DSL version wouldn't be that much more difficult. Here's one way of doing it. Note that this produces SQL with sub-expressions instead of CTEs. While CTEs are possible, in this case it would make the code harder to read.

date_set = DB[Sequel.function(:some_func, 'some-code')]

expanded_date_set = DB[DB[:my_data].as(:md)]
  .join(date_set.as(:ds), [:id])
  .where(code: 'some-code')
  .where(between('ds.start_date', 'md.start_date', 'md.end_date'))
  .group(1, 2, 3)
  .select(:id, Sequel[:ds][:start_date], Sequel[:ds][:end_date])
  .select_more(count.filter(lower(:v) => 'foo').as(:a))
  .select_more(count.filter(lower(:v) => %w[bar baz]).as(:b))

vals = DB[expanded_date_set].select(
  :id,
  :start_date,
  :end_date,
  case_when(
    :a,
    is: 0,
    then_take: cast(0, :FLOAT4),
    else_take: cast(:a, :FLOAT4).sql_number / :b
  ).as(:val)
)

merged_ranges = merged_date_ranges(array_agg(date_range(:start_date, :end_date)))

ranges_per_val = DB[vals]
  .select(:id, :val, merged_ranges.as(:ranges))
  .group(1, 2)

unnested = DB[ranges_per_val].select(:id, :val, unnest(:ranges).as(:r))

DB[unnested].select(
  :id,
  :val,
  lower(:r).as(:start_date),
  upper(:r).as(:end_date),
).order(1, 2, 3)

Umm... it's not all Sequel.

If you've gotten this far, the astute reader will recognize that there are some utility methods being used. Here's a slimmed down version of the functions used above.

module SequelUtils
  # common functions
  def count(what = nil)
    if what
      Sequel.function(:count, what)
    else
      Sequel.function(:count).*
    end
  end

  %w[array_agg lower merged_date_ranges upper unnest].each do |n|
    module_eval(
      "def #{n}(what) Sequel.function(:#{n}, what) end",
      __FILE__,
      __LINE__ - 2,
    )
  end

  def lower(what)
    Sequel.function(:lower, what)
  end

  def upper(what)
    Sequel.function(:upper, what)
  end

  def unnest(what)
    Sequel.function(:unnest, what)
  end

  # SQL case statements
  def multi_case(else_value)
    Sequel.case(yield, else_value)
  end

  def single_case(true_clause, true_value, else_value = nil)
    multi_case(else_value) { {true_clause => true_value} }
  end

  def case_when(what, is:, then_take:, else_take: nil)
    single_case(Sequel[what => is], then_take, else_take)
  end

  # misc
  def between(what, from, to)
    Sequel.lit("#{what} BETWEEN #{from} AND #{to}")
  end

  def cast(what, to_what)
    Sequel.cast(what, to_what)
  end

  def date_range(from, to)
    Sequel.pg_range.new(from...to, :daterange)
  end
end

Conclusion

Remember Ruby != Rails. While Rails has its place in the universe, it may not be the best decision to pull in some/many of the Rails gems just to be able to interact with your database(s). When you have to go off the Rails, consider using Sequel (it can even be used in a Rails app).

Jeremy also maintains an awesome web framework called Roda. It's lightweight, fast, and easy to use when you don't need the heft of Rails.

Cheers!