Is it possible to add comments to all queries?

[zom-yoshidan]

In my product, we use Sequel to access Postgres, and this product is running on Kubernetes. We are trying to embed traceparent as a comment in every SQL statement for distributed tracing.
However, unfortunately, the usage of Sequel is not unified among the multiple developers, and in some code, the model is used, in some code, SQL is written to a file and read, and in some code, Dataset is used.

For example, as follows:

# This process is common
connection = Sequel.connect(opts)

sql = 'select * from t1'
connection.fetch(sql).all

class Table2 < Sequel::Model
end
t2 = Table2.new

connection[:table3___t3].select(:t3__id).where(id: 1)

These are all written in separate files, so dealing with each one individually takes a significant amount of time.
But the requirement to add comments is the same for any pattern.

require 'opentelemetry/sdk'
trace_id = OpenTelemetry::Trace.current_span.context.hex_trace_id
comment = "trace_id: '#{trace_id}'"

Is there any method to ensure comments are always added in any case?

What I tried

I created a class to override the behavior of the connection, calling comment every time right before calling something of Dataset.

class WrapSequelDataset
  def initialize(connection)
    @connection = connection
  end

  def method_missing(name, *args, &block)
    result = @connection.send(name, *args, &block)

    if result.is_a?(Sequel::Dataset)
      trace_id = OpenTelemetry::Trace.current_span.context.hex_trace_id
      comment = "trace_id: '#{trace_id}'"
      result = result.comment(comment)
    end

    result
  end
end

connection = WrapSequelDataset.new(Sequel.connect(opts))
connection.fetch(sql).all  # add comment & fetch

However, we abandoned this method because when calling dataset.limit(limit, offset).all, sql became nil.

undefined method +' for nil:NilClass --- /app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/extensions/sql_comments.rb:115:in block (2 levels) in <module:SQLComments>'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:147:in `sql'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:1640:in `subselect_sql_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:1251:in `literal_dataset_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:109:in `literal_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:271:in `aliased_expression_sql_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/sql.rb:112:in `to_s_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:1276:in `literal_expression_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:89:in `literal_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:1137:in `identifier_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:1147:in `block in identifier_list_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:1145:in `each'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:1145:in `identifier_list_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:1597:in `source_list_append'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:1435:in `select_from_sql'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/sql.rb:248:in `select_sql'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/extensions/sql_comments.rb:104:in `block (2 levels) in <module:SQLComments>'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/actions.rb:152:in `each'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/actions.rb:51:in `block in all'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/actions.rb:1010:in `_all'
/app/vendor/bundle/ruby/2.7.0/gems/sequel-5.52.0/lib/sequel/dataset/actions.rb:51:in `all'

What I readed

• https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/sql_comments_rb.html
• Sequel 5.52.0 Released #1814

[jeremyevans]

I recommend using Database#with_comments for this:

DB = Sequel.connect(opts)
DB.extension :sql_comments

DB.with_comments('trace_id'=>'foo') do
  DB.fetch('SELECT 1 AS v')
end
# SELECT 1 AS v -- trace_id:foo

[zom-yoshidan]

Thank you for your response!
My understanding is that when using Database#with_comments, the SQL is written in a block.
As mentioned earlier, there are several program files that execute SQL.
In such cases, is the only way to methodically deal with each one individually?

[jeremyevans]

Can't you just make sure that all SQL is executed inside a with_comments block? You shouldn't need to deal with each query individually in that case. For a web application, you could have a rack middleware that does this and handles comments for all requests received by the application.

[zom-yoshidan]

I am using rack for my web application. I hadn't thought of using a rack middleware. Certainly, if I pass @app.call(env) within the with_comments block, it seems possible to achieve. Thank you! 🙇

I really appreciate you for creating such a wonderful Gem and continuously updating it.