I love theSequel 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 ofString#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, 3SQLDB[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 thatHEREDOC)
If I want to tweak that SQL it's fairly easy to copy and paste it into a database REPL likepsql. 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'sone 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.
moduleSequelUtils# common functionsdefcount(what=nil)ifwhatSequel.function(:count,what)elseSequel.function(:count).*endend%w[array_agg lower merged_date_ranges upper unnest].eachdo|n|module_eval("def#{n}(what) Sequel.function(:#{n}, what) end",__FILE__,__LINE__-2,)enddeflower(what)Sequel.function(:lower,what)enddefupper(what)Sequel.function(:upper,what)enddefunnest(what)Sequel.function(:unnest,what)end# SQL case statementsdefmulti_case(else_value)Sequel.case(yield,else_value)enddefsingle_case(true_clause,true_value,else_value=nil)multi_case(else_value){{true_clause=>true_value}}enddefcase_when(what,is:,then_take:,else_take:nil)single_case(Sequel[what=>is],then_take,else_take)end# miscdefbetween(what,from,to)Sequel.lit("#{what} BETWEEN#{from} AND#{to}")enddefcast(what,to_what)Sequel.cast(what,to_what)enddefdate_range(from,to)Sequel.pg_range.new(from...to,:daterange)endendConclusion
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 gooff the Rails, consider usingSequel (it can even be used in a Rails app).
Jeremy also maintains an awesome web framework calledRoda. It's lightweight, fast, and easy to use when you don't need the heft of Rails.
Cheers!
Top comments(4)
- Email
- LocationBrno, Czechia
- EducationMathematics and Computer Science
- WorkSenior Ruby Engineer
- Joined
Have you considered using virtual row blocks? It seems that a lot of methods inSequelUtils 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)
You're correct about using the builder form ofpg_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 usethe wonderful Parslet gem to effectuate much of the transformation from expressions into Sequel queries.
For further actions, you may consider blocking this person and/orreporting abuse
