Karim A. Nassar


Activator::DB - Wrap DBI with convenience subroutines and consistant access accross all programs in a project.


  use Activator::DB;
  my $db = Activator::DB->connect('default'); # connect to default db
  • Get a single row:

        my @row     = $db->getrow( $sql, $bind, @args );
        my $rowref  = $db->getrow_arrayref( $sql, $bind, @args );
  • Get hashref of col->value pairs:

        my $hashref = $db->getrow_hashref( $sql, $bind, @args );
  • Get all rows arrayref (these are identical):

        my $rowsref = $db->getall( $sql, $bind, @args );
        my $rowsref = $db->getall_arrayrefs( $sql, $bind, @args );
  • Get all rows ref: with each row a hashref of cols->value pairs:

        my $rowsref = $db->getall_hashrefs( $sql, $bind, @args );
  • do any query ( usually INSERT, DELETE, UPDATE ):

        my $id = $db->do( $sql, $bind, @args );
  • do query, but return id instead of success.:

        my $id = $db->do_id( $sql, $bind, @args );
       ( NOTE: this is very mysql dependant at the moment)
  • Get data from a different db for a while:

        $db->connect('alt'); # connect to alternate db
        # do something
        $db->connect('def'); # reset to default connection
        # do something else
  • Transactions (NOT YET IMPLEMENTED)::

        my $altdb = Activator::DB->connect('altdb');
        $db->do( @stuff );
        $db->do( @more_stuff );


Activator::DB module provides convenience and total consistency to accessing a database throughout a project. The idea is to reduce typing for the common cases, and remove worrying about connections. This module is a wrapper for DBI providing these advantages:

  • Provides connect string aliases centrally configured.

  • Provide consistent arguments handling to all query functions.

  • Provides connection caching without Apache::DBI -- this allows use of your model layer code in crons, daemons AND website.

  • Connection and query debug dumps using your project or module level Activator::Log config, or on a per-query basis.

  • Allows all code in your project/team/company to access the db in a consistent fashion.

  • By default, dies on all errors enforcing try/catch programming

  • Implemented as a singleton so each process is guranteed to be using no more than one connection to each database from the pool.


  • If you know DBI, you don't necessarily know Activator::DB


  • Only tested with MySql and PostgreSQL


This module uses Activator::Registry to automatically choose default databases, and Activator::Log to log warnings and errors.

Registry Setup (from Activator::Registry)

This module expects an environment variable ACT_REG_YAML_FILE to be set. If you are utilizing this module from apache, this directive must be in your httpd configuration:

  SetEnv ACT_REG_YAML_FILE '/path/to/config.yml'

If you are using this module from a script, you need to insure that the environment is properly set using a BEGIN block:

      $ENV{ACT_REG_YAML_FILE} ||= '/path/to/config.yml'

Registry Configuration

Add an Activator::DB section to your project YAML configuration file:

    log4perl<.conf>:         # Log4perl config file or definition
                             # See Logging Configuration below
     default:                # default configuration for all connections
       connection: <conn_alias>

   ## Optional default attributes and config for all connections
         debug:      0/1     # default: 0, affects all queries, all aliases
         reconn_att: <int>   # attempt reconnects this many times. default: 3
         reconn_sleep: <int> # initial sleep seconds between reconnect attempts.
                             # doubles every attempt. default: 1
       attr:                 # connection attributes. Only AutoCommit at this time
         AutoCommit: 0/1     # default: 1

   ## You must define at least one connection alias
         user: <user>
         pass: <password>
         dsn: '<DSN>' # MySql Example: DBI:mysql:<DBNAME>:<DBHOST>
                      # PostgreSQL Example: DBI:Pg:dbname=<DBNAME>
                      # see: perldoc DBI, perldoc DBD::Pg, perldoc DBD::mysql
                      # for descriptions of valid DSNs

   ## These attributes and config are all optional, and use the default from above
           AutoCommit: 0/1
            debug:     0/1   # only affects this connection


This module can be used either pseudo-OO or static on multiple databases. I say pseudo-OO, because you don't call new: this module auto-vivicates a singleton object whenever you connect for the first time.

## pseudo-OO example:
  my $db = Activator::DB->connect( 'db_alias' );
  $db->query_method( $sql, $bind, @args );
  $db->connect( 'alt_db_alias' );
  $db->query_method( $sql, $bind, @args );
  $db->connect( 'db_alias' );
  $db->query_method( $sql, $bind, @args );
## Static formatted calls require that you dictate the connection for every request. So, the above can also be done as:
  Activator::DB->query_method( $sql, $bind, connect => 'db_alias', @args );
  Activator::DB->query_method( $sql, $bind, connect => 'alt_db_alias', @args );
  Activator::DB->query_method( $sql, $bind, connect => 'db_alias', @args );
## However, the common use case for this module is:
  my $db = Activator::DB->connect( 'db_alias' );
  $db->query_method( $sql, $bind, @args );
    ### do some perl
  $db->query_method( $sql, $bind, @args );
    ### do some perl
  $db->query_method( $sql, $bind, @args );
    ### do some perl
  ... etc.

connect() Usage

  my $db = Activator::DB->connect('my_db');   # connect to my_db
  my $db->connect('default'); # connect to default db
  my $db->connect('def');     # shortcut to default db
  my $db->connect();          # shortercut to default db

connect() Caveat

Note that connect() always returns the singleton object, which in some usage patterns could cause some confusion:

  my $db1->connect('db1');           # connect to db1
  $db1->query( $sql, $bind, @args ); # acts on db1
  my $db2->connect('db2');           # connect to db2
  $db2->query( $sql, $bind, @args ); # acts on db2
  $db1->query( $sql, $bind, @args ); # still acts on db2!

For this reason, it is highly recommended that you always use the same variable name (probably $db) for the Activator::DB object.

Query Methods Usage

Every query function takes named arguments in the format of:

  Activator::DB->$query_method( $sql, $bind, opt_arg => <opt_value> );

Mandatory Arguments:

 sql   : sql statement string
 bind  : bind values arrayref

Optional Arguments: conn => alias of the db connection (default is 'default') NOTE: this changes the connection alias for all future queries attr => hashref of attributes to use for ONLY THIS QUERY Supported: AutoCommit debug => pretty print sql debugging lines

 slice     => possible future support for DBI::getall_hashref
 max_rows  => possible future support for DBI::getall_hashref


## Simple query:
    my @row = $db->getrow( $sql );
## Needy query:
    my $res = $db->do( $sql, $bind,
          connect => 'altdb', # changes the alias for future connections!
          attr => { AutoCommit => 0, },
          debug => 1,

Query Failures & Errors

All query methods die on failure, and must be wrapped in a try/catch block.

  eval {
    Activator::DB->query_method( $sql, $bind, @args );
  if ($@) {
    # catch the error

We highly recommend (and use extensively) Exception::Class::TryCatch which allows this syntactic sugar:

  try eval {
    Activator::DB->query_method( $sql, $bind, @args );
  if ( catch my $e ) {
     # rethrow, throw a new error, print something, AKA: handle it!

Errors Thrown:

  connection failure         - could not connect to database
  sql missing                - query sub called without 'sql=>' argument
  connect missing            - static call without 'connect=>' argument
  prepare failure            - failure to $dbh->prepare
  execute failure            - failure to $dbh->execute
  alias_config missing       - connection alias has no configuration
  activator_db error         - sub _warn_or_die() died without error args passed in
  fetch failure              - $sth->fetch* call failed
  do failure                 - $dbh->do call failed





Prepare and Execute a SQL statement and get a the result of values back via DBI::fetchrow_array(), DBI::fetchrow_arrayref(), DBI::fetchrow_hashref() respectively. NOTE: Unlike DBI, these return empty array/arrayref/hashref (like DBI::fetchall_arrayref does, instead of undef) when there are no results.


  my @row     = $db->getrow( $sql, $bind, @args )
  my $rowref  = $db->getrow_arrayref( $sql, $bind, @args )
  my $hashref = $db->getrow_hashref( $sql, $bind, @args )




Prepare and Execute a SQL statement, and return a reference to the result obtained by DBI::fetchall_arrayref(). Returns an empty arrayref if no rows returned for the query.

  • getall() is an alias for getall_arrayrefs() and they both return an arrayref of arrayrefs, one arrayref of values for each row of data from the query.

      $rowrefs is [ [ row1_col1_val, row1_col2_val ],
                    [ row2_col1_val, row2_col2_val ],
  • getall_hashrefs() returns an arrayref of of rows represented by hashrefs of column name => value mappings.

      $rowrefs is [ { col1 => val, col2 => val },
                    { col1 => val, col2 => val },
  my $rowref = $db->getall( $sql, $bind, @args )
  my $rowref = $db->getall_arrayrefs( $sql, $bind, @args )
  my $rowref = $db->getall_hashrefs( $sql, $bind, @args )


Execute a SQL statement and return the number of rows affected. Dies on failure.


  my $res = $db->do( $sql, $bind, @args )


Execute a SQL statement that generates an id and return the id. Dies on failure.


  my $id = $db->do_id( $sql, $bind, @args )


DBI, Activator::Registry, Activator::Log, Activator::Exception, Exception::Class::DBI, Class::StrongSingleton, Exception::Class::TryCatch


Karim A. Nassar


Copyright (c) 2007 Karim A. Nassar <karim.nassar@acm.org>

You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.