SqlQuery

Ruby gem to load SQL queries from templates using ERB.

It makes working with pure SQL easier with syntax highlighting.

Let's you clean your Ruby code from SQL strings.

Supported extensions: .sql.erb or .erb.sql

Installation

Add this line to your application's Gemfile:

gem 'sql_query'

And then execute:

$ bundle

Or install it yourself as:

$ gem install sql_query

Usage

Create SQL query in file in app/sql_queries directory

# app/sql_queries/get_player_by_email.sql.erb
SELECT *
FROM players
WHERE email = <%= quote @email %>

quote method is an alias to ActiveRecord.connection.quote method. You can use it to sanitize your variables for SQL.

You can use SQL like this:

> query = SqlQuery.new(:get_player_by_email, email: '[email protected]')

> query.execute
   (0.6ms)  SELECT * FROM players WHERE email = '[email protected]'
=> []

> query.explain
=> EXPLAIN for:
SELECT *
FROM players
WHERE email = '[email protected]'

                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on players  (cost=0.00..2.14 rows=1 width=5061)
   Filter: ((email)::text = '[email protected]'::text)
(2 rows)

> query.sql
=> "SELECT *\nFROM players\nWHERE email = '[email protected]'\n"

>  query.pretty_sql
=> SELECT *
FROM players
WHERE email = '[email protected]'

initialization

If you need to have nested paths to your queries like player/get_by_email just use string instead of symbol as file name.

Example:

SqlQuery.new('player/get_by_email', email: '[email protected]')

Special options

• db_connection - If you want to change default connection to database you may do it for every query execution using this option.
• sql_file_path - it will override default path where gem will look for sql file.

Methods

• execute - executes query and returns result data. It accepts boolean argument. When argument is false it will run raw sql query instead of prepared_for_logs.
• exec_query - similar to #execute but with data returned via ActiveRecord::Result.
• explain - runs explain for SQL from template
• sql - returns SQL string
• pretty_sql - returns SQL string prettier to read in console
• prepared_for_logs - returns sql string without new lines and multiple whitespaces.

Configuration

# config/initializers/sql_query.rb
SqlQuery.configure do |config|
  config.path = '/app/sql_templates'
  config.adapter = ActiveRecord::Base
end

Configuration options

• path - If you don't like default path to your queries you can change it here.

• adapter - class which implements connection method.

Partials

You can prepare part of sql query in partial file and reuse it in multiple queries.

Partial file should start with '_'.

Example:

# app/sql_queries/_email_partial.sql.erb
players.email = <%= quote @email %>

and use this partial like this:

SELECT *
FROM players
WHERE <%= partial :email_partial %>

Examples

Check examples folder for some usefull queries.

If you have some examples to share please make pull request.

Contributing

1. Fork it ( https://github.com/[my-github-username]/sql_query/fork )
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

To run specs, setup Postgres with the following:

CREATE DATABASE sqlquery;
CREATE ROLE sqlquery WITH LOGIN;