Use erb_sql_templates
when you have large complex SQL queries that you do not want
embedded inside your Ruby as strings. The gem allows for SQL to be stored in their own
directory and files and the queries can be customized with ERB.
Example SQL template file, some_query.erb.sql
. Templates must have .erb.sql
extension.
-- FILE: find_user.erb.sql
SELECT
*,
<%= render '_say_hello' :name => 'Bob' %> AS hello_message
FROM <%= table_name %>
WHERE
email = <%= h(email) %>
-- FILE: _say_hello.erb.sql
CONCAT('Hello ', <%= h name %>)
To use the template in Ruby, specify the template directory,
a database connection, and call #execute
or #render
with the template name.
Names arguments will be available inside the SQL template.
template_dir = Rails.root.to_s + '/db/sql/templates'
sql_builder = ErbSqlTemplates.new(template_dir, ActiveRecord::Base.connection)
# Execute SQL directly
sql_builder.execute(:find_user, :email => '[email protected]', :table_name => 'users')
# Or have it return the SQL
sql = sql_builder.render(:find_user, :email => '[email protected]', :table_name => 'users')
The library expects that the connection has a #quote
method on it.
Inside your SQL template you can use the h
helper to escape variables.
Example: UPDATE users SET name = <%= h dirty_input %>
Try ruby test/*_test.rb
.