Project

yaasql

0.0
No commit activity in last 3 years
No release in over 3 years
Write db queries directly in SQL - no ORM or query-builder required. Include some conveniences around naming queries and providing arguments.
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
 Dependencies

Development

~> 1.14
~> 5.0
~> 0.18
~> 10.0
 Project Readme

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

Usage

Installing

gem 'yaasql'

Defining Queries

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;

Loading queries

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"}]

Parameterizing Queries

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"}]

Development

Requires a local postgres server for testing.

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

Releasing

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

Building a .gem package

bundle exec rake build

Installing Dev Version Locally

bundle exec rake install

Feature Wishlist

  • 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

License

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