Yesql

Ruby library for using SQL in Ruby on Rails projects.

YeSQL is a Ruby wrapper built on top of ActiveRecord to allow applications to execute "raw" SQL files from any directory within the application.

Heavily inspired by krisajenkins/yesql Clojure library. You can see the rationale of the library, which is the same for this one.

Installation

Add this line to your application's Gemfile:

gem 'yesql'

And then execute:

$ bundle install

Or install it yourself as:

$ gem install yesql

Usage

Write a SQL query in a file under the app/yesql directory named users.sql:

-- app/yesql/users.sql
SELECT *
FROM users;

Now open the Rails console, include the module and execute your query using YeSQL:

include YeSQL

YeSQL('users')
# users (0.9ms)  SELECT * FROM users;
# => [[1, nil, nil, 2020-09-27 21:27:02.997839 UTC, 2020-09-27 21:27:02.997839 UTC]

By default the output is an array of arrays, containing the value for every row.

Options

bindings

If your query has bindings, you can pass them as the second argument when calling YeSQL.

-- app/yesql/top_10_users_in_x_country.sql
SELECT
  :country AS country,
  users.*
FROM users
WHERE country_id = :country_id
LIMIT :limit;

When calling YeSQL:

YeSQL('top_10_users_in_x_country', { country: 'Cuba', country_id: 1, limit: 6 })
  • If the query doesn't have bindings, but they're provided they're just omitted.
  • If the query has bindings, but nothing is provided, it raises a NotImplementedError exception.

output

If you need an output other than an array of arrays, you can use the output options. It accepts three values:

  • :columns (or 'columns'): returns an array containing the name of the columns returned from the query in the format ['column_a', 'column_b', ...].
  • :hash (or 'hash'): returns an array of hashes in the format [{ column: value }, ...].
  • :rows (or 'rows') DEFAULT: returns an array of arrays containing the result values from the query in the format [[value1, value2, ...], ...].

Example:

YeSQL('users', output: :columns)
# => ['id', 'name', 'admin', 'created_at', 'updated_at']

YeSQL('users', output: :hash)
# => [{:id=>1, :name=>nil, :admin=>nil, :created_at=>2020-09-27 21:27:02.997839 UTC, :updated_at=>2020-09-27 21:27:02.997839 UTC}]

YeSQL('users', output: :rows)
# => [[1, nil, nil, 2020-09-27 21:27:02.997839 UTC, 2020-09-27 21:27:02.997839 UTC]]

YeSQL('users') # same as in `YeSQL('users', output: :rows)`
# => [[1, nil, nil, 2020-09-27 21:27:02.997839 UTC, 2020-09-27 21:27:02.997839 UTC]]
  • If an unsupported output value is provided it raises a NotImplementedError exception.
  • If no output value is provided, the default is rows.

prepare

Using prepare: true it creates a prepared statement with the content of the SQL file:

ActiveRecord::Base.connection.execute('SELECT * FROM pg_prepared_statements').to_a
(0.Xms)  SELECT * FROM pg_prepared_statements
# => []

YeSQL('top_10_users_in_x_country', prepare: true)
# ...

ActiveRecord::Base.connection.execute('SELECT * FROM pg_prepared_statements').to_a
(0.Xms)  SELECT * FROM pg_prepared_statements
# => [{"name"=>"a1", "statement"=>"SELECT   $1 AS country,   users.* FROM users WHERE country_id = $2 LIMIT $3;", "prepare_time"=>2020-10-09 20:52:01.664121 +0000, "parameter_types"=>"{text,integer,bigint}", "from_sql"=>false}]

Configuration

For default YeSQL looks for the .sql files defined under the app/yesql/ folder but you can update it to use any folder you need. For that you can create a Ruby file under the config/initializers/ with the following content:

::YeSQL.configure { |config| config.path = 'path' }

After saving the file and restarting the server the files are going to be read from the given folder.

You can check at anytime what's the configuration path by inspecting the ::YeSQL config object:

::YeSQL.config
# => #<YeSQL::Config::Configuration:0x00007feea1aa2ef8 @path="app/yesql">
::YeSQL.config.path
# => "app/yesql"

Development

  • Clone the repository.
  • Install the gem dependencies.
  • Make sure to create both databases used in the dummy Rails applications (mysql, pg) in the spec/ folder.
  • Run the tests.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/sebastian-palma/yesql. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the code of conduct.

TODO

  • [ ] Allow comments in .sql files.
  • [ ] Improve errors.
  • [ ] Auto convert x IN (xs) queries to (x = x' OR x = x'').

License

The gem is available as open source under the terms of the MIT License.

Code of Conduct

Everyone interacting in the Yesql project's codebases, issue trackers, chat rooms and mailing lists is expected to follow the code of conduct.