Rspec Query Limit Matcher

Have you ever wondered how many simultaneous queries your application sends to the database? A hundred, a thousand, or perhaps a million? Whatever your answer, we should be aware of the influence that this number has on the performance of our programme. I would like to introduce to you the rspec-query-limit gem (you can find the source code on our github in the monterail/rspec-query-limit repository): this gem is a rspec matcher that measures the numbers of queries being sent to the database in Ruby on Rails applications. The main goal of this gem is to prevent the so-called N+1 problem from occurring, and to make us realize how many different queries are subsequently being sent to the database. Rspec query limit measures queries using Active Support Instrumentation which allows us to measure certain actions in Rails.

You can use this gem wherever you wish in your tests - you can measure the number of queries in your controllers, services, use cases or models, or anywhere in your applications where you communicate with the database. I would like to show you an example use case that will hopefully be to your liking.

Example of usage

Let’s assume that we have a very simple blog application with a DB structure that looks like this:

class Author < ActiveRecord::Base
  has_many :posts
end

class Post < ActiveRecord::Base
  belongs_to :author
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

Taking this example into consideration, let’s create a PostService where we implement the last_posts method which will return an array of last posts with additional information like: number of comments, number of author posts, current post title and author email address. This method will be implemented incorrectly (with an N+1 problem) and because of that we’ll name it last_posts_wrong. Let’s see what the implementation looks like:

class PostService

  def last_posts_wrong(limit)
    Post.where(active: true).last(limit).map do |post|
      {
        post_name: post.title,
        comments_count: post.comments.count,
        last_comment: post.comments.last.content,
        author_email: post.author.email,
        author_articles: post.author.posts.count,
      }
    end
  end

end

A correct implementation of the method last_post in PostService should send 4 queries to the DB:

• Select proper set of posts
• Select post comments
• Select post author information
• Select number of author posts

Let's check it! To do this we can use the matcher query_limit_eq(number), which is provided by the rspec-query-limit gem to measure the number of queries. We will create two test examples with different post limits:

require "spec_helper"

describe PostService do
  let(:number_of_comments) { 30 }
  let!(:comments) { create_list(:comment, number_of_comments) }

  context 'method with N+1 problem' do
    it 'should have 4 queries' do
      expect { PostService.new.last_posts_wrong(10) }.to query_limit_eq(4)
    end

    it 'should have 4 queries' do
      expect { PostService.new.last_posts_wrong(20) }.to query_limit_eq(4)
    end
  end
end

And then we can run tests and the results are:

Failures:

  1) PostService method with N+1 problem should has 4 queries
     Failure/Error: expect { PostService.new.last_posts_wrong(10) }.to query_limit_eq(4)
       Expected to run exactly 4 queries, got 41
     # ./spec/services/post_service_spec.rb:9:in `block (3 levels) in <top (required)>'
     # ./spec/support/database_cleaner.rb:10:in `block (3 levels) in <top (required)>'
     # ./spec/support/database_cleaner.rb:9:in `block (2 levels) in <top (required)>'

  2) PostService method with N+1 problem should has 4 queries
     Failure/Error: expect { PostService.new.last_posts_wrong(20) }.to query_limit_eq(4)
       Expected to run exactly 4 queries, got 81
     # ./spec/services/post_service_spec.rb:13:in `block (3 levels) in <top (required)>'
     # ./spec/support/database_cleaner.rb:10:in `block (3 levels) in <top (required)>'
     # ./spec/support/database_cleaner.rb:9:in `block (2 levels) in <top (required)>'

Both tests fail, just as we expected, and we can see that the received values depend on the post limit number. Behold the N+1 problem! Let’s try to fix it by writing a new method in PostService and naming it last_posts_correct. To achieve our goal, we will include all related associations, and we will change count to size (count sends a new query to the DB). The final results of our refactoring looks like this:

class PostService

...

  def last_posts_correct(limit)
    Post.where(active: true).includes(:comments, author: :posts).last(limit).map do |post|
      {
        post_name: post.title,
        comments_count: post.comments.size,
        last_comment: post.comments.last.content,
        author_email: post.author.email,
        author_articles: post.author.posts.size,
      }
    end
  end

end

Let’s write another set of specs to test our new approach. To make sure that the N+1 problem doesn’t appear, we’ll prepare specs with a different post limit:

  context 'correct method with 30 comments' do
    it 'should have 4 queries' do
      expect { PostService.new.last_posts_correct(10) }.to query_limit_eq(4)
    end

    it 'should have 4 queries' do
      expect { PostService.new.last_posts_correct(20) }.to query_limit_eq(4)
    end
  end

  context 'correct method with 50 comments' do
    let(:number_of_comments) { 50 }
    it 'should have 4 queries' do
      expect { PostService.new.last_posts_correct(10) }.to query_limit_eq(4)
    end

    it 'should have 4 queries' do
      expect { PostService.new.last_posts_correct(20) }.to query_limit_eq(4)
    end
  end

The results of all the specs:

Finished in 3.89 seconds (files took 2.64 seconds to load)
6 examples, 2 failures

Failed examples:

rspec ./spec/services/post_service_spec.rb:8 # PostService method with N+1 problem should has 4 queries
rspec ./spec/services/post_service_spec.rb:12 # PostService method with N+1 problem should has 4 queries

We can see that our new implementation doesn’t depend on the number of records and, as a result, we can always send 4 queries to the DB. In other words, N+1 problem solved!

Summary

I hope that now you can clearly see the importance of having the number of queries under control. When details are changed or improvements are made to your codebase, you can be sure that they won’t cause any N+1 problem and your application performance will still be good and sound. You can avoid many difficulties down the road simply by paying attention to the number of queries being made and by adding proper tests that measure their impact. This gem is not only about the N+1 problem but it’s also about being aware of how many queries we send to the DB in our applications. In my opinion, prevention is better than a cure, therefore I would like to encourage you to write dedicated tests to keep the number of queries no higher than necessary. Hopefully, this gem helps you achieve this goal.