Rails adds ActiveRecord::Relation#annotate for adding SQL comments to ActiveRecord::Relation queries


Rails has added a new method ActiveRecord::Relation#annotate which allows annotating queries generated by ActiveRecord::Relation instance SQL comments.

For eg:

prefix = "Na"
User.where("name LIKE ?", prefix).annotate("users with name starting with #{prefix}").to_sql

=> "SELECT \"users\".* FROM \"users\" WHERE (name LIKE 'Na') /* users with name starting with Na */"

When using annotate the query is generated with the block-style SQL comment.

ActiveRecord::Relation#annotate

  • We can pass multiple arguments to annotate which adds separate blocks.
User.where("name LIKE ?", prefix).annotate("select names", "users with name starting with #{prefix}").select(:name).to_sql

=> "SELECT \"users\".\"name\" FROM \"users\" WHERE (name LIKE 'Na') /* select names */ /* users with name starting with Na */"
  • Multiple comments can also be added by chaining multiple annotate method calls.
User.select(:name).annotate("select names").where("name LIKE ?", prefix).annotate("users with name starting with #{prefix}").to_sql

=> "SELECT \"users\".\"name\" FROM \"users\" WHERE (name LIKE 'Na') /* select names */ /* users with name starting with Na */"
  • annotate used in associations.
has_many :comments, -> { annotate("user comments") }

user.comments.to_sql
=> "SELECT \"comments\".* FROM \"comments\" WHERE \"comments\".\"user_id\" = 1 /* users comments */"
  • annotate in scopes.
scope :name_starts_with, ->(prefix) {
  where("name like ?", prefix).annotate("user name starting with #{prefix}")
}

User.name_starts_with("Na")
=> "SELECT \"users\".* FROM \"users\" WHERE (name like 'Na') /* user name starting with Na */"
  • annotate in scoping
User.annotate("scoped").scoping do
  User.all.to_sql
end
=> "SELECT \"users\".* FROM \"users\" /* scoped */"

This feature can be very useful when combined with Rails instrumentation or other ways of tracking and analyzing SQL queries generated at runtime in Rails applications.

Although there are other tools or gems that provide similar functionality in some contexts, they work on annotating all queries in request or job. Using annotate we can annotate specific scopes or associations.