Proposal: Add Configuration Option to Prepend SQL Comments in ActiveRecord

rubyonrails-core
1

Hello everyone,

We use AWS Aurora’s MySQL performance insights to gather and export detailed query insights. To enhance our database monitoring, we add metadata (tags) as SQL comments to SQL queries. These comments are used to track and trace queries within DataDog, providing critical information for performance analysis. The current mechanism in ActiveRecord appends SQL comments to the end of the query (using the annotate method on ActiveRecord::Relation objects). However, this approach has created a significant challenge due to how MySQL’s performance schema truncates queries longer than 1024 characters, which is not uncommon with complex SQL queries.

Problem:

  • When a SQL query exceeds 1024 characters, any appended comments are discarded because of MySQL’s truncation limits.
  • This results in the loss of essential metadata needed for traceability and monitoring in DataDog.

Proposed Solution:

  • To address this, we propose introducing a configuration option in ActiveRecord to prepend SQL comments to the beginning of queries rather than appending them. By prepending comments, we ensure that even if a query is truncated, the essential metadata remains intact, allowing for complete tracking and analysis in DataDog.

Technical Implementation:

  • We have implemented this functionality by introducing an option prepend_annotations in ActiveRecord, which can be toggled to switch between appending and prepending SQL comments.
  • This feature leverages the following components:
    • A new setting in the ActiveRecord module (ActiveRecord.prepend_annotations).
    • Changes to ActiveRecord::ConnectionAdapters::AbstractAdapter and Arel::Visitors::ToSql to respect the setting when building SQL queries, by checking the configuration object when visiting SelectCore node.
    • An initializer to configure this option in Rails applications based on the environment configuration.

Why This Matters:

  • This change is particularly relevant for applications with complex queries or large datasets, where exceeding 1024 characters in a query is common.
  • Without this change, metadata used for tracking query performance is at risk of being lost, severely limiting the ability to monitor, debug, and optimize SQL performance in DataDog.
  • By introducing this option, Rails will provide better support for use cases where the truncation of long queries affects essential monitoring tools.

I would be happy to share more details about the implementation or discuss alternative approaches. Please let me know if this sounds like a valuable addition to Rails, or if you have any feedback or suggestions.

Best regards,

Aref Aslani

2

Please check https://api.rubyonrails.org/classes/ActiveRecord/QueryLogs.html

Rafael França Rails Core Team Member

3

Hi @rafaelfranca,

thanks for the hint! I played around with the query logs a little bit, and that’s exactly what I was looking for. I wish there were an option to choose custom formatters, like JSONFormatter, for example. For our project, I can monkey-patch the ActiveRecord::QueryLogging module like this:

# frozen_string_literal: true

require "active_record/railtie"

module ActiveRecord
  module QueryLogs
    module JSONFormatter
      class << self
        # Formats the key-value pairs into a string.
        def format(key, value)
          { key => value }
        end

        def join(pairs)
          pairs.reduce({}, :merge).to_json
        end
      end
    end

    class << self
      def tags_formatter=(format) # :nodoc:
        @formatter = case format
        when :legacy
          LegacyFormatter
        when :sqlcommenter
          SQLCommenter
        when :json
          JSONFormatter
        else
          raise ArgumentError, "Unsupported formatter: #{format}"
        end
        @tags_formatter = format
      end
    end
  end
end

But it would be nice to allow it to accept custom formatters as well. Do you think it would be a good addition to ActiveRecord if I try to add it?

Kind regards,

Aref

4

There is also one more problem with ActiveRecord::QueryLogs. I’m not sure if it’s possible to set specific tags for specific SQL queries. For example, let’s say, I would like to add method_name tag on each SQL query, showing in which method of which class of the application those queries are generated. For example in the context of the same request, one query gets generated in SomeService#perform and another query in AnotherService#perform. Is this something we can implement using ActiveRecord::QueryLogs?