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]')

Methods

execute - executes query and returns result data.
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

If you don't like default path to your queries you can configure it in initializer.

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

Contributing

Fork it ( https://github.com/[my-github-username]/sql_query/fork )
Create your feature branch (git checkout -b my-new-feature)
Commit your changes (git commit -am 'Add some feature')
Push to the branch (git push origin my-new-feature)
Create a new Pull Request