Creating a database module in Perl

Question

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

Miller · Accepted Answer · 2014-08-04 06:57:21Z

2

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;

edited Aug 4, 2014 at 6:57

Miller

35.3k4 gold badges42 silver badges61 bronze badges

answered Aug 3, 2014 at 13:14

Borodin

127k9 gold badges72 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

a7omiton Over a year ago

I never thought about placing all my statements in the constructor before, but that makes sense now completely as you wouldn't want to prepare something multiple times if it's the same. Thanks also for the OO corrections :)

Miller Over a year ago

Actually, instead of putting them in the constructor, just cache the queries using ||= so they aren't recalculated. This is better since it leaves the prepared statements near the code that actually uses them instead of isolated in the constructor.

Eric Redon · Accepted Answer · 2014-08-03 13:19:14Z

1

There is no absolute "correct way" to create a database module. The way you went for it seems like a good starting point, especially if it works out for you in its current shape, however you will probably need a few iterations based on your own needs to refine the module and its features.

If I may offer some opinionated pointers:

Having a reference module for handling your database connections is a great idea. Make it as easy to use "out-of-the-box" as possible if you need to get wide adoption. You wouldn't want an "XKCD: Standards" kind of situation to happen.
Having all database queries in a single module also has some merits, however depending on how big your needs grow, you may soon end up with an unmaintainable multi-thousand lines monster. So ordering/classifying them in smaller submodules from the get go might save you from that pain.
Pushing the previous comment further, if the queries are directly related to the behaviour of some objects of your system, consider using an ORM such as DBIx::Class instead.
Separate the configuration (DSN, username, password) from the module. This is especially useful for not accidentally testing your bleeding-edge developments against a production instance.
Use a set of dedicated, explicit parameters to pass in to DBI and the underlying driver (eg. AutoCommmit => 0), optionally allowing the user to override a few.
If your DBMS supports transactions, ensure that your module lets no loose transaction behind by always committing or rolling them back. Alternatively, you could make the user responsible to explicitly use commit or rollback, and die() really hard if they don't. That will save you a lot of pain when a lot of other concurrent modules consume the database.
connect_cache() has merits, but it could bring up its own set of concurrency problems.

Also, if your module is intended to be used in a web application, be aware that Apache::DBI might take liberties with the way you connect.

answered Aug 3, 2014 at 13:19

Eric Redon

1,76212 silver badges21 bronze badges

2 Comments

a7omiton Over a year ago

Thanks, this is so helpful! I don't think it's right to accept one answer over the other for my question here, as these are both very helpful and answer my question :)

Borodin Over a year ago

@a7omiton: Remember that the primary purpose of your question is to help others that have a similar problem. So it is best to accept an answer so that the question is marked as resolved. But you may want to hold off for a day or so in case an even better solution comes along.

Collectives™ on Stack Overflow

Creating a database module in Perl

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related