sql_builder

SqlBuilder

SqlBuilder is a small, simple Ruby library that lets you compose SQL queries using a fluent, builder syntax.

This is not an ORM and does require any schema definition or mapping. You simply specify a query and SqlBuilder will generate an ad-hoc, read-only Ruby class to represent the result.

Installation

Add this line to your application's Gemfile:

gem 'sql_builder', git: '[e-mail]:Terrier-Tech/sql_builder.git'

And then execute:

$ bundle

Usage

SqlBuilder uses a chainable builder syntax to make constructing queries more flexible than using plain strings. It also automatically handles things like applying prefixes to column names and managing aliases.

# sql builder example
locs = SqlBuilder.new
  .select(%w(city state), 'loc', 'location_') # columns, table alias, prefix
  .select(%w(first_name last_name), 'tech', 'technician_') 
  .from('locations', 'loc') # table name, alias
  .left_join('users', 'tech', 'tech.id = loc.primary_technician_id') # table name, alias, clause
  .where("loc.zip = ?", '55124') # plain string clause
  .where("tech.first_name = ?", 'Billy')
  .exec

# returns an array of POROs with attributes location_city, location_state, 
# technician_first_name, technician_last_name
locs.each do |loc|
  puts "#{loc.technician_first_name} in #{loc.location_city}" 
end

Attribute types will be automatically inferred from the names, including converting from cents to dollars.

Since SqlBuilder stores all of the components of the query and is a plain Ruby object, it can also be composed conditionally:

builder = SqlBuilder.new
  .select(%w(first_name last_name), 'tech')
  .from('users', 'tech')
  .where('tech._state = 0')
  .where("tech.role = ?", 'technician')

if branch_id
  builder.where("tech.branch_id = ?", branch_id)
end

techs = builder.exec

Important Notes

First, SqlBuilder now sanitizes input!, this utilizes ActiveRecord's(v4.2) sanitize_sql_array method to do the heavy lifting. You may opt for a more recent version of ActiveRecord in which case it will use the sanitize method.

Second, SqlBuilder assumes you have ActiveRecord around in order to use SqlBuilder#exec. If this isn't the case, you can always get the raw SQL using SqlBuilder#to_sql.

Joins

SqlBuilder offers 4 join commands left_join, inner_join, outer_join, and right_join. Any of these can take one of three combinations of arguments (all strings):

# base_query
inspection_items = SqlBuilder.new
    .select('inspection_items.*')
    
# the following three examples will add the following join clause: INNER JOIN work_orders AS wo ON wo.id = inspection_items.work_order_id
# three args can be used to add any join (parent or child)
inspection_items.inner_join('work_orders', 'wo', 'wo.id = inspection_items.work_order_id')

# two args can only be used to add a parent table join
inspection_items.inner_join('work_orders AS wo', 'inspection_items.work_order_id')

# one arg can only be used to add a parent table join
inspection_items.inner_join('work_orders AS wo')


# note that the 2 arg variation can be used to join a table that does not have a traditional foreign_key
work_orders = SqlBuilder.new
    .select('work_orders.*')

work_orders.inner_join('users', 'work_orders.technician_id')

Computed Columns

You can compute additional columns on the result set. Use the compute_column method with the name of the new column and a block that accepts each row:

locs.compute_column 'age' do |row|
  Time.now - row.created_at
end

Alternatively, you can use compute_columns to compute multiple columns at once:

locs.compute_columns do |row|
  geo = row.geo.parse_geo_point
  {latitude: geo.latitude, longitude: geo.longitude}
end

as_raw

Optionally, you can call the `as_raw' method on a SqlBuilder instance to have it return hashes instead of objects as the result:

locs = SqlBuilder.new
  .select(%w(city state), 'loc', 'location_')
  .select(%w(first_name last_name), 'tech', 'technician_') 
  .from('locations')
  .inner_join('users', 'tech', 'tech.id = loc.primary_technician_id')
  .where("zip = ?", '55124')
  .as_raw
  .exec

locs.each do |loc|
  puts "#{loc['technician_first_name']} in #{loc['location_city']}"
end

NOTE: for historical reasons, .as_raw is enabled by default in Clypboard. Use .as_objects to enable the Ruby object generation.

Dialects

Currently, SqlBuilder only supports PostgresQL and MS-SQL dialects. You can set the dialect by calling SqlBuilder#dialect with either :psql (default) or mssql.

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/Terrier-Tech/sql_builder. 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.