There's SQL in my Ruby
Tags: ruby, sequel, heredoc
Published: 2023-04-06 21:33:04

I love the [Sequel library from Jeremy Evans](https://sequel.jeremyevans.net/) (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`.

```ruby
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.

```ruby
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.

```ruby
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](http://sequel.jeremyevans.net/) (it can even be used in a Rails app).

Jeremy also maintains an awesome web framework called [Roda](http://roda.jeremyevans.net/). It's lightweight, fast, and easy to use when you don't need the heft of Rails.

Cheers!