Persistent database connections with Catalyst

Question

My organization has a custom package for connecting to our database servers that takes care of trying various mirrors at random (according to a config file) and only trying the master database server for non-readonly connections or if none of the mirrors can be reached. I want to take that and use it to have persistent connections within a Catalyst app.

What I've tried is creating a Model package based on Catalyst::Model::DBI but that redefines that modules's connect() method to use our package's connection method. Then I have redefined "selectall_arrayref", "do" methods that use stay_connected()... It works ok but it's creating a new connection for every query - even during the same http request - although the documentation for Catalyst::Model::DBI seems to imply that the connection should be persistent.

So my question is, what SHOULD be happening? Do I need to do something different to get a persistent handle? would Model::DBI normally provide that? if so how do i graft this thing of ours into it, or is there a better easier way? I don't want to have to re-write our whole package to use DBIx::Class, I just want to plug in our routine that gives me a database handle that's right for our system.

package SpamControl::Model::Database;
use strict;
use Freecycle::Database;

use base 'Catalyst::Model::DBI';

# redefine the connect method, this is just copied from Model::DBI but  using the Freecycle package for the actual connection.
sub connect {
    my $self = shift;
    my $dbh;
    # TODO: I wish this could be a persistent connection.
    eval {
            $dbh = Freecycle::Database::connect({ username => 'member', read_only => 1 });
    };
    if ($@) { $self->{log}->debug( qq{Couldn't connect to the database "$@"} ) if $self->{debug} }
    else { $self->{log}->debug ( 'Connected to the database')  if $self->{debug}; }
    $self->_pid( $$ );
    $self->_tid( threads->tid ) if $INC{'threads.pm'};
    return $dbh;
}

# for read/write connections
sub dbh_admin {
    my ($self,$c) = @_;
    my $dbh = Freecycle::Database::connect({ username => 'admin', read_only => 0 });
    return $dbh;
}

sub do {
    my $self = shift;
    return $self->dbh_admin->do(@_);
}

sub selectall_hashref {
    my $self = shift;
    return $self->stay_connected->selectall_hashref(@_);
}

...etc etc.

emazep emazep · Accepted Answer · 2015-04-04T08:11:10

Catalyst::Model::DBI provides persistent connections and automatic re-connections (through DBIx::Connector nowadays).

If you have a module that already does that (Freecycle::Database?) you should not use Catalyst::Model::DBI at all, but rather use your module as a Catalyst model, for which Catalyst::Model::Adaptor can help a lot.

Persistent database connections with Catalyst

1 Answers