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)
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
can be shortened to:
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.
... and on top op roda there is Bridgetown with an incredibly easy and refreshing SSR functionality ...
Yes. I've looked at Bridgetown. I haven't had the opportunity to actually use it, but it seems simple and elegant.