QuerySpy for counting ActiveRecord queries, asserting against n+1 regressions.

query_spy.rb

# An SQL query spy derived from the perhaps deprecated SQLCounter
# in ActiveRecord::TestCase and/or activerecord/test/cases/helper.rb
class QuerySpy

  # Returns the number of queries executed by yielding the given block.
  # Examples:
  #
  #   QuerySpy.count { get "/things" }.should == 1
  #
  #   create_a_thing
  #   count1 = QuerySpy.count { get "/things" }
  #   create_a_thing
  #   count2 = QuerySpy.count { get "/things" }
  #   count2.should <= count1
  #
  def self.count
    spy = self.new
    ActiveSupport::Notifications.subscribe("sql.active_record", spy)
    yield
    ActiveSupport::Notifications.unsubscribe(spy)
    spy.log.length
  end

  # Ignore queries for PostgreSQL.
  # See ActiveRecord::TestCase and/or activerecord/test/cases/helper.rb
  # for other database systems.
  IGNORE = Regexp.union([
    /^PRAGMA (?!(table_info))/,
    /^SELECT currval/,
    /^SELECT CAST/,
    /^SELECT @@IDENTITY/,
    /^SELECT @@ROWCOUNT/,
    /^SAVEPOINT/,
    /^ROLLBACK TO SAVEPOINT/,
    /^RELEASE SAVEPOINT/,
    /^SHOW max_identifier_length/,
    /^BEGIN/,
    /^COMMIT/,
    /^\s*select\b.*\bfrom\b.*pg_namespace\b/im,
    /^\s*select\b.*\battname\b.*\bfrom\b.*\bpg_attribute\b/im
  ])

  def initialize
    @log = []
  end

  attr_reader :log

  def call(name, start, finish, message_id, values)
    sql = values[:sql]
    return if 'CACHE' == values[:name]
    return if IGNORE =~ sql
    @log << sql
  end
end

z_example_spec.rb

require "spec_helper"

describe "Number of queries when listing Things" do

  # Create a Thing, with various sub-things that should
  # be eager-loaded by the listing page.
  def add_thing
    create(:thing_with_various_sub_things)
  end

  it "should not exhibit n+1" do
    add_thing
    count1 = QuerySpy.count { get "/things" }
    add_thing
    count2 = QuerySpy.count { get "/things" }
    count2.should <= count1
  end

end