Inspecting ActiveRecord queries

Imagine a case when you define conditions for how an object performs the SQL query:

if supports_multiple_insert?
  # use bulk INSERT
  execute "INSERT INTO posts (id, title) VALUES (1, 'abc') (2, 'foo')"
else
  # use single INSERT
  execute "INSERT INTO posts (id, title) VALUES (1, 'abc')"
  execute "INSERT INTO posts (id, title) VALUES (2, 'foo')"
end

How would you test this condition? There's no other way than to subscribe to SQL queries and watch them.

Using ActiveRecord you can implement it with a subscriber:

class InsertQueriesSubscriber
  attr_reader :events

  def initialize
    @events = []
  end

  def call(_, _, _, _, values)
    @events << values[:sql] if values[:sql] =~ /INSERT/
  end
end

def test_bulk_insert
  begin
    insert_subscriber = InsertQueriesSubscriber.new
    subscription = ActiveSupport::Notifications.subscribe("sql.active_record", insert_subscriber)

    # perform the operation

    assert_equal 1, insert_subscriber.events.size, "It takes one INSERT query to insert two fixtures"
  ensure
    ActiveSupport::Notifications.unsubscribe(subscription)
  end
end

events will accumulate all INSERT queries and you can assert it. This is actually a real life case from my commit in Rails.

Another example of using subscribers is forbidding specific SQL queries. In our case, we wanted to forbid queries that contain serialized instance of ActionController::Parameters:

class ParamsInsertQuerySubscriber
  # implementation of AC::Parameters is different in Rails 5
  PATTERN = if Rails::VERSION::MAJOR >= 5
    "!ruby/object:ActionController::Parameters"
  else
    "!ruby/hash-with-ivars:ActionController::Parameters"
  end

  def call(_, _, _, _, values)
    sql = values[:sql]
    if sql.starts_with?("INSERT") && sql.include?(PATTERN)
      raise "not allowed to store serialized ActionController::Parameters: #{sql}"
    end
  end
end

When running the app in development or test mode, you may enable the subscriber:

ActiveSupport::Notifications.subscribe('sql.active_record', ParamsInsertQuerySubscriber.new)

You can imagine other use cases of SQL notifications. Please share your ideas in comments!