qdsl

Install gem Build status

qdsl is SQL query builder DSL for Ruby: think of it as "hygienic macros for SQL". Note that this gem is in its very early development stages and is very far from complete. Here are the main talking points:

  • It's most likely riddled with bugs.
  • It is intended to have minimal external dependencies: it doesn't currently depend on any database adapters or anything like that since it is intended to generate only text and query parameter bindings.
  • It will generate PostgreSQL-compatible SQL, since that's what I care about.
  • It is intended to allow clean and straightforward composition of SQL queries and subqueries.
  • It takes a similar approach to other SQL Ruby-embedded DSLs (e.g. SQLDSL) but differs as follows:
    • It attempts to validate the query: column names are currently supported and the intention is to support column types too.
    • It automatically generates table aliases in order to cleanly scope column names and to remove some of the confusion around joining tables with identically named columns.

Installation

Add this line to your application's Gemfile:

gem 'qdsl'

And then execute:

$ bundle

Or install it yourself as:

$ gem install qdsl

Usage

qdsl allows us to build SQL queries in a fairly natural way as a series of Ruby chained method calls. A few examples are given below.

Reference the gem

require 'qdsl'

Define a table

clients = Qdsl::Table.new(
  :clients,
  :id,
  :first_name,
  :last_name
)

This does not connect to a database or anything like that. This is just a way to define a table name and the list of column names from which we can build subsequent queries. Eventually, we might support generating these Table objects from Active Record models, but until that day comes, this is how we define data sources.

Define a query

This is based on the most complex query defined in the gem's test suite:

subquery = Qdsl::select(:id, :first_name, :last_name).
  from(clients)
query = Qdsl::select { |t0, t1| [t0.first_name, t1.last_name] }.
  from(subquery).
  inner_join(subquery).on { |t0, t1| t0.id.equals(t1.id) }.
  where { |t0, t1| t0.first_name.equals('str0').and(t1.last_name.equals('str1')) }

Calling render on the query object query yields SQL that looks something like the following:

SELECT _00.first_name, _01.last_name
FROM (
  SELECT _02.id, _02.first_name, _02.last_name
  FROM clients AS _02
) AS _00
INNER JOIN (
  SELECT _03.id, _03.first_name, _03.last_name
  FROM clients AS _03
) AS _01
ON _00.id = _01.id
WHERE (_00.first_name = :_param00) AND (_01.last_name = :_param01)

The parameters attribute on the Context object populated by render will define the keys _param00 and _param01 with values str0 and str1 respectively. Notice the following:

  • The use of code blocks to perform validation of column names
  • Rudimentary support for boolean expressions
  • Replacement of string literals with query parameters
  • Automatic generation of table aliases in order to avoid column name collisions

Contributing

  1. Fork it from here
  2. Create your feature branch (git checkout -b my-new-feature)
  3. Commit your changes (git commit -am 'Add some feature')
  4. Push to the branch (git push origin my-new-feature)
  5. Create a new pull request