I recently discovered a handy feature introduced in Rails 6. This feature allows you to annotate your SQL queries with comments directly embedded in the query itself. I know, I’m a bit late to the party, but I suspect I’m not the only one, so here’s a quick rundown.

In our app, we have a deeply nested hash called public_data that’s often used to render a record’s JSON payload, along with its associations, in the controller.

This public_data hash is built from a mix of attributes and model methods. However, it also calls the public_data methods of associated models, which might, in turn, call back to the original model’s public_data. This circular complexity has been a challenge, and while we’ve started transitioning to the Barley serializer gem for better structure, we still rely on this method in parts of our codebase for backward compatibility.

So when @user.public_data is called in the controller, it often triggers a long chain of database queries. Tracking down where a specific relation is being called—whether in User, Profile, or Subscription—can be daunting.

While Rails logs are helpful, they require sifting through the output to correlate the log entry with the SQL query. This becomes even trickier when debugging in remote environments and searching through extensive logs in tools like New Relic or CloudWatch.

Enter ActiveRecord::Relation#annotate…

This simple method lets you add comments to your SQL queries, embedding contextual information directly within the query.

Here’s how it works:

users = User.active.where(score: ..5).annotate("called from Admin::UsersController")

#> SELECT * FROM `users` WHERE `last_visit_at` >= '2024-11-10 16:53:05'
#  AND `score` <= 5 /* called from Admin::UsersController */ ORDER BY `id` ASC

This provides a clear indication of where the query originated. You can also include additional context, such as the current locale, user, or order ID, depending on your needs.

For example, adding an annotation inside the public_data method on my Subscription model makes it much easier to trace where the query originated in the chain.

Another great feature is the ability to stack annotations. Let’s say we add an annotation in the active scope of the User model:

class User < ApplicationRecord
  scope :active, -> { where(last_visit_at: 1.week.ago..).annotate("user active in the past week") }
end

Calling the same query as before would generate:

users = User.active.where(score: ..5).annotate("called from Admin::UsersController")

#> SELECT * FROM `users` WHERE `last_visit_at` >= '2024-11-10 16:53:05'
#  AND `score` <= 5 /* user active in the past week */ /* called from Admin::UsersController */
#  ORDER BY `id` ASC

Neat, right? This approach brings more clarity and structure to your logs, making them easier to query and group in log viewers. Additionally, these annotations appear in your database logs, offering a seamless way to correlate them with application logs.

Possible applications include:

  • Tracking queries in background jobs
  • Tagging controller context, headers, or anything else inside the queries
  • Debugging N+1 or slow queries
  • Expliciting the scope of queries
  • And so much more creative stuff!

That said, annotations come with a slight tradeoff in query readability. It’s essential to be deliberate about what and when to log, and to periodically review whether the annotations remain relevant and valuable.

Even if used exclusively for local debugging, SQL annotations are an excellent addition to your toolbox.