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
Public Class Methods
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
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
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
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
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
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
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
# 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