DEV Community

Masataka Pocke Kuwabara
Masataka Pocke Kuwabara

Posted on • Edited on

ActiveRecord::Originator, a RubyGem indicating the origin of the SQL

This article is translated from a Japanese article written by me.


Hello, I'm Pocke.

Today, I created a gem called activerecord-originator, and I'd like to introduce it to you.

GitHub logo pocke / activerecord-originator

A RubyGem adding SQL comments to indicate the origin of the SQL

ActiveRecord::Originator

Add SQL comments to indicate the origin of the SQL.

This gem adds SQL comments indicating the origin of the part of the query. This is useful for debugging large queries.

Rails tells us where the SQL is executed, but it doesn't tell us where the SQL is constructed This gem lets you know where the SQL is constructed! For example:

Article Load (0.1ms)  SELECT "articles".* FROM "articles" WHERE "articles"."status" = ? /* app/models/article.rb:3:in `published' */
 AND "articles"."category_id" = ? /* app/controllers/articles_controller.rb:3:in `index' */
 ORDER BY "articles"."created_at" DESC /* app/models/article.rb:4:in `order_by_latest' */
Enter fullscreen mode Exit fullscreen mode

You can see where .where and .order methods are called without reading the source code. It is helpful if the query builder is complex.

Installation

Install the gem and add to…

What is this?

This gem inserts comments into each part of the SQL queried by Active Record, indicating where it was constructed.

To understand, it's faster to see an example. The following log is an example of a query executed in ArticlesController#index.

Article Load (0.1ms)  SELECT "articles".* FROM "articles" WHERE "articles"."status" = ? /* app/models/article.rb:3:in `published' */
 AND "articles"."category_id" = ? /* app/controllers/articles_controller.rb:3:in `index' */
 ORDER BY "articles"."created_at" DESC /* app/models/article.rb:4:in `order_by_latest' */
Enter fullscreen mode Exit fullscreen mode

This gem adds the comments enclosed in /* ... */. From this, you can see that articles'.' status" = ? originates from the published method on line 3 of article.rb, that category_id is filtered in the controller, and where the scope that defines the ORDER BY is located.

Motivation

The simple example I mentioned earlier probably doesn't tell you this gem's advantage.

However, as SQL assembly becomes more complex, this gem will prove its true value. It's common for the location where a query is executed and the location where it's constructed to be far apart, such as when defining scopes. Moreover, when creating classes to construct queries for complex searches, searching within those classes can be quite a task.
With this gem, you can pinpoint how the query was constructed, making debugging easier.

It can also be useful for debugging default_scope because it is difficult to understand where they were applied. 1

Usage

Run the following command to install the activerecord-originator gem.

$ bundle add activerecord-originator
Enter fullscreen mode Exit fullscreen mode

That's all for the setup. Just by installing the gem, comments will be added to the queries.

Alternative Solutions

Similar gems include marginalia and activerecord-cause. Also, from Rails 7, a similar feature to marginalia has been standardly equipped in Rails.

The major difference between these traditional features and activerecord-originator is the granularity of comments (or logs). With traditional features, comments are output on a per-query basis. It's very convenient to know where that query was executed, but you couldn't get more detailed information.

activerecord-originator outputs comments for each element of SQL, conveying more detailed information. Information that was previously unknown becomes clear, increasing the information available for debugging.

The activerecord-originator is not intended to replace traditional features but is used in conjunction with them.

Caution

There are two things to be aware of when using activerecord-originator. One is the use of Active Record's internal API, and the other is performance.

Active Record Internal API

This gem strongly depends on Arel, which is an internal API of Active Record. Therefore, it may easily break with future Rails updates.
For example, it could break due to major internal refactoring.

It may also not work well with older versions of Rails. Although I have confirmed that the tests pass for Active Record v6.0 with some exceptions, and all tests pass for v6.1, there is no guarantee that older versions will continue to be supported.

Since it's a gem that can be easily installed and uninstalled, it's good to think that you might need to remove it from the Gemfile when updating Rails.

Performance

Since processing to output comments is performed every time a query is constructed, it is expected to incur a considerable cost.
Therefore, enabling it in a production environment could affect performance.

However, I haven't done any benchmarking or anything yet, so I don't know how much impact it will actually have.
If you measure the actual impact, I would be happy if you could let me know.

Implementation

Finally, I'll briefly introduce the implementation.

First, this gem prepends the ActiveRecord::Originator::ArelNodeExtension module to all descendant classes of Arel::Nodes::Node (I'll post the entire text below because it's short).
https://github.com/pocke/activerecord-originator/blob/v0.1.0/lib/activerecord/originator/arel_node_extension.rb

module ActiveRecord
  module Originator
    module ArelNodeExtension
      def initialize(...)
        __skip__ = super
        @ar_originator_backtrace = caller
      end

      attr_reader :ar_originator_backtrace
    end
  end
end
Enter fullscreen mode Exit fullscreen mode

With this module, all nodes record their creation location (== the location where methods like where were called).

Another module, ActiveRecord::Originator::ArelVisitorExtension, is prepended to the Arel::Visitors::ToSql class.
https://github.com/pocke/activerecord-originator/blob/v0.1.0/lib/activerecord/originator/arel_visitor_extension.rb

The ToSql class traverses the Arel AST to generate SQL strings. The ArelVisitorExtension module overrides the methods corresponding to each node class in the ToSql class, inserting comments based on the location information recorded by ArelNodeExtension.

These two modules are the core of the implementation. The implementation itself is not very difficult, but it has become heavily dependent on Rails' internal API.

This was an introduction to the activerecord-originator gem, which makes SQL executed more debuggable. I would be happy if you could try it out.

And another module, ActiveRecord::Originator::ArelVisitorExtension, is prepended to the Arel::Visitors::ToSql class.

[https://github.com/pocke/activerecord-originator/blob/v0.1.0/lib/activerecord/originator/arel_visitor_extension.rb]

The ToSql class is a class that traverses the Arel AST to generate SQL strings. The ArelVisitorExtension module overrides the methods corresponding to each node in the ToSql class, inserting comments based on the location information recorded by ArelNodeExtension.

These two modules are the core of the implementation.
The implementation itself is not very difficult, but it has become heavily dependent on Rails' internal API.


This was an introduction to the activerecord-originator gem, which makes SQL executed more debuggable. I would be happy if you could try it out.


  1. The idea for this gem came to me when I saw someone struggling to identify where a condition defined by default_scope was coming from, as it was not clear. 

Top comments (0)