perlancar

About the series: perlancar's 2014 Advent Calendar: Introduction to a selection of 24 modules which I published in 2014. Table of contents.

Here's one little module that can help you create dummy databases during testing: Test::WithDB. It's great together with Test::DatabaseRow (which happened to be featured in this year's Perl Advent Calendar by MARKF).

PS: I'm still not happy with the module name. It should probably not reside under Test::, or even contain any "Test" in its name. Suggestions welcome.

So what problem does this module try to solve and how to use this module?

During testing, often we need to create temporary databases that will be filled with tables and data, tested, then dropped again. If you use SQLite then this is a no-brainer as SQLite works with plain files, you can just point to a temporary file/directory created by File::Temp. However, if you use a client/server-based database then usually you'll need some administrator-level privileges to create and drop databases.

By using Test::WithDB (or TWDB for short) basically you specify this administrator connect information in a config file, then call create_db() method to create one or more databases with random names, that will automatically get dropped during DESTROY() (or explicitly with done()).

Here's an example of my configuration when testing a PostgreSQL-based application. In ~/twdb.ini:

[GLOBAL]
admin_dsn="dbi:Pg:host=localhost"
admin_user=postgres
admin_pass=secretsauce

user_dsn="dbi:Pg:host=localhost"

user_user=ujang

user_pass=pass12345

;!merge GLOBAL

[myapp1]

init_sql_admin=create extension citext

TWDB uses an INI flavor called IOD which supports include, among other things. You can see from the above example that we merge the GLOBAL section with the myapp1 section. To see how this config file will be loaded in Perl, we can use dump-iod (which you can get by installing App::IODUtils from CPAN). When we run dump-iod --naked-res ~/twdb.ini we'll get:

{
   "GLOBAL" : {
      "admin_dsn" : "dbi:Pg:host=localhost",
      "admin_pass" : "secretsauce",
      "admin_user" : "postgres",
      "user_dsn" : "dbi:Pg:host=localhost",
      "user_pass" : "pass12345",
      "user_user" : "ujang"
   },
   "myapp1" : {
      "admin_dsn" : "dbi:Pg:host=localhost",
      "admin_pass" : "secretsauce",
      "admin_user" : "postgres",
      "init_sql_admin" : "create extension citext",
      "user_dsn" : "dbi:Pg:host=localhost",
      "user_pass" : "pass12345",
      "user_user" : "ujang"
   }
}

Then, in your test file e.g. t/01-basic.t:

#!perl

use MyApp1;

use Test::DatabaseRow;

use Test::More;

use Test::WithDB;

$ENV{TWDB_CONFIG_PROFILE} = 'myapp1';

my $twdb = Test::WithDB->new;

my $dbh = $twdb->create_db;

MyApp1->init_db($dbh); # create tables

{

    local $Test::DatabaseRow::dbh = $dbh;

    all_row_ok(

        sql     => [ "SELECT * FROM staff WHERE first_name = ?", 'Santa' ],

        results => 1,

    );

    # ...

}

$twdb->done;

done_testing;

From the above, create_db() will return a DBI database handle connected to the newly created database using the normal user privileges (in config file: user_dsn, user_user, user_pass). This database also gets citext already activated (in config file: init_sql_admin).

After testing is done, done() will drop all databases if all tests are passing. If there are any failed test, then databases will not be dropped, ready for you to debug.