Ruby framework for building modular database libraries
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Type Name Latest commit message Commit time
Failed to load latest commit information.


Database enabled modules for ruby.

GitHub version Travis CI Code Climate Test Coverage Inline docs Gem downloads documentation


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.


From the command line:

gem install db_mod

Or in your Gemfile:

gem 'db_mod'

And then in your script:

require 'db_mod'


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'

  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

include MyFunctions

  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'
  # ...

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


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

db = DbAccess.create db: 'mydb'

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

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

  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'])

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

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

# ...

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

# ...

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

For methods with fixed parameters:

def_statement(:a, %(
    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

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| do |animal|
      "the #{animal['name']} goes #{animal['sound']}"
    end.join ' and '

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

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

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

# ...

  # === 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) }

  def send_report(csv)
    # ...

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

module ReportEmailer
  include CsvReports

  default_method_settings do
    returning do |ids|
      ids.each { |id| send_all_reports_for(id) }

  def send_all_reports_for(user)

  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) } }
  def_statement(:send_reports, 'SELECT id FROM user WHERE name = $name') do
    single(:value).returning { |id| send_all_reports_for(id) }

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

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)