Module: Ensql

Defined in:
lib/ensql.rb,
lib/ensql/sql.rb,
lib/ensql/adapter.rb,
lib/ensql/version.rb,
lib/ensql/sequel_adapter.rb,
lib/ensql/active_record_adapter.rb

Overview

Primary interface for loading, interpolating and executing SQL statements using your preferred database connection. See Ensql.sql for interpolation details.

Examples:

# Run adhoc statements
Ensql.run("SET TIME ZONE 'UTC'")

# Run adhoc D/U/I statements and get the affected row count
Ensql.sql('DELETE FROM logs WHERE timestamp < %{expiry}', expiry: 1.month.ago).count # => 100

# Organise your SQL and fetch results as convenient Ruby primitives
Ensql.sql_path = 'app/sql'
Ensql.load_sql('customers/revenue_report', params).rows # => [{ "customer_id" => 100, "revenue" => 1000}, … ]

# Easily retrive results in alternative dimensions
Ensql.sql('select count(*) from users').first_field # => 100
Ensql.sql('select id from users').first_column # => [1, 2, 3, …]
Ensql.sql('select * from users where id = %{id}', id: 1).first_row # => { "id" => 1, "email" => "[email protected]" }

Defined Under Namespace

Modules: ActiveRecordAdapter, Adapter, SequelAdapter Classes: Error, SQL

Constant Summary collapse

VERSION =

Gem version

"0.6.1"
SUPPORTED_ACTIVERECORD_VERSIONS =

Versions of activerecord compatible with the ActiveRecordAdapter

['>= 5.0', '< 6.2'].freeze
SUPPORTED_SEQUEL_VERSIONS =

Versions of sequel compatible with the SequelAdapter

'~> 5.9'

Class Attribute Summary collapse

Class Method Summary collapse

Class Attribute Details

.adapter ⇒ Object

Connection adapter to use. Must implement the interface defined in Adapter. If not specified, it will try to autoload an adapter based on the availability of Sequel or ActiveRecord, in that order.

Examples:

require 'sequel'
Ensql.adapter # => Ensql::SequelAdapter
Ensql.adapter = Ensql::ActiveRecordAdapter # override adapter
Ensql.adapter = CustomMSSQLAdapater # supply your own adapter



89
90
91
 
# File 'lib/ensql.rb', line 89

def adapter
  @adapter ||= autoload_adapter
end

.sql_path ⇒ Object

Path to search for *.sql queries in, defaults to "sql/". For example, if sql_path is set to 'app/queries', load_sql('users/active') will read 'app/queries/users/active.sql'.

Examples:

Ensql.sql_path = Rails.root.join('app/queries')

See Also:



46
47
48
 
# File 'lib/ensql.rb', line 46

def sql_path
  @sql_path ||= 'sql'
end

Class Method Details

.load_sql(name, params = {}) ⇒ Ensql::SQL

Load SQL from a file within sql_path. This is the recommended way to manage SQL in a non-trivial project. For details of how to write interpolation placeholders, see SQL.

Examples:

Ensql.load_sql('users/activity', report_params)
Ensql.load_sql(:upsert_users, imported_users_attrs)

Returns:

See Also:



62
63
64
65
 
# File 'lib/ensql.rb', line 62

def load_sql(name, params={})
  path = File.join(sql_path, "#{name}.sql")
  SQL.new(File.read(path), params, name)
end

.run(sql, params = {}) ⇒ void

This method returns an undefined value.

Convenience method to interpolate and run the supplied SQL on the current adapter.

Examples:

Ensql.run("DELETE FROM users WHERE id = %{id}", id: user.id)
Ensql.run("ALTER TABLE test RENAME TO old_test")



75
76
77
 
# File 'lib/ensql.rb', line 75

def run(sql, params={})
  SQL.new(sql, params).run
end

.sql(sql, params = {}) ⇒ Ensql::SQL

Encapsulates a plain-text SQL statement and optional parameters to interpolate. Interpolation is indicated by one of the four placeholder formats:

  1. Literal: %{param}

    • Interpolates param as a quoted string or a numeric literal depending on the class.
    • nil is interpolated as 'NULL'.
    • Other objects depend on the database and the adapter, but most (like Time) are serialised as a quoted SQL string.

  2. List Expansion: %{(param)}

    • Expands an array to a list of quoted literals.
    • Mostly useful for column IN (1,2) or postgres row literals.
    • Empty arrays are interpolated as (NULL) for SQL conformance.
    • The parameter will be converted to an Array.

  3. Nested List: %{param(nested sql)}

    • Takes an array of parameter hashes and interpolates the nested SQL for each Hash in the Array.
    • Raises an error if param is nil or a non-hash array.
    • Primary useful for SQL VALUES () clauses.

  4. SQL Fragment: %{!sql_param}

    • Interpolates the parameter without quoting, as a SQL fragment.
    • The parameter must be an SQL object or this will raise an error.
    • nil will not be interpolated.
    • Allows composition of SQL via subqueries.

Any placeholders in the SQL must be present in the params hash or a KeyError will be raised during interpolation. Interpolation occurs just before the SQL is executed.

Examples:

# Interpolate a literal
Ensql.sql('SELECT * FROM users WHERE email > %{date}', date: Date.today)
# SELECT * FROM users WHERE email > '2021-02-22'

# Interpolate a list
Ensql.sql('SELECT * FROM users WHERE name IN %{(names)}', names: ['user1', 'user2'])
# SELECT * FROM users WHERE name IN ('user1', 'user2')

# Interpolate a nested VALUES list
Ensql.sql('INSERT INTO users (name, created_at) VALUES %{users( %{name}, now() )}',
  users: [{ name: "Claudia Buss" }, { name: "Lundy L'Anglais" }]
)
# INSERT INTO users VALUES ('Claudia Buss', now()), ('Lundy L''Anglais', now())

# Interpolate a SQL fragement
Ensql.sql('SELECT * FROM users ORDER BY %{!orderby}', orderby: Ensql.sql('name asc'))
# SELECT * FROM users ORDER BY name asc

Returns:



34
35
36
 
# File 'lib/ensql.rb', line 34

def sql(sql, params={})
  SQL.new(sql, params)
end