Project: db_mod - The Ruby Toolbox

DbMod

Database enabled modules for ruby.

Description

The db_mod gem is a simple framework that helps you organise your database access functions into modular libraries that can be included in your projects to give them selective access to facets of your data.

For the moment db_mod only supports PostgreSQL databases via the pg gem. This gem is still in the early stages of development and no guarantees will be made about backwards compatibility until v0.1.0.

Issues, feature or pull requests, comments and feedback all welcomed.

Installation

From the command line:

gem install db_mod

Or in your Gemfile:

gem 'db_mod'

And then in your script:

require 'db_mod'

Usage

The database connection

At its most basic, db_mod provides db_connect, query, and transaction:

require 'db_mod'

module MyFunctions
  include DbMod

  def get_stuff
    query 'SELECT * FROM stuff'
  end

  def do_complicated_thing(input)
    transaction do # calls BEGIN
      query 'INSERT ...'
      query 'UPDATE ...'
      query 'DELETE ...'

      output = query 'SELECT ...'
      fail if output.empty? # calls ROLLBACK
    end # calls COMMIT
  end
end

include MyFunctions

db_connect(
  db: 'mydb',
  host: 'localhost', # defaults to local socket
  port: 5432,        # this is the default
  user: 'myuser',    # default is ENV['USER']
  pass: 'password'   # attempts trusted connection by default
)

get_stuff.each do |thing|
  thing['id'] # => '1'
  # ...
end

Module instances: `DbMod.create`

Each module also comes with its own create function, which instantiates an object exposing all of the module's functions.

# Standard connection options can be used.
# db_connect will be called.
db = MyFunctions.create db: 'mydb'

# Or an existing connection object can be passed
db = MyFunctions.create PGconn.connect # ...

db.get_stuff

The connection object: `@conn`

The connection created by db_connect or create will be stored in the instance variable @conn. This instance variable may be set explicitly instead of calling db_connect, allowing arbitrary sharing of database connections between modules and objects. See notes below on using this technique with def_prepared.

Module heirarchies

By including multiple modules into the same class or object, they will all use the same connection object supplied by db_connect. This connection object is stored in the instance variable @conn and can be supplied manually:

module DbAccess
  # includes DbMod and defines do_things
  include Db::Things

  # includes DbMod and defines do_stuff
  include Db::Stuff

  def things_n_stuff
    transaction do
      do_things
      do_stuff
    end
  end
end

db = DbAccess.create db: 'mydb'
db.things_n_stuff

Declaring SQL statements

Prepared statement methods: `DbMod.def_prepared`

Modules which include DbMod can declare prepared statements using the module function def_prepared. These statements will be prepared on the connection when db_connect is called. A method will be defined in the module with the same name as the prepared statement, provided as a convenience for executing the statement:

module Db
  module Things
    # Statements can use named parameters:
    def_prepared :foo, <<-SQL
      SELECT *
        FROM foo
       WHERE id = $id
         AND b > $minimum_value
         AND c > $minimum_value
    SQL
  end

  module Stuff
    # Indexed parameters also work:
    def_prepared :bar, <<-SQL
      INSERT INTO bar
        (a, b, c)
      VALUES
        ($1, $2, $1)
    SQL
  end

  module ComplicatedStuff
    # Statements on included modules will also
    # be executed when db_connect is called.
    include Things
    include Stuff

    def complicated_thing!(id, min)
      transaction do
        foo(id: id, minimum_value: min).each do |thing|
          bar(thing['a'], thing['b'])
        end
      end
    end
  end
end

include Db::ComplicatedStuff
db_connect db: 'mydb'

complicated_thing!(1, 2)

Note that if an existing connection is supplied to create or @conn then declared statements will not be automatically prepared. In this case the module function prepare_all_statements(conn) can be used to prepare all statements declared in the module or any included modules on the given connection.

db = Db::ComplicatedStuff.create my_conn
Db::ComplicatedStuff.prepare_all_statements my_conn

Saved statement methods: `DbMod.def_statement`

def_statement works in just the same way as def_prepared, except that the SQL queries are saved in memory rather than being sent to the database at connection time. This is useful for queries that will only be run once or twice during a program's execution.

Configuring defined statements

db_mod contains a simple framework for extending these statement methods and prepared methods with additional result processing. A block can be passed to +def_prepared+ and +def_statement+ definitions, where a basic DSL is made available for additional method configuration.

JSON and CSV formatting

Statement and prepared methods can be configured on declaration by using as(:csv) and as(:json), which will convert the result set to a string formatted as either a CSV document or an array of JSON objects, respectively.

# db_mod makes no attempt to load these
require 'csv'
require 'json'

module Reports
  include DbMod

  def_prepared(:foo, 'SELECT a, b FROM foo WHERE bar_id = $id') { as(:csv) }
  def_statement(:bar, 'SElECT c, d FROM bar WHERE foo_id = $1') { as(:json) }
end

include Reports
db_connect db: 'testdb'

foo(id: 1) # => "a,b\n1,2\n3,4\n..."
bar(2) # => '[{"c":"5","d":"6"},...]'

Queries returning one row, column or value

To save a lot of repetetive unboxing of query results, methods that return only one row, or rows with only one column, or only one row with a single value, can be marked as such using the single extension.

module Getters
  include DbMod

  def_prepared(:user, 'SELECT * FROM user WHERE id = $1') { single(:row) }
  def_prepared(:name, 'SELECT name FROM user WHERE id = $1') { single(:value) }
  def_statement(:ids, 'SELECT id FROM user') { single(:column) }
end

# ...

user(1) # => { "id" => "1", "name" => "username" }
name(1) # => "username"
ids # => ['1', '2', '3', ...]

When no results are returned, :column returns [] while :row and :value will return nil. To raise an exception instead of returning nil, use :row! and :value! instead.

def_statement(:a, 'SELECT 1 WHERE true = false') { single(:value) }
def_statement(:b, 'SELECT 1 WHERE true = false') { single(:value!) }

a # => nil
b # => fail

Default parameter values

Arbitrary default parameter values can be supplied using defaults.

For methods with named parameters:

def_statement(:a, 'SELECT * FROM foo WHERE id = $id AND y > $min') do
  defaults min: 10
end

# ...

a(id: 1) # === a(id: 1, min: 10)

For methods with fixed parameters:

def_statement(:a, %(
  SELECT *
    FROM foo
   WHERE x = $1
     AND y < $2
     AND z > $3
)) { defaults 4, 5, 6 }

# ...

a          # === a(4, 5, 6)
a(1)       # === a(1, 5, 6)
a(1, 2)    # === a(1, 2, 6)
a(1, 2, 3) # === a(1, 2, 3)

A proc may be given as the default value for any parameter. The proc should accept one parameter, which will be the argument list/hash (depending on if the statement uses numbered or named arguments) and should return a single value to be used for this execution of the query.

def_prepared(:default_min, %(
  SELECT default_min
    FROM defaults
   WHERE foo_id = $1
)) { single(:value) }

def_prepared(:report, 'SELECT * FROM a WHERE b = $c AND d > $e') do
  defaults min: ->(args) { default_min(args[:c]) }
  as :json
end

The above example shows how the proc will be executed using the instance of the DbMod module as scope, so statement, prepared, or other methods may be accessed.

Note that the argument list will be partially constructed at the time it is received by the proc; other default values may or may not yet have been populated. Defaults will be populated in the order that they are declared using defaults.

Custom method return values

Besides the built-in result transformations provided by as and single, db_mod also allows arbitrary control over the return value of statement and prepared methods using a block provided via returning:

def_prepared(:a, 'SELECT name, sound FROM animals') do
  # Block parameter is the SQL result set
  returning do |animals|
    animals.map do |animal|
      "the #{animal['name']} goes #{animal['sound']}"
    end.join ' and '
  end
end

def_prepared(:important_report!, 'SELECT address FROM email WHERE id = $1') do
  # 'single' and 'as' will transform the result set
  # before it is passed to the block
  single(:value)

  returning { |email| send_email(email, a) }
end

# Block has instance-level scope
def send_email(address, body)
  # ...
end

# ...

important_report!(123)
  # === send_email('ex@mp.le', 'the sheep goes baa and the cow goes moo')

Default configuration settings

To save typing, modules may declare a default_method_settings block that will be applied to all following def_statement and def_prepared definitions. The dsl used is the same as for individual method configuration blocks.

require 'csv'

module CsvReports
  include DbMod

  default_method_settings do
    as(:csv).returning { |csv| send_report(csv) }
  end

  def send_report(csv)
    # ...
  end

  def_prepared(:report_a, 'SELECT * FROM report_a WHERE user_id = $1')
  def_prepared(:report_b, 'SELECT * FROM report_b WHERE user_id = $1')
  def_prepared(:report_c, 'SELECT * FROM report_c WHERE user_id = $1')
end

module ReportEmailer
  include CsvReports

  default_method_settings do
    single(:column)
    returning do |ids|
      ids.each { |id| send_all_reports_for(id) }
    end
  end

  def send_all_reports_for(user)
    report_a(user)
    report_b(user)
    report_c(user)
  end

  def_statement(:send_all_reports, 'SELECT id FROM user')
  def_statement(:send_priority_reports, 'SELECT id FROM user WHERE priority')

  # individual settings may be overridden
  def_statement(:send_all_a_reports, 'SELECT id FROM user') do
    returning { |ids| ids.each { |id| report_a(id) } }
  end
  def_statement(:send_reports, 'SELECT id FROM user WHERE name = $name') do
    single(:value).returning { |id| send_all_reports_for(id) }
  end
end

Defaults don't cascade automatically from one module to another module that has included it. However the following sorts of things work if you need more flexibility in reusing default settings:

BASE_SETTINGS = ->() { single(:row).as(:json) }
module A
  include DbMod

  # Use base settings with overrides in block
  default_method_settings(BASE_SETTINGS) { as(:csv) }
end

A.default_method_settings # => { as: :csv, single: :row }

module B
  include A

  # Inherit settings from A, overrides as named args
  default_method_settings(A.default_method_settings, as: :csv)
end

db_mod

Development

Runtime

DbMod

Description

Installation

Usage

The database connection

Module instances: `DbMod.create`

The connection object: `@conn`

Module heirarchies

Declaring SQL statements

Prepared statement methods: `DbMod.def_prepared`

Saved statement methods: `DbMod.def_statement`

Configuring defined statements

JSON and CSV formatting

Queries returning one row, column or value

Default parameter values

Custom method return values

Default configuration settings

db_mod

Development

Runtime

DbMod

Description

Installation

Usage

The database connection

Module instances: DbMod.create

The connection object: @conn

Module heirarchies

Declaring SQL statements

Prepared statement methods: DbMod.def_prepared

Saved statement methods: DbMod.def_statement

Configuring defined statements

JSON and CSV formatting

Queries returning one row, column or value

Default parameter values

Custom method return values

Default configuration settings

Module instances: `DbMod.create`

The connection object: `@conn`

Prepared statement methods: `DbMod.def_prepared`

Saved statement methods: `DbMod.def_statement`