Default SQL comment for all queries on database object

[janko]

In our application, we have a main MariaDB database, which we communicate with via Active Record, and analytics Postgres database, for which we use Sequel. I would like to make it easier to grep analytics queries in our logs, so was looking at using the sql_comments extension.

For starters, I would like all queries executed on our ANALYTICS_DB (which is a Sequel::Database object) to have a default comment of database:analytics or something like that. I was wondering if there is a way to automatically add Dataset#comment to all datasets for this database. We're not using Sequel models at all.

On top of that, I would ideally like to append additional information about where each query is coming from, and for that I'd probably use Database#with_comments.

[janko]

Do you think the default database:analytics thing is better done at the logging level, rather than via SQL comments? I'm not sure how I would go about that.

[jeremyevans]

In terms of logging vs. comments, it depends on what logs you are analyzing. If you are analyzing logs produced by Sequel, either is fine. If you are analyzing logs produced by the database, then you'll probably need to use SQL comments.

In your case, it sounds like you are analyzing logs that are written to by both ActiveRecord and Sequel. A simple approach to your problem would be to use a different log file for Sequel than you are using for ActiveRecord (or an additional Sequel-specific log), and then everything in the Sequel log file will be for your analytics database.

In terms of a default comment for all datasets for a given Database object, the following may work:

DB = Sequel.connect('...')
DB.extension :sql_comments
def DB.dataset
  super.comment('database:analytics')
end
DB.send(:reset_default_dataset)

[janko]

Thanks a lot, I didn't think of just using a separate log file 😃. The example showing a default SQL comments looks reasonable as well.