Class: PgSqlCaller::Model

Inherits:
Object
  • Object
show all
Defined in:
lib/pg_sql_caller/model.rb

Overview

Wraps a single ActiveRecord class and runs raw SQL through its connection. Positional ‘?` placeholders are bound and sanitized by ActiveRecord, so values are never interpolated into the SQL string.

sql = PgSqlCaller::Model.new(ApplicationRecord)
sql.select_value('SELECT count(*) FROM users WHERE active = ?', true) # => 42
sql.select_values('SELECT email FROM users WHERE dept_id = ?', 5)     # => ['a@x', 'b@x']
sql.select_all('SELECT id, name FROM users')  # => [{ 'id' => 1, 'name' => 'Jo' }, ...]
sql.transaction { sql.execute('UPDATE users SET active = false') }

The ‘*_serialized` variants additionally cast each value back to its Ruby type using the result’s column types (e.g. timestamp -> Time, int[] -> Array), and key rows by Symbol:

sql.select_all_serialized('SELECT id, created_at FROM users')
# => [{ id: 1, created_at: 2026-06-08 12:00:00 +0000 }, ...]

Direct Known Subclasses

Base

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(model_class) ⇒ Model

Returns a new instance of Model.

Parameters:

  • model_class (Class<ActiveRecord::Base>) —

    the class whose connection is used to run statements and to sanitize/typecast values



105
106
107
 
# File 'lib/pg_sql_caller/model.rb', line 105

def initialize(model_class)
  @model_class = model_class
end

Instance Attribute Details

#model_class ⇒ Class<ActiveRecord::Base> (readonly)

Returns the ActiveRecord class this instance wraps.

Returns:

  • (Class<ActiveRecord::Base>) —

    the ActiveRecord class this instance wraps



82
83
84
 
# File 'lib/pg_sql_caller/model.rb', line 82

def model_class
  @model_class
end

Class Method Details

.define_sql_method(name) ⇒ Symbol

Define a single connection-backed SQL instance method named name.

Parameters:

  • name (Symbol) —

    the connection method to wrap (e.g. :select_value)

Returns:

  • (Symbol) —

    the name of the defined method



29
30
31
32
33
34
 
# File 'lib/pg_sql_caller/model.rb', line 29

def define_sql_method(name)
  define_method(name) do |sql, *bindings|
    sql = sanitize_sql_array(sql, *bindings) if bindings.any?
    connection.public_send(name, sql)
  end
end

.define_sql_methods(*names) ⇒ Array<Symbol>

Define several connection-backed SQL instance methods at once — a thin wrapper over define_sql_method, kept for backward compatibility.

Parameters:

  • names (Array<Symbol>) —

    the connection methods to wrap

Returns:

  • (Array<Symbol>) —

    names, unchanged



41
42
43
 
# File 'lib/pg_sql_caller/model.rb', line 41

def define_sql_methods(*names)
  names.each { |name| define_sql_method(name) }
end

Instance Method Details

#connection ⇒ ActiveRecord::ConnectionAdapters::AbstractAdapter

The ActiveRecord connection adapter of #model_class; every SQL method runs through it.

Returns:

  • (ActiveRecord::ConnectionAdapters::AbstractAdapter) 


87
 
# File 'lib/pg_sql_caller/model.rb', line 87

delegate :connection, to: :model_class

#current_database ⇒ String

Returns the name of the currently connected database (‘current_database()`).

Returns:

  • (String) —

    the name of the currently connected database (‘current_database()`)



242
243
244
 
# File 'lib/pg_sql_caller/model.rb', line 242

def current_database
  select_value('SELECT current_database();')
end

#execute(sql, *bindings) ⇒ PG::Result

Execute sql (e.g. INSERT/UPDATE/DELETE/DDL) and return the raw adapter result.

Parameters:

  • sql (String) —

    SQL statement, optionally containing ‘?` placeholders

  • bindings (Array<Object>) —

    values bound, in order, to the ‘?` placeholders

Returns:

  • (PG::Result) —

    the raw PostgreSQL result (e.g. #cmd_tuples for affected rows)



65
 
# File 'lib/pg_sql_caller/model.rb', line 65

define_sql_method :execute

#explain_analyze(sql) ⇒ String

Run ‘EXPLAIN ANALYZE` for sql and return the query plan as text.

Parameters:

  • sql (String) —

    the statement to analyze

Returns:

  • (String) —

    the plan, one line per row, prefixed with a QUERY_PLAN header



211
212
213
214
215
216
217
 
# File 'lib/pg_sql_caller/model.rb', line 211

def explain_analyze(sql)
  # `sql` is the statement to analyze; the caller owns the full SQL by contract (like #execute),
  # so there is no boundary to bind across.
  # nosemgrep: pg-sql-caller-interpolated-raw-sql
  result = select_values("EXPLAIN ANALYZE #{sql}")
  ['QUERY_PLAN', *result].join("\n")
end

#next_sequence_value(table_name) ⇒ Integer

The next value of the table’s ‘<table_name>_id_seq` sequence (its current last_value + 1), read without consuming the sequence.

Parameters:

  • table_name (String, Symbol)

Returns:

  • (Integer) 


160
161
162
163
164
165
166
 
# File 'lib/pg_sql_caller/model.rb', line 160

def next_sequence_value(table_name)
  sequence_name = quote_table_name("#{table_name}_id_seq")
  # `sequence_name` is an identifier escaped via quote_table_name (identifiers cannot use `?`
  # bindings), so the interpolation below is injection-safe.
  # nosemgrep: pg-sql-caller-interpolated-raw-sql
  select_value("SELECT last_value FROM #{sequence_name}") + 1
end

#quote_column_name(name) ⇒ String

Quote a column-name identifier for safe inclusion in SQL (delegated to the #connection, since the model class itself does not expose it).

Parameters:

  • name (String, Symbol) —

    the column name to quote

Returns:

  • (String) —

    the quoted identifier



94
 
# File 'lib/pg_sql_caller/model.rb', line 94

delegate :quote_column_name, to: :connection

#quote_table_name(name) ⇒ String

Quote a table-name identifier for safe inclusion in SQL (delegated to the #connection, since the model class itself does not expose it).

Parameters:

  • name (String, Symbol) —

    the table name to quote

Returns:

  • (String) —

    the quoted identifier



101
 
# File 'lib/pg_sql_caller/model.rb', line 101

delegate :quote_table_name, to: :connection

#quote_value(value) ⇒ String

Quote and escape a value as a SQL literal, safe to inline into a statement.

Parameters:

  • value (Object) —

    the value to quote (e.g. String, Numeric, nil, Time)

Returns:

  • (String) —

    the quoted SQL literal (e.g. “‘O”Brien’”)



285
286
287
 
# File 'lib/pg_sql_caller/model.rb', line 285

def quote_value(value)
  connection.quote(value)
end

#sanitize_sql_array(sql, *bindings) ⇒ String

Interpolate ‘?` placeholders in sql with bindings through ActiveRecord’s sanitizer (values are quoted/escaped, never raw-interpolated).

Parameters:

  • sql (String) —

    SQL containing ‘?` placeholders

  • bindings (Array<Object>) —

    values bound, in order, to the placeholders

Returns:

  • (String) —

    the safe, ready-to-run SQL



237
238
239
 
# File 'lib/pg_sql_caller/model.rb', line 237

def sanitize_sql_array(sql, *bindings)
  model_class.send :sanitize_sql_array, bindings.unshift(sql)
end

#select_all(sql, *bindings) ⇒ ActiveRecord::Result

Run sql and return every row.

Parameters:

  • sql (String) —

    SQL statement, optionally containing ‘?` placeholders

  • bindings (Array<Object>) —

    values bound, in order, to the ‘?` placeholders

Returns:

  • (ActiveRecord::Result) —

    rows as String-keyed hashes



72
 
# File 'lib/pg_sql_caller/model.rb', line 72

define_sql_method :select_all

#select_all_serialized(sql, *bindings) ⇒ Array<Hash{Symbol => Object}>

Like #select_all, but cast each value back to its Ruby type (using the result’s column types) and key every row by Symbol.

Parameters:

  • sql (String) —

    SQL statement, optionally containing ‘?` placeholders

  • bindings (Array<Object>) —

    values bound, in order, to the ‘?` placeholders

Returns:

  • (Array<Hash{Symbol => Object}>) 


122
123
124
125
126
127
 
# File 'lib/pg_sql_caller/model.rb', line 122

def select_all_serialized(sql, *bindings)
  result = select_all(sql, *bindings)
  result.map do |row|
    row.to_h { |key, value| [key.to_sym, deserialize_result(result, key, value)] }
  end
end

#select_row(sql, *bindings) ⇒ Array?

Run sql and return the first row as an array of column values.

Parameters:

  • sql (String) —

    SQL statement, optionally containing ‘?` placeholders

  • bindings (Array<Object>) —

    values bound, in order, to the ‘?` placeholders

Returns:

  • (Array, nil) —

    the first row, or nil when no row matches



191
192
193
 
# File 'lib/pg_sql_caller/model.rb', line 191

def select_row(sql, *bindings)
  select_rows(sql, *bindings)[0]
end

#select_rows(sql, *bindings) ⇒ Array<Array>

Run sql and return rows as arrays of column values (no column names).

Parameters:

  • sql (String) —

    SQL statement, optionally containing ‘?` placeholders

  • bindings (Array<Object>) —

    values bound, in order, to the ‘?` placeholders

Returns:

  • (Array<Array>) 


79
 
# File 'lib/pg_sql_caller/model.rb', line 79

define_sql_method :select_rows

#select_value(sql, *bindings) ⇒ Object?

Run sql and return the value of the first column of the first row.

Parameters:

  • sql (String) —

    SQL statement, optionally containing ‘?` placeholders

  • bindings (Array<Object>) —

    values bound, in order, to the ‘?` placeholders

Returns:

  • (Object, nil) —

    the single value, or nil when no row matches



51
 
# File 'lib/pg_sql_caller/model.rb', line 51

define_sql_method :select_value

#select_value_serialized(sql, *bindings) ⇒ Object?

Like #select_value, but cast the value back to its Ruby type.

Parameters:

  • sql (String) —

    SQL statement, optionally containing ‘?` placeholders

  • bindings (Array<Object>) —

    values bound, in order, to the ‘?` placeholders

Returns:

  • (Object, nil) —

    the type-cast value, or nil when no row matches



134
135
136
137
138
139
140
141
 
# File 'lib/pg_sql_caller/model.rb', line 134

def select_value_serialized(sql, *bindings)
  result = select_all(sql, *bindings)
  key = result.first&.keys&.first
  return if key.nil?

  value = result.first.values.first
  deserialize_result(result, key, value)
end

#select_values(sql, *bindings) ⇒ Array<Object>

Run sql and return the first column of every row.

Parameters:

  • sql (String) —

    SQL statement, optionally containing ‘?` placeholders

  • bindings (Array<Object>) —

    values bound, in order, to the ‘?` placeholders

Returns:

  • (Array<Object>) 


58
 
# File 'lib/pg_sql_caller/model.rb', line 58

define_sql_method :select_values

#select_values_serialized(sql, *bindings) ⇒ Array<Array>

Run sql and return each row as an array of its type-cast column values.

Parameters:

  • sql (String) —

    SQL statement, optionally containing ‘?` placeholders

  • bindings (Array<Object>) —

    values bound, in order, to the ‘?` placeholders

Returns:

  • (Array<Array>) —

    one inner array per row



148
149
150
151
152
153
 
# File 'lib/pg_sql_caller/model.rb', line 148

def select_values_serialized(sql, *bindings)
  result = select_all(sql, *bindings)
  result.map do |row|
    row.map { |key, value| deserialize_result(result, key, value) }
  end
end

#table_data_size(table_name) ⇒ Integer

On-disk size of the table’s main data fork only, in bytes (PostgreSQL ‘pg_relation_size`).

Parameters:

  • table_name (String, Symbol)

Returns:

  • (Integer) —

    size in bytes



182
183
184
 
# File 'lib/pg_sql_caller/model.rb', line 182

def table_data_size(table_name)
  select_value('SELECT pg_relation_size(?)', table_name)
end

#table_full_size(table_name) ⇒ Integer

Total on-disk size of the table including indexes and TOAST, in bytes (PostgreSQL ‘pg_total_relation_size`).

Parameters:

  • table_name (String, Symbol)

Returns:

  • (Integer) —

    size in bytes



173
174
175
 
# File 'lib/pg_sql_caller/model.rb', line 173

def table_full_size(table_name)
  select_value('SELECT pg_total_relation_size(?)', table_name)
end

#transaction { ... } ⇒ Object

Run the given block inside a database transaction, committing on success and rolling back if it raises.

Yields:

  • executes within the open transaction

Returns:

  • (Object) —

    the block’s return value

Raises:

  • (ArgumentError) —

    if no block is given



201
202
203
204
205
 
# File 'lib/pg_sql_caller/model.rb', line 201

def transaction(&)
  raise ArgumentError, 'block must be given' unless block_given?

  connection.transaction(&)
end

#transaction_open? ⇒ Boolean

Whether a database transaction is currently open on the connection.

Returns:

  • (Boolean) 


112
113
114
 
# File 'lib/pg_sql_caller/model.rb', line 112

def transaction_open?
  connection.send(:transaction_open?)
end

#typecast_array(values, type:) ⇒ String

Encode a Ruby array into a PostgreSQL array literal for the given attribute type, ready to bind as a single ‘?` value.

Parameters:

  • values (Array) —

    the Ruby values to encode

  • type (Symbol) —

    an ActiveRecord attribute type (e.g. :integer, :string, :datetime)

Returns:

  • (String) —

    a PostgreSQL array literal, e.g. “{1,2,3}”



225
226
227
228
229
 
# File 'lib/pg_sql_caller/model.rb', line 225

def typecast_array(values, type:)
  type = ActiveRecord::Type.lookup(type, array: true)
  data = type.serialize(values)
  data.encoder.encode(data.values)
end

#with_min_messages(level) { ... } ⇒ Object

Temporarily set the connection’s client_min_messages to level for the duration of the block, restoring the previous value afterward.

Parameters:

  • level (String) —

    one of: debug5, debug4, debug3, debug2, debug1, log, notice, warning, error

Yields:

  • runs with the level applied

Returns:

  • (Object) —

    the block’s return value



273
274
275
276
277
278
279
 
# File 'lib/pg_sql_caller/model.rb', line 273

def with_min_messages(level)
  old_level = select_value('SHOW client_min_messages')
  execute('SET client_min_messages TO ?', level)
  yield
ensure
  execute('SET client_min_messages TO ?', old_level) unless old_level.nil?
end

#with_notice_processor(callback) { ... } ⇒ Object

Capture PostgreSQL NOTICE output (e.g. from RAISE NOTICE) emitted while the block runs, passing each message to callback. Lowers client_min_messages to notice for the duration (see #with_min_messages) and restores the previous notice processor afterward.

sql.with_notice_processor(->(msg) { logger.info(msg) }) do
  sql.execute("DO $$ BEGIN RAISE NOTICE 'hi'; END $$")
end

Parameters:

  • callback (#call) —

    invoked with each notice message (a chomped String)

Yields:

  • runs with the notice processor installed

Returns:

  • (Object) —

    the block’s return value



258
259
260
261
262
263
264
265
 
# File 'lib/pg_sql_caller/model.rb', line 258

def with_notice_processor(callback)
  with_min_messages('notice') do
    old_processor = connection.raw_connection.set_notice_processor { |result| callback.call(result.to_s.chomp) }
    yield
  ensure
    connection.raw_connection.set_notice_processor(&old_processor)
  end
end