Yaasql

Basic SQL-templating library inspired by https://github.com/krisajenkins/yesql.

You can read more about the rationale for a library like this in Yesql's Readme

gem 'yaasql'

Create a .sql file to define your queries.

Queries must be identified with a comment line each query a name. This name will become the name of the Ruby method yaasql eventually creates for your query.

You may have multiple queries per file.

-- name: count_examples
SELECT COUNT(*) FROM examples;

require 'yaasql/db'
require 'pg'

class MyDB
  DB_CONN = PG.connect(DB_URL)
  extend Yaasql::DB
  define_queries("./queries.sql", DB_CONN)
end

MyDB.new.count_examples
# => [{"count" => "3"}]

Words with a leading : in query files will be interpreted as arguments to the query.

-- name: get_example_by_id
SELECT * FROM examples where id = :id limit 1;

-- name: get_examples_by_id
SELECT * FROM examples where id =ANY(:ids);

Arguments can then be provided as a symbol-keyed hash when querying the function:

db = MyDB.new

db.get_example_by_id({id: 1})
# => [{"id" => "1", "name" => "example 1"}]

db.get_examples_by_id({ids: [1,2,3]})
# => [{"id" => "1", "name" => "example 1"}, {"id" => "2", "name" => "example 2"}]

Requires a local postgres server for testing.

bundle install
rake setup # creates the test postgres DB
rake # runs tests

• Builds the .gem package
• Creates and pushes a git tag for the current version
• Pushes the .gem to rubygems.org

bundle exec rake release

bundle exec rake build

bundle exec rake install

• Reading multiple queries from single string (blank-line separated?)
• Module for including into a namespace
• Metaprogramming for defining query methods
• Define queries from a file
• Support array queries with =ANY()
• where IN (...) queries
• Option for stdout / stdin redirection (to support COPY FROM / COPY TO queries)
• Option for streaming queries to process results row-by-row (e.g. for large datasets)
• Support positional ? arguments

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