Mojolicious and connection to a database

frazap has asked for the wisdom of the Perl Monks concerning the following question:

My small Mojolicious app has the following structure:

+lib
   MyLib.pm
   +MyLib
        +Controller
             Invtot.pm
+public
    js files...
+script
     my_lib.pl
+t
    basic.t
+templates
       ....
      +layouts
       ....
[download]

It's composed of two pages, one with a form that need to connect to a database. I will a add an other form in a third page, that will connect to a second database. I came with the following (that works) to connect to my first db :

use a callback to the get route of the form to connect
rember the connection in hash $connections{my_db}=1;, so that the next time the page is displayed the connection is not made again if already done.

That way I could connect to different db without having all the connection done in the startup

The main module MyLib.pm is

package MyLib;
use Mojo::Base 'Mojolicious';

my %connections;

# This method will run once at server start
sub startup {
    my $self = shift;

    # Router
    my $r = $self->routes;

    # Normal route to controller
    $r->get('/')->to( template => 'index' );
    $r->get('/libmap');    #->to(controller => 'libmap');
    $r->get('/invtot')->to(
        controller => 'invtot',
        title      => 'Paper journals from the library',
        cb         => sub { $self->connect_invtot() }
    );

    if ( exists $ENV{PAR_TEMP} && $^O eq "MSWin32" ) {
        system qw(start http://localhost:3000/invtot);
    }

    $r->post('/invtot')->to( controller => 'Invtot', action => 'post' 
+);

}

sub connect_invtot {

    my $self = shift;
    return if $connections{db1};

    # Load configuration from hash returned by config file
    my $file;
    if ( $ENV{PAR_TEMP} ) {
        print $ENV{PAR_TEMP}, "\n";
        $file = $ENV{PAR_TEMP} . "/inc/script/my_lib.conf";
    }
    else {
        $file = 'my_lib.conf';
    }
    my $config = $self->plugin( 'Config', { file => $file } );

    # Configure the application
    $self->secrets( $config->{secrets} );

    $self->log->debug("connecting...");
    $self->plugin(
        'Database',
        {   dsn      => 'dbi:mysql:host=mysql....:dbname=d....',
            username => $config->{username},
            password => $config->{password},
            options  => {
                'pg_enable_utf8' => 1,
                AutoCommit       => 1,
                PrintError       => 0,
                RaiseError       => 1
            },
            helper => 'db1',
        }
    );
    $connections{db1} = 1;
}

1;
[download]

My question: even if that works, is there something that could break in the long term, something I forgot here?

Thanks

Comment on Mojolicious and connection to a database Select or Download Code

Replies are listed 'Best First'.
Re: Mojolicious and connection to a database by trippledubs (Deacon) on May 03, 2019 at 12:16 UTC
looks great. Conditionally loading a config. Might be able to make it more resilient by making sure what you think is there, like "/inc/script/my_lib.conf". You are checking $ENV{PAR_TEMP} to see if it's set, but not checking to see if it's set correctly. Is the file there? Does it have all the data you need? Does it bomb out correctly when somethings wrong? Assume that something will go wrong, now how long is it going to take to find out why with the default error messages? Break it imaginatively and just kinda see what happens.	[reply]
Re^2: Mojolicious and connection to a database by frazap (Monk) on May 03, 2019 at 13:02 UTC
Well, loading the config file that way is to make sure the app works when packed with pp. It's no big deal once it's tested. My question was more in relation with remembering the connection done using the hash. Could the connection be broken by the database server when the page has been load ? If yes, can I check this rather then using the hash value ? Thanks for helping anyway !	[reply]
Re^3: Mojolicious and connection to a database by bliako (Monsignor) on May 04, 2019 at 08:05 UTC
What if your database server crashes/shuts down/restarts after you run the `plugin()` to connect to it? `%connections` flag will not tell you that. In general, one can `ping()` prior to, or `eval()` on each db operation and catch the error if any. So perhaps convert this flag to a sub which pings the db-handle you think you have and return the result. See check if DBI is still connected.	[reply] [d/l] [select]
Re^3: Mojolicious and connection to a database by trippledubs (Deacon) on May 03, 2019 at 14:26 UTC
hmm.. maybe try testing by putting a load on the web server and see how many connections you have to the database? I would think if that part is not working right you would see either increasing number of connections to the database up to some ceiling or your pages not getting data from the database. If I understand your concern correctly.	[reply]
Re: Mojolicious and connection to a database by Anonymous Monk on May 06, 2019 at 16:41 UTC
The Mojo::mysql module will hold active connections for you in a fork safe manner. Alternatively DBIx::Connector is a database-agnostic way to wrap DBI for this. In either case, you can store the Mojo::mysql/DBIx::Connector object globally and retrieve a handle from it in each discrete action, and it will only reconnect if necessary.	[reply]
Re^2: Mojolicious and connection to a database by frazap (Monk) on May 08, 2019 at 06:24 UTC
Thanks for the suggestion MyLib.pm is package MyLib; use Mojo::Base 'Mojolicious'; use Mojo::mysql; #my %connections; # This method will run once at server start sub startup { my $self = shift; # Router my $r = $self->routes; # Normal route to controller $r->get('/')->to( template => 'index' ); $r->get('/libmap'); #->to(controller => 'libmap'); $r->get('/invtot')->to( controller => 'invtot', title => 'Paper journals from the library', cb => sub { $self->connect_invtot() } ); if ( exists $ENV{PAR_TEMP} && $^O eq "MSWin32" ) { system qw(start http://localhost:3000/invtot); } $r->post('/invtot')->to( controller => 'Invtot', action => 'post' +); } sub connect_invtot { my $self = shift; #return if $connections{db1}; # Load configuration from hash returned by config file my $file; if ( $ENV{PAR_TEMP} ) { print $ENV{PAR_TEMP}, "\n"; $file = $ENV{PAR_TEMP} . "/inc/script/my_lib.conf"; } else { $file = 'my_lib.conf'; } my $config = $self->plugin( 'Config', { file => $file } ); # Configure the application $self->secrets( $config->{secrets} ); $self->log->debug("connecting..."); $self->helper(db1 => sub {state $db1 = Mojo::mysql->strict_mode('m +ysql://'. $config->{username} . ':'. $config->{password} . '@my.../d +okpe_i01' )}); $self->db1->options({ 'pg_enable_utf8' => 1, AutoCommit => 1, PrintError => 0, RaiseError => 1 }); } 1; [download] In my Invtot.pm controller the post method is `sub post { my $c = shift; #get data from the form and build sql in $sql{$key} my $stm = $c->app->db1->db->prepare_cached( $sql{$key} ) or die $c->app->db1->db->errstr; # unless(exists $sql{$ +key}); $toFind = format_search_arg( $toFind, $mode ); $c->app->log->debug( $mode . " " . $toFind ); $stm->execute($toFind) or die $stm->errstr; $c->stash( stm => $stm, for_abo => $for_abo ); }` [download] But this failed with `Can't locate object method "db1" via package "MyLib" at ...` Thanks for any help	[reply] [d/l] [select]