NAME
Database::ManagedHandle - Manage database handles safely for long running processes
VERSION
version 0.002
SYNOPSIS
# Create a class file from which the configuration can be read:
package Database::ManagedHandleConfig;
use Moo;
has config => (
is => 'ro',
default => sub {
return {
default => q{db1},
databases => {
db1 => {
dsn => "dbi:SQLite:uri=file:/tmp/first_db.sq3?mode=rwc",
username => undef,
password => undef,
attr => {},
},
db2 => {
dsn => 'dbi:Pg:dbname=db;host=go;port=5432;application_name=MyApp',
username => 'user',
password => 'pass',
attr => { ReadOnly => 0, AutoCommit => 0, PrintError => 0 },
},
},
};
},
);
1;
# In your program code:
use Database::ManagedHandle;
my $mh1 = Database::ManagedHandle->instance;
my $dbh1 = $mh1->dbh();
my $ary_ref = $dbh1->selectall_arrayref( 'SELECT current_timestamp()' );
# Another example:
Database::ManagedHandle->instance()->dbh('db2')->do( 'INSERT INTO t VALUES(1,2,3)' );DESCRIPTION
Database::ManagedHandle is built for those long running web services or scripts which can lose a database connection due to network issues, database being updated, database itself closing unused connections after an idle period or any other reason.
Database::ManagedHandle uses Moo and specifically the MooX::Singleton role to become a Singleton. This ensures that there is always only one instance of the class in the entire running program. This in turn means that the program needs only one database handle and it is accessible from any part of the code.
Database::ManagedHandle opens and reopens database handles when required. It can house several handles. If there is more than one, then one handle needs to be defined as the default.
When the program first requests a database handle, either a named handle or the default, Database::ManagedHandle opens the database connection and passes the opened handle to the program. After using the handle, the program does not need to worry about it. It can safely let the variable fall out of scope.
During subsequent calls for the handle, Database::ManagedHandle first ensures that the connection is still alive. If not, it will establish the handle again.
Do not keep a database handle around. Only use the same handle for one operation, then purposefully undef it or let it drop out of scope. When you need it again, get it from Database::ManagedHandle.
Configuration
Database::ManagedHandle reads its configuration from a class. By default, the class name is Database::ManagedHandleConfig. Alternatively, set environment variable DATABASE_MANAGED_HANDLE_CONFIG, e.g. DATABASE_MANAGED_HANDLE_CONFIG=MyHandles.
The configuration class must have two methods: new() and config(). Neither takes any arguments. config() returns a hash which has the required information. See "SYNOPSIS" for an example of how to do this as a Moo class.
Logging
Database::ManagedHandle uses the excellent Log::Any to produce logging messages.
The easiest way to get the logging messages printed is to add the following line in the preamble of your program:
use Log::Any::Adapter ('Stdout', log_level => 'debug' );Alternative, you can do this on the command line:
perl '-MLog::Any::Adapter(Stdout, log_level=>trace)'METHODS
dbh
Get the default database handle.
my $dbh = Database::ManagedHandle->instance()->dbh();Get a database handle by its name.
my $mh = Database::ManagedHandle->instance;
my $dbh = $mh->dbh( 'db_example' );THANKS
Big thanks for Dancer::Plugin::Database for being an inspiration and example on how to verify database connection is still working.
AUTHOR
Mikko Koivunalho <mikkoi@cpan.org>
COPYRIGHT AND LICENSE
This software is copyright (c) 2023 by Mikko Koivunalho.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.