SqlSpy

A gem to track all SQL queries performed inside a block of code.

With SqlSpy you can test:

• The total amount of queries performed by a piece of code
• The amount of queries performed on a specific table
• The type of these queries: SELECT, INSERT, UPDATE, DELETE
• The query duration
• The SQL produced by your ActiveRecord code
• N+1 queries

The implementation is inspired by how ActiveRecord is tested.

Check out my blog post about writing controller tests with SqlSpy in order to prevent N+1 regressions.

Usage

Add the gem to your Gemfile:

gem"sql_spy"

Install it:

bundle install

Wrap the code you'd like to track inside SqlSpy.track {}:

require"sql_spy"queries=SqlSpy.trackdo# Some code that triggers ActiveRecord queriesend

The SqlSpy.track method will return an array containing all the queries performed inside the block. Every object inside this array exposes the following methods:

• #model_name: The model name (e.g. "User").
• #sql: The SQL query that was performed.
• #duration: The duration of the query in milliseconds.
• #select?: Is this a SELECT query?
• #insert?: Is this an INSERT query?
• #update?: Is this an UPDATE query?
• #delete?: Is this a DELETE query?

Here are some ideas of how you could use this:

# Expect less than 5 queriesassertqueries.count < 5# Expect 1 INSERT queryassert_equal1,queries.select(&:insert?).size# Expect 2 queries to the posts tableassert_equal2,queries.select{ |query| query.model_name == "Post"}.size# None of the queries should be slower than 100msassertqueries.none?{ |query| query.duration > 100}# Fail on N+1 queries: expect at most 1 query per tablequeries_by_model=queries.group_by(&:model_name)assertqueries_by_model.none?{ |model_name,queries| queries.count > 1}

Development

The gem is tested with Postgres 9.6 and SQLite 3.

To run tests with SQLite:

rake

To run tests with Postgres:

# Create a test database
cretedb sql_spy_test
DATABASE_URL=postgres:///sql_spy_test rake

License

The gem is available as open source under the terms of the MIT License.