There's SQL in my Ruby

#ruby #sequel #heredoc

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!

Top comments (4)

Janko Marohnić • Apr 12 '23 • Edited

Have you considered using virtual row blocks? It seems that a lot of methods in SequelUtils are just shortcuts for creating functions, which is what virtual row blocks already provide.

Also, I believe

Sequel::Postgres::PGRange.new(
  from,
  to,
  exclude_end: true,
  db_type: :daterange,
)

can be shortened to:

Sequel.pg_range(from...to, :daterange)

Jason Rogers † • Apr 12 '23 • Edited

You're correct about using the builder form of pg_range, I just forgot about it. I'll update the post.

RE: SequelUtils vs. virtual row blocks... thanks for bringing that up. VRBs are definitely powerful.

In the service I'm developing, however, VRBs are usually not practical because the inputs to these utility methods are most often complicated things; we wanted a uniform way to perform all of the transformations throughout the service. The service receives custom query language expressions from a legacy system and transforms those expressions into standard SQL queries and then returns the results. We use the wonderful Parslet gem to effectuate much of the transformation from expressions into Sequel queries.