module Ensql

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

@example

# 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" => "test@example.com" }

Constants

SUPPORTED_ACTIVERECORD_VERSIONS

Versions of activerecord compatible with the {ActiveRecordAdapter}

SUPPORTED_PG_VERSIONS

Versions of pg compatibile with the {PostgresAdapter}

SUPPORTED_SEQUEL_VERSIONS

Versions of sequel compatible with the {SequelAdapter}

VERSION

Gem version

Attributes

sql_path[W]

Public Class Methods

adapter() click to toggle source

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.

@example

require 'sequel'
Ensql.adapter # => Ensql::SequelAdapter.new
Ensql.adapter = Ensql::ActiveRecordAdapter.new # override adapter
Ensql.adapter = my_tsql_adapter # supply your own adapter
# File lib/ensql/adapter.rb, line 17
def adapter
  Thread.current[:ensql_adapter] || Thread.main[:ensql_adapter] ||= autoload_adapter
end
adapter=(adapter) click to toggle source

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

# 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 = {}) click to toggle source

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

@see .sql_path= @return [Ensql::SQL]

@example

Ensql.load_sql('users/activity', report_params)
Ensql.load_sql(:upsert_users, imported_users_attrs)
# 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!() click to toggle source

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

# 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 = {}) click to toggle source

Convenience method to interpolate and run the supplied SQL on the current adapter. @return [void]

@example

Ensql.run("DELETE FROM users WHERE id = %{id}", id: user.id)
Ensql.run("ALTER TABLE test RENAME TO old_test")
# File lib/ensql.rb, line 45
def run(sql, params = {})
  SQL.new(sql, params).run
end
sql(sql, params = {}) click to toggle source

(see SQL) @return [Ensql::SQL] SQL statement

# File lib/ensql.rb, line 33
def sql(sql, params = {})
  SQL.new(sql, params)
end
sql_path() click to toggle source

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'. @see .load_sql

@example

Ensql.sql_path = Rails.root.join('app/queries')
# File lib/ensql/load_sql.rb, line 16
def sql_path
  @sql_path ||= "sql"
end
transaction(start: "START TRANSACTION", commit: "COMMIT", rollback: "ROLLBACK", &block) click to toggle source

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') { }

@see rollback! @param start the SQL to begin the transaction. @param commit the SQL to commit the transaction if successful. @param rollback the SQL to rollback the transaction if an error is raised.

# 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

Private Class Methods

autoload_adapter() click to toggle source
# File lib/ensql/adapter.rb, line 34
def autoload_adapter
  if defined? Sequel
    require_relative "sequel_adapter"
    SequelAdapter.new
  elsif defined? ActiveRecord
    require_relative "active_record_adapter"
    ActiveRecordAdapter.new
  else
    raise Error, "Couldn't autodetect an adapter, please specify manually."
  end
end