Module: Ensql

Defined in:
lib/ensql.rb,
lib/ensql/sql.rb,
lib/ensql/error.rb,
lib/ensql/adapter.rb,
lib/ensql/version.rb,
lib/ensql/load_sql.rb,
lib/ensql/transaction.rb,
lib/ensql/pool_wrapper.rb,
lib/ensql/sequel_adapter.rb,
lib/ensql/postgres_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: Adapter Classes: ActiveRecordAdapter, Error, PoolWrapper, PostgresAdapter, SQL, SequelAdapter

Constant Summary collapse

VERSION =

Gem version

"0.6.5"
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"
SUPPORTED_PG_VERSIONS =

Versions of pg compatibile with the PostgresAdapter

[">= 0.19", "< 2"].freeze

Class Attribute Summary collapse

Class Method Summary collapse

Class Attribute Details

.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:



16
17
18
 
# File 'lib/ensql/load_sql.rb', line 16

def sql_path
  @sql_path ||= "sql"
end

Class Method Details

.adapter ⇒ Object

Get the current connection 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.new
Ensql.adapter = Ensql::ActiveRecordAdapter.new # override adapter
Ensql.adapter = my_tsql_adapter # supply your own adapter


17
18
19
 
# File 'lib/ensql/adapter.rb', line 17

def adapter
  Thread.current[:ensql_adapter] || Thread.main[:ensql_adapter] ||= autoload_adapter
end

.adapter=(adapter) ⇒ Object

Set the connection adapter to use. Must implement the interface defined in Adapter. This uses a thread-local variable so adapters can be switched safely in a multi-threaded web server.



24
25
26
27
28
29
30
 
# File 'lib/ensql/adapter.rb', line 24

def adapter=(adapter)
  if adapter.is_a?(Module) && (adapter.name == "Ensql::SequelAdapter" || adapter.name == "Ensql::ActiveRecordAdapter")
    warn "Using `#{adapter}` as an adapter is deprecated, use `#{adapter}.new`.", uplevel: 1
  end

  Thread.current[:ensql_adapter] = adapter
end

.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:



32
33
34
35
36
37
 
# File 'lib/ensql/load_sql.rb', line 32

def load_sql(name, params = {})
  path = File.join(sql_path, "#{name}.sql")
  SQL.new(File.read(path), params, name)
rescue Errno::ENOENT
  raise Error, "couldn't load SQL from file '#{path}' (sql_path: '#{sql_path}')"
end

.rollback! ⇒ Object

Immediately rollback and exit the current transaction block. See transaction.



51
52
53
54
55
 
# File 'lib/ensql/transaction.rb', line 51

def rollback!
  throw :rollback, :rollback
rescue UncaughtThrowError
  raise Error, "not in a transaction block, can't rollback"
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")


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

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:



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

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

.transaction(start: "START TRANSACTION", commit: "COMMIT", rollback: "ROLLBACK", &block) ⇒ Object

Wrap a block with a transaction. Uses the well supported SQL-standard commands for controlling a transaction by default, however database specific statements can be supplied. Any exceptions inside the block will trigger a rollback and be reraised. Alternatively, you can call rollback! to immediately exit the block and rollback the transaction. Returns the result of the block. If the block returns :rollback, the transaction will also be rolled back.

# If `do_thing1` or `do_thing2` raise an error, no statements are committed.
Ensql.transaction { do_thing1; do_thing2 }

# If `do_thing2` is falsey, `do_thing1` is rolled back and `do_thing3` is skipped.
Ensql.transaction { do_thing1; do_thing2 or Ensql.rollback!; do_thing3 }

# Nest transactions with savepoints.
Ensql.transaction do
  do_thing1
  Ensql.transaction(start: 'SAVEPOINT my_savepoint', commit: 'RELEASE SAVEPOINT my_savepoint', rollback: 'ROLLBACK TO SAVEPOINT my_savepoint') do
    do_thing2
    do_thing3
  end
end

# Use database-specific transaction semantics.
Ensql.transaction(start: 'BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE') { }

Parameters:

  • start (defaults to: "START TRANSACTION") —

    the SQL to begin the transaction.

  • commit (defaults to: "COMMIT") —

    the SQL to commit the transaction if successful.

  • rollback (defaults to: "ROLLBACK") —

    the SQL to rollback the transaction if an error is raised.

See Also:



38
39
40
41
42
43
44
45
46
47
 
# File 'lib/ensql/transaction.rb', line 38

def transaction(start: "START TRANSACTION", commit: "COMMIT", rollback: "ROLLBACK", &block)
  adapter.run(start)
  result = catch(:rollback, &block)
  adapter.run(result == :rollback ? rollback : commit)
  result
# # We need to try rollback on _any_ exception. Since we reraise, rescuing this is safe.
rescue Exception # rubocop:disable Lint/RescueException
  adapter.run(rollback)
  raise
end