sufler/sql_query

Ruby gem to load SQL queries from `.sql.erb` templates using ERB.

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;

sql_query's Issues

About the db_connection option

Not being higly familiar with connections handling, I am wandering how to specify this option when:

1. using a connection declared in database.yml config file
2. using a custom connection to a foreign database

I'll be glad to add examples to the documentation when I solve this point :-)

Thanks a lot!

Development status and roadmap?

Hi,

I'm considering using sql_query for a project in production, and to that end I want to make sure that this gem is still under relatively active development. Do you have new features planned?

Thanks for your help!

prepared_for_logs and stored procedures

Hi, and many thanks for sql_query. I'm using it to load stored procedures into my PostGIS DB for a rails app. However, I find that I get postgres syntax errors on my call

SqlQuery.new(:procedure_cell_truncate, opts).execute

specifically

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at end of input
LINE 1: ...ygon FROM geom ) AS q WHERE cells.id = q.id; END; $body$ LAN...

(The full .sql.erb file is here).

I found that I could pipe the output of

SqlQuery.new(:procedure_cell_truncate, opts).sql

directly to psql and there was no syntax error. So on a hunch, I monkey-patched the prepared_for_logs method with

class SqlQuery
  def prepared_for_logs
    sql
  end
end

and then the syntax error went away.

I'm really not sure why the gsub in prepared_for_logs is borking this query, perhaps this is obvious to someone else?

New constructor

Maybe make, some thing like this:

    def initialize(*attrs)
      fail 'options empty' if attrs.empty? || !attrs.last.is_a? Hash
      options = attrs.last
      case attrs[0]
      when String
        options[:sql_name] ||= name
      when Symbol
        options[:sql_name] ||= name
      end

      prepare_variables(options)
      @connection = ActiveRecord::Base.connection
    end

Use:

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

Request: support option to use exec_query instead of execute?

Hello! I was wondering if it would be possible to support exec_query here instead of execute. This would be nice because exec_query returns an object that's easier to work with.

https://github.com/sufleR/sql_query/blob/master/lib/sql_query.rb#L27

If you think this is ok I can file a PR. Thanks! Proposed API would just be the addition of an exec_query method.

prepare_for_logs reduces multiple spaces to single space in embedded parameters

Hi! I've tried to upgrade to last released version 0.7.2, but the behaviour is still present.

The query template file "test_query.sql.erb" has the following content:
SELECT * FROM my_table WHERE my_string_field = <%= quote @my_string_value %>

Calling prepared_for_logs function
SqlQuery.new('test_query', my_string_value: 'My string value').prepared_for_logs

returns the following query
"SELECT * FROM my_table WHERE my_string_field = 'My string value'"
instead of
"SELECT * FROM my_table WHERE my_string_field = 'My string value'"

0.7.3 publish to rubygems.org? update CHANGELOG

@sufleR great tool! thank you for your great work.

noticed 0.7.3 was not published to rubygems.org

Feature Request: Scopes & AR translations

Hi, love your gem!

really helps organizing SQL in files, and write advanced queries without the Active Record/Arel hassle

could you add Scopes implementation, so there is code reuse & chaining?
i now find myself copy/pasting the same WHERE's across queries.

and could you add Active Record 'translations'
like this example: .where(item: items)
is translated into = 'item', or IN ('item', 'item')
depending on whether you have a single param or Array as parameter.
now i have to do this: items.join(',') assuming the items Array already have ''s around the keys

Thank you!

Rails 7 support

Are there any reasons why activerecord is set to <= 7.0?
That essentially blocks using this gem with Rails 7 because it limits you to version 7.0.0 only which probably isn't the best idea for any production usage ;)

Switch CI to GitHub Actions from Travis

looks like Travis is no longer working.