A database is the heart of many applications, and having problems with it may result in serious performance issues.

ORMs such as ActiveRecord and Mongoid help us abstract implementation and deliver code faster, but sometimes, we forget to check what queries are running under the hood.

The bullet gem helps us identify some well-known database-related problems:

1. "N+1 Queries": when the application runs a query to load each item of a list
2. "Unused Eager Loading": when the application loads data, usually to avoid N+1 queries, but doesn't use it
3. "Missing Counter Cache": when the application needs to execute count queries to get the number of associated items

In this post, I'm going to show:

• how to configure the bullet gem in a Ruby project,
• examples of each problem mentioned before,
• how bullet detects each,
• how to fix each problem, and
• how to integrate bullet with AppSignal.

I will use some examples from a project that I created for this post.

How to Configure Bullet in a Ruby Project

First, add the gem to Gemfile.

We can add it to all environments given, we can enable or disable it and use a different approach on each one:

gem 'bullet'

Next, it's necessary to configure it.

If you are in a Rails project, you can run the following command to generate the configuration code automatically:

bundle exec rails g bullet:install

If you are in a non Rails project, you can add it manually, for example, by adding the following code in spec_helper.rb after loading the application's code:

Bullet.enable        = true
Bullet.bullet_logger = true
Bullet.raise         = true

And adding the following code in the main file after loading the application's code:

Bullet.enable = true

I'm going to share more details on configurations in this post. If you want to see them all, go to bullet's README page.

Using bullet In Tests

With the previously suggested configuration, Bullet will detect bad queries executed in tests and raise exceptions for them.

Now, let's see some examples.

Detecting N+1 Queries

Given an index action as follows:

# app/controllers/posts_controller.rb
class PostsController < ApplicationController
  def index
    @posts = Post.all
  end
end

And a view like this:

# app/views/posts/index.html.erb
 
<h1>Posts</h1>
 
<table>
  <thead>
    <tr>
      <th>Name</th>
      <th>Comments</th>
    </tr>
  </thead>
 
  <tbody>
    <% @posts.each do |post| %>
    <tr>
      <td><%= post.name %></td>
      <td><%= post.comments.map(&:name) %></td>
    </tr>
    <% end %>
  </tbody>
</table>

bullet will raise an error detecting an "N+1" when running an integrated test that executes code from the view and the controller, for example, using a request spec as follows:

# spec/requests/posts_request_spec.rb
require 'rails_helper'
 
RSpec.describe "Posts", type: :request do
  describe "GET /index" do
    it 'lists all posts' do
      post1 = Post.create!
      post2 = Post.create!
 
      get '/posts'
 
      expect(response.status).to eq(200)
    end
  end
end

In this case, it will raise this exception:

Failures:

  1) Posts GET /index lists all posts
     Failure/Error: get '/posts'

     Bullet::Notification::UnoptimizedQueryError:
       user: fabioperrella
       GET /posts
       USE eager loading detected
         Post => [:comments]
         Add to your query: .includes([:comments])
       Call stack
         /Users/fabioperrella/projects/bullet-test/app/views/posts/index.html.erb:17:in `map'
         ...
     # ./spec/requests/posts_controller_spec.rb:9:in `block (3 levels) in <top (required)>'

This happens because the view is executing one query to load each comment name in post.comments.map(&:name):

Processing by PostsController#index as HTML
  Post Load (0.4ms)  SELECT "posts".* FROM "posts"
  ↳ app/views/posts/index.html.erb:14
  Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ?  [["post_id", 1]]
  ↳ app/views/posts/index.html.erb:17:in `map'
  Comment Load (0.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = ?  [["post_id", 2]]

To fix it, we can simply follow the instruction in the error message and add .includes([:comments]) to the query:

-@posts = Post.all
+@posts = Post.all.includes([:comments])

This will instruct ActiveRecord to load all the comments with only 1 query.

Processing by PostsController#index as HTML
  Post Load (0.2ms)  SELECT "posts".* FROM "posts"
  ↳ app/views/posts/index.html.erb:14
  Comment Load (0.0ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (?, ?)  [["post_id", 1], ["post_id", 2]]
  ↳ app/views/posts/index.html.erb:14

However, bullet will not raise an exception in a controller test like the following, because controller tests don't render views by default, so the N+1 query will not be triggered.

Note: controller tests are discouraged since Rails 5:

# spec/controllers/posts_controller_spec.rb
require 'rails_helper'
 
RSpec.describe PostsController do
  describe 'GET index' do
    it 'lists all posts' do
      post1 = Post.create!
      post2 = Post.create!
 
      get :index
 
      expect(response.status).to eq(200)
    end
  end
end

Another example of a test that Bullet will not detect an "N+1" is a view test because, in this case, it will not run the N+1 queries in the database:

# spec/views/posts/index.html.erb_spec.rb
require 'rails_helper'
 
describe "posts/index.html.erb" do
  it 'lists all posts' do
    post1 = Post.create!(name: 'post1')
    post2 = Post.create!(name: 'post2')
 
    assign(:posts, [post1, post2])
 
    render
 
    expect(rendered).to include('post1')
    expect(rendered).to include('post2')
  end
end

A Tip to Have More Chances to Detect an N+1 in Tests

I recommend creating at least 1 request spec for each controller action, just to test if it returns the correct HTTP status, then bullet will be watching the queries when rendering these views.

Detecting Unused Eager Loading

Given the following basic_index action:

# app/controllers/posts_controller.rb
class PostsController < ApplicationController
  def basic_index
    @posts = Post.all.includes(:comments)
  end
end

And the following basic_index view:

# app/views/posts/basic_index.html.erb
 
<h1>Posts</h1>
 
<table>
  <thead>
    <tr>
      <th>Name</th>
    </tr>
  </thead>
 
  <tbody>
    <% @posts.each do |post| %>
    <tr>
      <td><%= post.name %></td>
    </tr>
    <% end %>
  </tbody>
</table>

When we run the following test:

# spec/requests/posts_request_spec.rb
require 'rails_helper'
 
RSpec.describe "Posts", type: :request do
  describe "GET /basic_index" do
    it 'lists all posts' do
      post1 = Post.create!
      post2 = Post.create!
 
      get '/posts/basic_index'
 
      expect(response.status).to eq(200)
    end
  end
end

Bullet will raise the following error:

  1) Posts GET /basic_index lists all posts
     Failure/Error: get '/posts/basic_index'

     Bullet::Notification::UnoptimizedQueryError:
       user: fabioperrella
       GET /posts/basic_index
       AVOID eager loading detected
         Post => [:comments]
         Remove from your query: .includes([:comments])
       Call stack
         /Users/fabioperrella/projects/bullet-test/spec/requests/posts_request_spec.rb:20:in `block (3 levels) in <top (required)>'

This happens because it's not necessary to load the list of comments for this view.

To fix the problem, we can simply follow the instruction in the error above and remove the query .includes([:comments]):

-@posts = Post.all.includes(:comments)
+@posts = Post.all

It's worth saying that it will not raise the same error if we run only a controller test, without render_views, as shown before.

Detecting Missing Counter Cache

Given a controller like this:

# app/controllers/posts_controller.rb
class PostsController < ApplicationController
  def index_with_counter
    @posts = Post.all
  end
end

And a view like this:

# app/views/posts/index_with_counter.html.erb
 
<h1>Posts</h1>
 
<table>
  <thead>
    <tr>
      <th>Name</th>
      <th>Number of comments</th>
    </tr>
  </thead>
 
  <tbody>
    <% @posts.each do |post| %>
    <tr>
      <td><%= post.name %></td>
      <td><%= post.comments.size %></td>
    </tr>
    <% end %>
  </tbody>
</table>

If we run the following request spec:

describe "GET /index_with_counter" do
  it 'lists all posts' do
    post1 = Post.create!
    post2 = Post.create!
 
    get '/posts/index_with_counter'
 
    expect(response.status).to eq(200)
  end
end

bullet will raise the following error:

1) Posts GET /index_with_counter lists all posts
  Failure/Error: get '/posts/index_with_counter'

  Bullet::Notification::UnoptimizedQueryError:
    user: fabioperrella
    GET /posts/index_with_counter
    Need Counter Cache
      Post => [:comments]
  # ./spec/requests/posts_request_spec.rb:31:in `block (3 levels) in <top (required)>'

This happens because this view is executing 1 query to count the number of comments in post.comments.size for each post.

Processing by PostsController#index_with_counter as HTML
  ↳ app/views/posts/index_with_counter.html.erb:14
  Post Load (0.4ms)  SELECT "posts".* FROM "posts"
  ↳ app/views/posts/index_with_counter.html.erb:14
   (0.4ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = ?  [["post_id", 1]]
  ↳ app/views/posts/index_with_counter.html.erb:17
   (0.1ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = ?  [["post_id", 2]]

To fix this, we can create a counter cache, which can be a bit complex, especially if there is data in the production database.

A counter cache is a column that we can add to a table, that ActiveRecord will update automatically when we insert and delete associated models. There are more details in this post. I suggest reading it to know how to create and sync the counter cache.

Using Bullet in Development

Sometimes, tests might not detect the problems previously mentioned, for example, if test coverage is low, so it's possible to enable bullet in other environments using different approaches.

In the development environment, we can enable the following configurations:

Bullet.alert         = true

Then, it will show alerts like this in the browser:

Bullet.add_footer    = true

It will add a footer on the page with the error:

It's also possible to enable errors to be logged in the browser's console:

Bullet.console    = true

It will add an error like this:

Using Bullet in Staging with Appsignal

In the staging environment, we don't want these error messages to be shown to end-users, but it would be great to know if the application starts to have one of the problems mentioned previously.

At the same time, bullet may degrade performance and increase memory consumption in the application, so it's better to enable it only temporarily in staging, but don't enable it in production.

Assuming the staging environment is using the same configuration file as the production environment, which is a good practice to reduce the difference between them, we can use an environment variable to enable or disable bullet as follows:

# config/environments/production.rb
config.after_initialize do
  Bullet.enabled   = ENV.fetch('BULLET_ENABLED', false)
  Bullet.appsignal = true
end

To receive notifications about issues Bullet has found in your staging environment, you can use AppSignal to report those notifications as errors. You'll need to have the appsignal gem installed and configured in your project. You can see more details in the Ruby gem docs.

Then, if a problem is detected by bullet, it will create an error incident like this:

This error is raised by the uniform_notifier gem which was extracted from bullet.

Unfortunately, the error message doesn't show enough information, but I sent in a Pull Request to improve this!

Conclusion

The bullet gem is a great tool that can help us detect problems that will degrade performance in applications.

Try to keep good test coverage, as previously mentioned, to have greater chances of detecting these problems before going to production.

As an extra tip, if you want to be even more protected against performance problems related to the database, take a look at the wt-activerecord-index-spy gem, which helps to detect queries that are not using proper indexes.

P.S. If you'd like to read Ruby Magic posts as soon as they get off the press, subscribe to our Ruby Magic newsletter and never miss a single post!