RSpec matcher to control number of SQL queries executed by a block of code.
It wraps the answer at Stack Overflow by Ryan Bigg, which based on Active Support Notification and Instrumentation mechanisms.
For motivation and details see my blog post "Fighting the Hydra of N+1 queries" in the Martian Chronicles.
# Gemfile
gem "rspec-sqlimit"
The gem defines matcher exceed_query_limit
that takes maximum number of SQL requests to be made inside the block.
require "rspec-sqlimit"
RSpec.describe "N+1 safety" do
it "doesn't send unnecessary requests to db" do
expect { User.create }.not_to exceed_query_limit(1)
end
end
The above specification fails with the following description:
Failure/Error: expect { User.create }.not_to exceed_query_limit(1)
Expected to run maximum 1 queries
The following 3 queries were invoked:
1) begin transaction (0.045 ms)
2) INSERT INTO "users" DEFAULT VALUES (0.19 ms)
3) commit transaction (148.935 ms)
You can restrict the matcher using regex:
require "rspec-sqlimit"
RSpec.describe "N+1 safety" do
it "doesn't send unnecessary requests to db" do
expect { User.create }.not_to exceed_query_limit(1).with(/^INSERT/)
end
end
This time test passes.
When a specification with a restriction fails, you'll see an error as follows:
require "rspec-sqlimit"
RSpec.describe "N+1 safety" do
it "doesn't send unnecessary requests to db" do
expect { User.create }.not_to exceed_query_limit(1).with(/^INSERT/)
end
end
Failure/Error: expect { User.create }.not_to exceed_query_limit(0).with(/INSERT/)
Expected to run maximum 0 queries that match (?-mix:INSERT)
The following 1 queries were invoked among others (see mark ->):
1) begin transaction (0.072 ms)
-> 2) INSERT INTO "users" DEFAULT VALUES (0.368 ms)
3) commit transaction (147.559 ms)
For now the gem uses unbinded Active Record queries in error descriptions. For example, when your query contains arguments, the error message will look like
require "rspec-sqlimit"
RSpec.describe "N+1 safety" do
it "doesn't send unnecessary requests to db" do
expect { User.create(name: "Joe") }.not_to exceed_query_limit(1)
end
end
Failure/Error: expect { User.create }.not_to exceed_query_limit(0).with(/INSERT/)
Expected to run maximum 0 queries that match (?-mix:INSERT)
The following 1 queries were invoked among others (see mark ->):
1) begin transaction (0.072 ms)
-> 2) INSERT INTO "users" ("name") VALUES (?) (0.368 ms)
3) commit transaction (147.559 ms)
This is because Active Record instrumentation hook keeps a query and bindings separately (under :sql
and :binds
keys). So the challenge is to bind arguments to the query in the report to make a debugging a bit simpler.
The gem is available as open source under the terms of the MIT License.