The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.


POE::Component::Pool::DBI - Simplified DBI access through a pooled resource.


use POE qw( Component::Pool::DBI );

     inline_states => {
         _start => sub {
             my ($kernel, $heap) = @_[ KERNEL, HEAP ];
             my $dbpool = POE::Component::Pool::DBI->new(
                 connections     => 10,
                 dsn             => "DBI:mysql:database=test",
                 username        => "username",
                 password        => "password"
             # Outstanding queries keep the calling session alive.
                 callback => "handle_result",
                 query    => "select foo from bar where foo = ?",
                 params   => [ "foo" ],
                 userdata => "example"

             $heap->{dbpool} = $dbpool;
         handle_result => sub {
             my ($kernel, $heap, $results, $userdata) = 
                 @_[ KERNEL, HEAP, ARG0, ARG1 ];
             # Will be an arrayref of hashrefs.
             for my $record (@$results) {
                 print $record->{foo};
             my $dbpool = $heap->{dbpool};
             # Queries which do not return data should use the do method.
             # If no callback is supplied, no callback happens.  This is
             # suitable for queries where the result is not necessarily
             # important.
                 query => "INSERT INTO results (query, count) VALUES (?,?)",
                 args  => [ $udata, @$results ],
             # Ask for a clean shutdown.


This component provides a threadpool-backed DBI connection pool. It's fairly well optimized for high throughput (particularly insert) servers which rely on a relational database. It enables pooled connectivity by being implemented upon a limited-availability thread pool, with each thread maintaining a connection. It uses an asyncronous queue for allowing excess queries to 'stack up', in order to enable availability.


Why yet *another* DBI interface for POE? There are already about 6. But in looking for a solution for a high availability UDP server I was unable to find one which managed pooling effectively, and I always thought this would be a reasonable application of the partner component, POE::Component::Pool::Thread.



The size, in number of threads and connections, of the resource pool.


The database service name, as per the DBI->connect method.


The username on the database (DBI->connect).


The password on the database (DBI->connect).



The query method enqueues a query to be run by the job pool. The preparation of the query will be cached, so it's suggested you use placeholders to ensure the fastest response times and to avoid leaking statements.


The query argument holds the SQL or PL/SQL statement to execute. The statement will be invoked immediately, but it will be cached within the connection (see DBI prepare_cached).


The arguments to provide to DBI's execute method (see DBI).


The state to invoke wihen the operation is complete. The state will be provided with the results of the query (if applicable), as an array of hashref (faciliated by DBI's fetchall_arrayref({})) assuming your driver supports this functionality (most do).


Assuming a callback has been provided, this data will be provided to the callback. This data is cached within the controlling session, and is not "available" from inside the job threads. It needs not be shared in any way.


The do operation will invoke a query, but forgo attempting to fetch any results from the query. This method should be used for statements which do not return result sets, such as INSERT or UPDATE statements on most databases. This operation accepts the same arguments as the query method, which are documented above.


The shutdown operation piggybacks off of POE::Component::Pool::Thread (the PoCo:P:T session is used as the management session for this component). It simply asks all the threads to shut down.


  • This component intentionally doesn't allow fine graned control over prepared statement objects.

  • Some types of data sharing in ithreads have been known to leak,

  • My tests have shown this component, as well as POE::Component::Pool::Thread, does not work with the forks pragma.

  • This module doesn't particularly support transactional operations.


Scott S. McCoy (