Skip to content
Catch bad SQL queries before they cause problems in production
Branch: master
Clone or download
Latest commit 34e17ea Apr 9, 2019
Type Name Latest commit message Commit time
Failed to load latest commit information.
.travis Use travis, fix bug in fuzzer rounding Feb 15, 2019
bin Use the right option name for ActiveRecord integration Mar 26, 2019
cmd Output a short backtrace instead of appline Feb 1, 2019
data add screenshot Feb 15, 2019
lib 0.9.3 Apr 9, 2019
test Merge pull request #17 from burrito-brothers/ben/more_postgres Apr 5, 2019
web Add global elements to JSON output, add server name Mar 19, 2019
.gitignore Get web building working Mar 13, 2019
.travis.yml More PR test Feb 23, 2019 first commit, bitches Jan 21, 2019
Gemfile how about now Mar 14, 2019
Gemfile.lock 0.9.3 Apr 9, 2019
LICENSE add license Mar 9, 2019 Update README with Postgres and how to use outside of Ruby projects Mar 29, 2019
Rakefile Get web building working Mar 13, 2019
TODO move stuff into Query object Feb 14, 2019
package.json Get web building working Mar 13, 2019
shiba.yml.example add example file Feb 5, 2019

Build Status


Shiba is a tool (currently in alpha) that automatically reviews SQL queries before they cause problems in production. It uses production statistics for realistic query analysis. It catches missing indexes, overly broad indexes, and queries that return too much data.



Install in a Rails / ActiveRecord project using bundler. Note: this gem is not designed to be run on production. It should be required after minitest/rspec.

# Gemfile
gem 'shiba', :group => :test, :require => 'shiba/setup'

If your application lazy loads gems, you will to manually require it.

# config/environments/test.rb or test/test_helper.rb
require 'shiba/setup'


To get started, try out shiba locally. To verify shiba is actually running, you can run your tests with SHIBA_DEBUG=true.

# Install

# Run some tests using to generate a SQL report
rake test:functional
rails test test/controllers/users_controller_test.rb
SHIBA_DEBUG=true ruby test/controllers/users_controller_test.rb

# 1 problematic query detected
# Report available at /tmp/shiba-explain.log-1550099512

Postgres Support

Note: Postgres support is under development. For hopefully reliable results, test tables should have at least 1,000 rows.

Next steps

Going beyond table scans

Without more information, Shiba acts as a simple missed index detector. To catch other problems that can bring down production (or at least cause some performance issues), Shiba requires general statistics about production data, such as the number of rows in a table and how unique columns are.

This information can be obtained by running the bin/dump_stats command in production.

git clone
cd shiba ; bundle
bin/mysql_dump_stats -d DATABASE_NAME -h HOST -u USER -pPASS  > ~/shiba_index.yml

scp production:~/shiba_index.yml RAILS_PROJECT/config

The stats file will look similar to the following:

local$ head <rails_project>/config/shiba_index.yml
  count: 10000
      name: PRIMARY
      - column: id
        rows_per: 1 # one row per unique `id`
      unique: true
      name: index_users_on_email
      - column: email
        rows_per: 1 # one row per email address (also unique)
      unique: true
      name: index_users_on_organization_id
      - column: organization_id
        rows_per: 20% # each organization has, on average, 20% or 2000 users.
      unique: false

Automatic pull request reviews

Shiba can automatically comment on Github pull requests when code changes appear to introduce a query issue. To do this, it will need the Github API token of a user that has access to the repo. Shiba's comments will appear to come from that user, so you'll likely want to setup a bot account on Github with repo access for this. The token can be generated on Github at

Once the token is ready, you can integrate Shiba on your CI server by following these steps:

Travis Integration

On Travis, add this to the after_script setting:

# .travis.yml
 - bundle exec shiba review --submit

Add the Github API token you've generated as an environment variable named SHIBA_GITHUB_TOKEN at{organization}/{repo}/settings.

CircleCI Integration

To integrate with CircleCI, add this after the the test run step in .circleci/config.yml.

# .circleci/config.yml
- run:
   name: Review SQL queries
   command: bundle exec shiba review --submit

An environment variable named SHIBA_GITHUB_TOKEN will need to be configured on CircleCI under Project settings > Environment Variables

Custom CI Integration

To run on other servers, two steps are required:

  1. Ensure an environment variable named CI is set when the tests and shiba script are run.
  2. Run the shiba review command after tests are run, supplying the required arguments to --submit, --token, --branch, and --pull-request. For example:
export CI
rake test
bundle exec shiba review --submit --token $MY_GITHUB_TOKEN --branch $(git rev-parse HEAD) --pull-request $MY_PR_NUMBER

The --submit option tells Shiba to comment on the relevant PR when an issue is found.

Analyze queries from the developer console

For quick analysis, queries can be analyzed from the Rails console.

# rails console
[1] pry(main)> require 'shiba/console'
=> true
[2] pry(main)> shiba User.where(email: "")

Severity: high
Fuzzed Data: Table sizes estimated as follows -- 100000: users
Table Scan: The database reads 100% (100000) of the of the rows in **users**, skipping any indexes.
Results: The database returns 100000 row(s) to the client.
Estimated query time: 3.02s

=> #<Shiba::Console::ExplainRecord:0x00007ffc154e6128>: 'SELECT `users`.* FROM `users` WHERE `users`.`email` = '''. Call the 'help' method on this object for more info.
[3] pry(main)> 

Raw query strings are also supported, e.g. shiba "select * from users where = ''"

Typical query problems

Here are some typical query problems Shiba can detect. We'll assume the following schema:

create_table :users do |t|
  t.string :name
  t.string :email
  # add an organization_id column with an index
  t.references :organization, index: true


Full table scans

The most simple case to detect are queries that don't utilize indexes. While it isn't a problem to scan small tables, often tables will grow large enough where this can become a serious issue.

user = User.where(email: '').limit(1)

Without an index, the database will read every row in the table until it finds one with an email address that matches. By adding an index, the database can perform a quick lookup for the record.

Non selective indexes

Another common case is queries that use an index, and work fine in the average case, but the distribution is non normal. These issues can be hard to track down and often impact large customers.

users = User.where(organization_id: 1)
# => 75

users = User.where(organization_id: 42)
# => 52,000

Normally a query like this would only become a problem as the app grows in popularity. Fixes include adding limit or find_each.

With more data, Shiba can help detect this issue when it appears in a pull request.

Language support

Shiba commands can be used to analyze non Ruby / Rails projects when given a query log file. The log file is a list of queries with the query's backtrace as a SQL comment. The backtrace comment must begin with the word 'shiba' followed by a JSON array of backtrace lines:

SELECT `users`.* FROM `users` WHERE `users`.`email` = '' /*shiba["test/app/app.rb:29:in `<main>'", "Rakefile:0:in `<run>'"]*/
SELECT  `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1 /*shiba["test/app/app.rb:30:in `<main>'", "Rakefile:0:in `<run>'"]*/

The generated log file can then be analyzed after installing Ruby.

gem install bundler

git clone
cd shiba
bin/explain -f query.log --database <DB_NAME> --server mysql --json explain.log.json
bin/review -f explain.log.json

# When no problem queries are found, the command will exit with status 0.
$ echo $?
$ 0
You can’t perform that action at this time.