db_mod

Database enabled modules for ruby.

Rubydoc.info documentation

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, pull requests, comments and feedback all welcomed.

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 simple chained-method syntax is used

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.

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