The current "best practices" are to use a framework, like Mojolicious, Catalyst, or Dancer. These run as a service that handles incoming requests from a more robust and battle-hardened front-end proxy like Apache or Nginx or Traefik. Each framework has its own method of managing one single database connection object (per worker), usually wrapped with DBIx::Class and usually persistent through the life of the worker and with built-in code that creates a new connection and re-runs your query if the connection drops in the middle of a query. This is the state-of-the-art for Perl web development, and actually fairly closely mirrors the state-of-the-art in Python or Ruby. Mojo is actually a bit ahead of the curve, here.

But, since you are trying to fix an old script, you might not be interested in totally rewriting it with those new tools. So assuming you are running a process per request like classic CGI, you just need to create some kind of global database handle and then refer back to it any time you need to run a query. There are modules that can do this for you, but you *could* do something as simple as this: 

// in the main CGI script
sub dbh {
  $main::dbh ||= DBI->connect(...);
}

[download]
 main::dbh()->selectall_arrayref(...)

[download]
where you just refer to main::dbh() every time you want to access your connection.

Solutions using purpose-build modules will be prettier than this, but also have a little more learning curve.

As a side-note, I 100% agree with everyone else that you should unconditionally use query placeholders for every query.

In reply to Re: Best practices for closing database connections? by NERDVANA
in thread Best practices for closing database connections? by Polyglot

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.