Creating a database module in Perl

I wanted to know if this is the correct way to create a database module, particularly the way the database handle is created and used:

use strict;
use warnings;
use DBI;

my $DB_NAME = 'dancerapp';
my $DB_USER = 'root';
my $DB_PASS = 'root';

sub new {
    my $class = shift;
    return {}, $class;
}

sub _connect { #use connect_cached() instead?
    my $dbh = DBI->connect($DSN, $DB_USER, $DB_PASS) or die $DBI::errstr;
    return $dbh;
}

sub getTickets {
    my $self = shift;
    my $ticket_holder = shift;
    my $dbh = $self->_connect;
    my $sth = $dbh->prepare("SELECT * FROM table WHERE assigned_to=?");
    $sth->execute($ticket_holder);
    return $sth->fetchall_hashref;
}

1;

The main purpose of storing all my database queries in one module is to have a single place for them, I'm just concerned with having multiple connections/database handles flying around everywhere

Solution

In the Perl tradition, there are many ways to do this. But I would move as many one-off actions as possible into the constructor.

A couple of specific problems with your code

You need a package (class) name for your module
Your constructor needs to bless the hash reference, not just return it
Your don't define $DSN
Your getTickets method has no $dbh to use. The handle should be stored in the object when DBI->connect is called

Apart from that, I see no reason to have a separate connect method. That is something you can move into the constructor method

There is also no point in repeatedly calling prepare every time you want to perform a query. Instead, cache the statement using ||= to save the processing time on queries used often.

Finally, it is best to pass the DB name, user and pass as parameters to new. That way you can use the same module for multiple databases without resorting to editing the module to change environments.

This is something like what I would write. Note that it's untested beyond ensuring that it will compile. After using it for a while you may find you want to move more information into the object - the DSN for instance - if only as an aid to debugging.

package MyDB;

use strict;
use warnings;

use DBI;

sub new {
    my $class = shift;

    my $self = {};
    @{$self}{qw/ db user pass /} = @_;

    my $dsn = "DBI:mysql:database=$self->{db}";
    $self->{dbh} = DBI->connect($dsn, @{$self}{qw/ user pass /}) or die $DBI::errstr;

    bless $self, $class;
}

sub get_tickets {
    my $self = shift;
    my ($ticket_holder) = @_;

    $self->{get_tickets} ||= $self->{dbh}->prepare('SELECT * FROM table WHERE assigned_to = ?');
    $self->{get_tickets}->execute($ticket_holder);

    $self->{get_tickets}->fetchall_hashref;
}

1;