SQLRunner

SQLRunner allows you to load your queries out of SQL files, without using ORMs. Available for PostgreSQL and MySQL.

Installation

Add this line to your application's Gemfile:

gem "sql_runner"

And then execute:

$ bundle

Or install it yourself as:

$ gem install sql_runner

Usage

Run raw queries:

SQLRunner.connect("postgresql:///test?application_name=myapp")

SQLRunner.execute("SELECT 1")
#=> <PG:Result:0x007fdf8d3475f0>

SQLRunner.execute("SELECT 1").values
#=> [["1"]]

SQLRunner.execute("SELECT :number::integer", number: 1).values
#=> [["1"]]

Run SQL files:

SQLRunner.root_dir = "#{__dir__}/sql"

class GetMembers < SQLRunner::Query
  # by default will use root_dir/get_members.{sql,psql}
end

Specify custom SQL:

class GetMembers < SQLRunner::Query
  query "SELECT * FROM members ORDER BY created_at"
end

Specify custom connection:

class GetMembers < SQLRunner::Query
  connect "postgresql:///another_database"
end

Specify other options:

class GetMembers < SQLRunner::Query
  query_name "users" #=> will load root_dir/users.{psql,sql}
  root_dir "/some/path"
end

You can use this with ActiveRecord as well. To make it work, all you need to do is establishing the connection using activerecord:///:

require "active_record"

# You probably won't need this if you're using Rails.
ActiveRecord::Base.establish_connection("postgresql:///database")

# Set the adapter to be based on ActiveRecord.
SQLRunner.connect "activerecord:///"

SQLRunner.execute "SELECT 1"
#=> <PG:Result:0x008adf4d5495b0>

Plugins

Load just one record

class FindUserByEmail < SQLRunner::Query
  plugin :one
  query "SELECT * FROM users WHERE email = :email LIMIT 1"
end

FindUserByEmail.call(email: "john@example.com")
#=> {"id" => 1, "email" => "john@example.com"}

FindUserByEmail.call(email: "invalid")
#=> nil

FindUserByEmail.call!(email: "invalid")
#=> raise exception SQLRunner::RecordNotFound

Use a model

require "virtus"

class UserModel
  include Virtus.model

  attribute :id, String
  attribute :email, String
  attribute :name, String
end

class FindUserByEmail < SQLRunner::Query
  plugin :one
  plugin model: UserModel
  query "SELECT * FROM users WHERE email = :email LIMIT 1"
end

FindUserByEmail.call(email: "john@example.com")
#=> <UserModel:0x007fdf8c2c1280>

Avoid calling .to_a in collections

class FindUsers < SQLRunner::Query
  plugin :many
  query "SELECT * FROM users"
end

FindUsers.call
#=> [{"id" => "1", "email" => "john@example.com"}]

Adding new plugins

First you have to create a class/module that implements the .activate(target, options) class method. The following example overrides the call(**bind_vars) method by using Module.prepend.

module ReverseRecords
  def self.activate(target, options)
    target.singleton_class.prepend self
  end

  def call(**bind_vars)
    super(**bind_vars).to_a.reverse
  end
end

Register the plugin.

SQLRunner::Query.register_plugin :reverse, ReverseRecords

class Users < SQLRunner::Query
  query "SELECT * FROM users ORDER BY created_at ASC"
  plugin :reverse
end

Users.call

If your plugin can receive options, you can call it as plugin reverse: options, where options can be anything (e.g. Hash, Array, Object, etc).

Benchmarks

You won't gain too much performance by using this gem. The idea is making SQL easier to read by extracting complex stuff to their own files. These are the results against ActiveRecord using different wrapping libraries like virtus and dry-types.

Loading just one record:

  sql_runner - find one (raw)      :     5518.6 i/s
  sql_runner - find one (dry-types):     5015.4 i/s - same-ish: difference falls within error
  sql_runner - find one (virtus)   :     4746.2 i/s - 1.16x slower
activerecord - find one            :     3468.5 i/s - 1.59x slower

Loading several records:

  sql_runner - find many (raw)      :     6808.2 i/s
  sql_runner - find many (dry-types):     5251.4 i/s - same-ish: difference falls within error
  sql_runner - find many (virtus)   :     4145.6 i/s - 1.64x slower
activerecord - find many            :     2731.5 i/s - 2.49x slower

Development

After checking out the repo, run bin/setup to install dependencies. Then, run rake test to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/fnando/sql_runner. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.

License

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