DBIx::Connector - Fast, safe DBI connection and transaction management

Name

DBIx::Connector - Fast, safe DBI connection and transaction management

Synopsis

  use DBIx::Connector;

  # Fetch a cached DBI handle.
  my $dbh = DBIx::Connector->connect($dsn, $username, $password, \%attr );

  # Fetch a cached connection.
  my $conn = DBIx::Connector->new($dsn, $username, $password, \%attr );

  # Get the handle and do something with it.
  my $dbh  = $conn->dbh;
  $dbh->do('INSERT INTO foo (name) VALUES (?)', undef, 'Fred' );

  # Do something with the handle more efficiently.
  $conn->do(sub {
      my $dbh = shift;
      $dbh->do('INSERT INTO foo (name) VALUES (?)', undef, 'Fred' );
  });

Description

DBIx::Connector provides a simple interface for fast and safe DBI connection and transaction management. Connecting to a database can be expensive; you don't want your application to re-connect every time you want to run a query. The efficient thing to do is to cache database handles and then just fetch them from the cache as needed in order to minimize that overhead. Database handle caching is the core function of DBIx::Connector.

You might be familiar with Apache::DBI and with the DBI's connect_cached() method. DBIx::Connector serves a similar need, but does a much better job. How is it different? I'm glad you asked!

The second function of DBIx::Connector is transaction management. Borrowing from DBIx::Class, DBIx::Connector offers an interface that efficiently handles the scoping of database transactions so that you needn't worry about managing the transaction yourself. Even better, it offers an interface for savepoints if your database supports them. Within a transaction, you can scope savepoints to behave like subtransactions, so that you can save some of your work in a transaction even if some of it fails. See txn_do() and svp_do() for the goods.

Basic Usage

If you're used to Apache::DBI or connect_cached(), the simplest thing to do is to use the connect() class method. Just change your calls from:

  my $dbh = DBI->connect(@args);

Or:

  my $dbh = DBI->connect_cached(@args);

To:

  my $dbh = DBIx::Connector->connect(@args);

DBIx::Connector will return a cached database handle whenever possible, making sure that it's fork- and thread-safe and connected to the database. If you do nothing else, making this switch will save you some headaches.

But the real utility of DBIx::Connector comes from its do() and txn_do() methods. Instead of this:

  my $dbh = DBIx::Connector->connect(@args);
  $dbh->do($query);

Try this:

  my $conn = DBIx::Connector->new(@args);
  $conn->do(sub {
      my $dbh = shift;
      $dbh->do($query);
  });

The difference is that do() will pass the database handle to the code reference without first checking that the connection is still alive. The vast majority of the time, the connection will of course still be open. You therefore save the overhead of an extra query every time you use a cached handle.

It's only if the code reference dies that do() will check the connection. If the handle is not connected to the database (because the database was restarted, for example), then do() will create a new database handle and execute the code reference again.

Simple, huh? Better still, go for the transaction management in txn_do() and the savepoint management in svp_do(). You won't be sorry, I promise.

Interface

And now for the nitty-gritty.

Constructor

new

  my $conn = DBIx::Connector−>new($dsn, $username, $password, \%attr);

Returns a cached DBIx::Connector object. The supported arguments are exactly the same as those supported by the DBI, and these also determine the connection object to be returned. If new() (or connect()) has been called before with exactly the same arguments (including the contents of the attributes hash reference), then the same connection object will be returned. Otherwise, a new object will be instantiated, cached, and returned.

Caching connections can be useful in some applications, but it can also cause problems, such as too many connections, and so should be used with care. In particular, avoid changing the attributes of a database handle returned from dbh() because it will effect other code that may be using the same connection.

As with the DBI's connect_cached() method, where multiple separate parts of a program are using DBIx::Connector to connect to the same database with the same (initial) attributes, it is a good idea to add a private attribute to the the new() call to effectively limit the scope of the caching. For example:

  DBIx::Connector−>new(..., { private_foo_key => "Bar", ... });

Connections returned from that call will only be returned by other calls to new() (or to connect()) elsewhere in the code if those other calls pass in the same attribute values, including the private one. (The use of "private_foo_key" here is an example; you can use any attribute name with a "private_" prefix.)

Taking that one step further, you can limit a particular connection to one place in the code by setting the private attribute to a unique value for that place:

  DBIx::Connector−>new(..., { private_foo_key => __FILE__.__LINE__, ... });

By using a private attribute you still get connection caching for the individual calls to new() but, by making separate database connections for separate parts of the code, the database handles are isolated from any attribute changes made to other handles.

Class Method

connect

  my $dbh = DBIx::Connector−>connect($dsn, $username, $password, \%attr);

Returns a cached database handle similar to what you would expect from the DBI's connect_cached() method -- except that it ensures that the handle is fork- and thread-safe.

Otherwise, like connect_cached(), it ensures that the database connection is live before returning the handle. If it's not, it will instantiate, cache, and return a new handle.

This method is provided as syntactic sugar for:

  my $dbh = DBIx::Connector->new(@args)->dbh;

So be sure to carefully read the documentation for new() as well. DBIx::Connector provides this method for those who just want to switch from Apache::DBI or connect_cached(). Really you want more, though. Trust me. Read on!

clear_cache

  DBIx::Connector->clear_cache;

Clears the cache of all connection objects. Could be useful in certain server settings where a parent process has connected to the database and then forked off children and no longer needs to be connected to the database itself. (FYI to mod_perl users: DBIx::Connector doesn't cache its objects during mod_perl startup, so you don't need to clear the cache manually.)

Instance Methods

dbh

  my $dbh = $conn->dbh;

Returns the connection's database handle. It will use a cached copy of the handle if the process has not been forked or a new thread spawned, and if the database connection is alive. Otherwise, it will instantiate, cache, and return a new handle.

connected

  if ( $conn->connected ) {
      $conn->dbh->do($query);
  }

Returns true if the database handle is connected to the database and false if it's not. You probably won't need to bother with this method; DBIx::Connector uses it internally to determine whether or not to create a new connection to the database before returning a handle from dbh().

disconnect

  $conn->disconnect;

Disconnects from the database. If a transaction is in process it will be rolled back. DBIx::Connector uses this method internally in its DESTROY method to make sure that things are kept tidy.

do

  my $sth = $conn->do(sub {
      my $dbh = shift;
      return $dbh->prepare($query);
  });

  my @res = $conn->do(sub {
      my ($dbh, @args) = @_;
      $dbh->selectrow_array(@args);
  }, $query, $sql, undef, $value);

Executes the given code reference, passing in the database handle. Any additional arguments passed to do() will be passed on to the code reference. In an array context, it will return all the results returned by the code reference. In a scalar context, it will return the last value returned by the code reference.

The difference from just using the database handle returned by dbh() is that do() does not first check that the connection is alive. Doing so is an expensive operation, and by avoiding it, do() optimistically expects things to just work. (It does make sure that the handle is fork- and thread-safe, however.)

In the event of a failure due to a broken database connection, do() will re-connect to the database and execute the code reference a second time. Therefore, the code ref should have no side-effects outside of the database, as double-execution in the event of a stale database connection could break something:

  my $count;
  $conn->do(sub { $count++ });
  say $count; # 1 or 2

Execution of do() can be nested with more calls to do(), or to txn_do() or svp_do():

  $conn->do(sub {
      # No transaction.
      shift->do($query);
      $conn->txn_do(sub {
          shift->do($expensive_query);
          $conn->do(sub {
              # Inside transaction.
              shift->do($other_query);
          });
      });
  });

Transactions will be scoped to the highest-up call to txn_do(), so if you call do() inside a txn_do() block, it will be executed within the transaction.

txn_do

 $conn->txn_do(sub {
      my $dbh = shift;
      $dbh->do($_) for @queries;
  });

Just like do(), only the execution of the code reference is wrapped in a transaction. If you've manually started a transaction -- either by instantiating the DBIx::Connector object with AutoCommit => 0 or by calling begin_work on the database handle, execution of txn_do() will take place inside that transaction, an you will need to handle the necessary commit or rollback yourself.

Assuming that txn_do() started the transaction, in the event of a failure the transaction will be rolled back. In the event of success, it will of course be committed.

For convenience, you can nest your calls to txn_do() or do().

  $conn->txn_do(sub {
      my $dbh = shift;
      $dbh->do($_) for @queries;
      $conn->do(sub {
          shift->do($expensive_query);
          $conn->txn_do(sub {
              shift->do($another_expensive_query);
          });
      });
  });

All code executed inside the top-level call to txn_do() will be executed in a single transaction. If you'd like subtransactions, see svp_do().

svp_do

  $conn->txn_do(sub {
      $conn->svp_do(sub {
          my $dbh = shift;
          $dbh->do($expensive_query);
          $conn->svp_do(sub {
              shift->do($other_query);
          });
      });
  });

Executes code within the context of a savepoint if your database supports it. Savepoints must be executed within the context of a transaction; if you don't call svp_do() inside a call to txn_do(), svp_do() will call it for you.

You can think of savepoints as a kind of subtransaction. What this means is that you can nest your savepoints and recover from failures deeper in the nest without throwing out all changes higher up in the nest. For example:

  $conn->txn_do(sub {
      my $dbh = shift;
      $dbh->do('INSERT INTO table1 VALUES (1)');
      eval {
          $conn->svp_do(sub {
              shift->do('INSERT INTO table1 VALUES (2)');
              die 'OMGWTF?';
          });
      };
      warn "Savepoint failed\n" if $@;
      $dbh->do('INSERT INTO table1 VALUES (3)');
  });

This transaction will insert the values 1 and 3, but not 2.

  $conn->txn_do(sub {
      my $dbh = shift;
      $dbh->do('INSERT INTO table1 VALUES (4)');
      $conn->svp_do(sub {
          shift->do('INSERT INTO table1 VALUES (5)');
      });
  });

This transaction will insert both 3 and 4.

Savepoints are currently supported by the following database versions and higher:

driver

  $conn->driver->begin_work( $conn->dbh );

In order to support all database features in a database-neutral way, DBIx::Connector provides a number of different database drivers, subclasses of <LDBIx::Connector::Driver|DBIx::Connector::Driver>, that offer methods to handle database communications. Although the DBI provides a standard interface, for better or for worse, not all of the drivers implement them, and some have bugs. To avoid those issues, all database communications are handled by these driver objects.

This can be useful if you want to do some more fine-grained control of your transactionality. For example, to create your own savepoint within a transaction, you might to something like this:

  my $driver = $conn->driver;
  $conn->do_txn( sub {
      my $dbh = shift;
      eval {
          $driver->savepoint($dbh, 'mysavepoint');
          # do stuff ...
          $driver->release('mysavepoint');
      };
      $driver->rollback_to($dbh, 'mysavepoint') if $@;
  });

Most often you should be able to get what you need out of use of txn_do() and svp_do(), but sometimes you just need the finer control. In those cases, take advantage of the driver object to keep your use of the API universal across database back-ends.

See Also

Support

This module is stored in an open GitHub repository, http://github.com/theory/dbix-connector/tree/>. Feel free to fork and contribute!

Please file bug reports at http://github.com/theory/dbix-connectora/issues/>.

Authors

This module was written and is maintained by:

David E. Wheeler <david@kineticode.com>

It is based on documentation, ideas, kibbitzing, and code from:

Copyright and License

Copyright (c) 2009 David E. Wheeler. Some Rights Reserved.

This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

 DBIx::Connector - Fast, safe DBI connection and transaction management