Perl is built with testing in mind from the start. All the tools someone would like to have to do testing are there. Almost every module on CPAN has a directory t , containing test files. Writing one’s own test files is not really hard, once familiar with the tools.

Testing a web application written with Dancer is also really convenient with Plack::Test and PSGI. The usual way would be to use a SQL Lite database as a test database, which can be created or teared down pretty easily.

However, a SQLite database can’t always do the trick. If the website/API is using for instance database schema or any other functionality that SQLite does not support. In that case, we need to find a way to test with a PostgreSQL server.

To illustrate that, let’s write a really small API with Dancer 2, that will manage a film collection. To keep it simple, we will just keep a list of films with their title. The database engine used being PostgreSQL:

Let’s start with the database schema:

CREATE TABLE movies (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

The API will contain only resource movies, with possibility to add, get and delete a movie. A simple implementation is given as an example, at the end of this post, however, the interesting part is the testing. One point to note, the plugin Dancer2::Plugin::Database is used to access the database, connection parameters are in environments/development.yml

But first things first, what does our api do:

Add a movie to the database:

POST /movies

> curl -X POST -H 'content-type: Application/json' -d '{"title":"Star Wars Episode IV: A New Hope"}' http://localhost:5000/movies
> curl -X POST -H 'content-type: Application/json' -d '{"title":"Star Wars Episode V: The Empire Strikes Back"}' http://localhost:5000/movies

Get list of movies:

GET /movies

> curl http://localhost:5000/movies
{"movies":[{"id":1,"title":"Star Wars Episode IV: A New Hope"},{"id":2,"title":"Star Wars Episode V: The Empire Strikes Back"}]}

Get details of a specific movie

GET /movies/:id

> curl http://localhost:5000/movies/2
{"title":"Star Wars Episode V: The Empire Strikes Back","id":2}

Testing the API:

To write tests for that api, as it is using Dancer2, the easyest way is to use Plack::Test, as explained there: http://advent.perldancer.org/2014/12

However a database is needed for test here, and we always want to test with known data. A simple solution here is to use a SQLite database.

Using a SQLite database means the db will act a bit differently. Still, this is better than nothing. We create a unit test configuration environment/unit.yml

logger: Null
plugins:
  Database:
    driver: SQLite
    database: 'unit.sqlite'

then in our test script, we can initialize a new database at startup, in the following sample, the testApi is included in the test file, but it would be better to extract it to a separate file, included in every test files.

t/010-basic.t

use strict;
use warnings;
use Plack::Test;
use HTTP::Request::Common;
use JSON;
use Test::More;

testApi::prepare_db();

my $app = testApi::get_psgi_app();
my $test = Plack::Test->create($app);

my $request  = GET  '/movies';
my $response = $test->request($request);
is $response->code, 200, "Movies route is working";
my $decoded = from_json($response->content);
is scalar( @{$decoded->{movies}} ), 0, "No movies";

$request = POST '/movies', Content_Type => 'application/json', Content => to_json( { title => 'Star Wars Episode VII: The Force Awakens'} );
$response = $test->request($request);
is $response->code, 201, "Movie created";

$request  = GET  '/movies';
$response = $test->request($request);
is $response->code, 200, "Movies route is working";
$decoded = from_json($response->content);
is scalar( @{$decoded->{movies}} ), 1, "No movies";
is $decoded->{movies}->[0]->{title}, "Star Wars Episode VII: The Force Awakens", "Movie retrieved succesfully";

$request  = GET  '/movies/'.$decoded->{movies}->[0]->{id};
$response = $test->request($request);
is $response->code, 200, "Detail route is working";
$decoded = from_json($response->content);
is $decoded->{title}, "Star Wars Episode VII: The Force Awakens", "Movie retrieved succesfully";

done_testing();

package testApi;
BEGIN {
    $ENV{DANCER_ENVIRONMENT} = "unit";
}
use Dancer2 appname => 'Api';
use Dancer2::Plugin::Database;
use Api;
sub prepare_db {
    my $dbh = database();
    $dbh->do("DROP TABLE IF EXISTS movies");
    $dbh->do("
        CREATE TABLE movies (
            id integer primary key autoincrement,
            title string not null
        );
    ");
}

sub get_psgi_app {
    return Api::to_app();
}
1;

Pretty simple here, we just force the unit environment in BEGIN block and call the database creation before running the test.

Testing with PostgreSQL

Even if the SQLite solution is working, testing an application that is planned to run with a PostgreSQL database with SQLite is far from perfect, but does not need a lot of prerequisite, it’s easy to test locally. As soon as more advanced functionalities are needed, it does not work anymore.

To test the API with a PostgreSQL database, one solution is to:

• have a PostgreSQL server running locally or on a server
• have a user that has enough permission to create a new db, and drop it
• generate a new database for each test run
• drop that database at the end of the test

The idea is to generate a unique database name at the start of the test script, create the database, provision it. After that, all the tests are run and then the database is dropped. However, we want the Database to be dropped even if the tests crash, an END block will ensure that.

We create a unit test configuration environment/unitpg.yml

logger: Null
plugins:
  Database:
    driver: 'Pg'
    database: 'local'
    host: 'server'
    port: 5432
    username: 'user'
    password: 'password'
    dbi_params:
      RaiseError: 1
      AutoCommit: 1
      pg_enable_utf8: 1
      pg_server_prepare: 0

In the test file, the configuration is modified dynamically to use the generated database. There is a bit of boiler plate, but if extracted in a library, that system allows to test in an environment close to production.

use strict;
use warnings;
use Plack::Test;
use HTTP::Request::Common;
use JSON;
use Test::More;

my $app = testApi::get_psgi_app();
my $test = Plack::Test->create($app);

my $request  = GET  '/movies';
my $response = $test->request($request);
is $response->code, 200, "Movies route is working";
my $decoded = from_json($response->content);
is scalar( @{$decoded->{movies}} ), 0, "No movies";

$request = POST '/movies', Content_Type => 'application/json', Content => to_json( { title => 'Star Wars Episode VII: The Force Awakens'} );
$response = $test->request($request);
is $response->code, 201, "Movie created";

$request  = GET  '/movies';
$response = $test->request($request);
is $response->code, 200, "Movies route is working";
$decoded = from_json($response->content);
is scalar( @{$decoded->{movies}} ), 1, "No movies";
is $decoded->{movies}->[0]->{title}, "Star Wars Episode VII: The Force Awakens", "Movie retrieved succesfully";

$request  = GET  '/movies/'.$decoded->{movies}->[0]->{id};
$response = $test->request($request);
is $response->code, 200, "Detail route is working";
$decoded = from_json($response->content);
is $decoded->{title}, "Star Wars Episode VII: The Force Awakens", "Movie retrieved succesfully";

done_testing();

package testApi;
use feature qw/state/;
use DBI;

BEGIN {
    $ENV{DANCER_ENVIRONMENT} = "unitpg";
}
use Dancer2 appname => 'Api';
use Dancer2::Plugin::Database;
use Api;
use DBI;

my ($TEST_DB_HOST,$TEST_DB_USER,$TEST_DB_PASS,$TEST_DB_NAME);

END {
    if ( _db_exists( $TEST_DB_NAME ) ) {
        #kill connection
        _db_conn()->do("
            SELECT pg_terminate_backend(pg_stat_activity.pid)
            FROM pg_stat_activity
            WHERE pg_stat_activity.datname = ?
              AND pid <> pg_backend_pid()
        ",{},$TEST_DB_NAME);
        _db_conn()->do('DROP DATABASE '.$TEST_DB_NAME);
    }
    _db_conn()->disconnect() if _db_conn()->connected();
}

sub create_test_db {
    $TEST_DB_HOST = "localhost";
    $TEST_DB_USER = "test";
    $TEST_DB_PASS = "test";
    $TEST_DB_NAME = "test_api_".time();
    my $admin_dbh = _db_conn();
    $admin_dbh->do("DROP DATABASE $TEST_DB_NAME") if _db_exists($TEST_DB_NAME);
    $admin_dbh->do("CREATE DATABASE $TEST_DB_NAME");
    _db_conn( $TEST_DB_NAME )->do("
        CREATE TABLE movies (
            id SERIAL PRIMARY KEY,
            title TEXT NOT NULL
        );
    ");
}

sub _db_conn {
  my $db_name = $_[0] // 'postgres';    # optional, admin DB connection if unset
  state %dbh_connections;
  $dbh_connections{$db_name} //= DBI->connect("dbi:Pg:database=$db_name;host=$TEST_DB_HOST", $TEST_DB_USER, $TEST_DB_PASS, { PrintError => 0, RaiseError => 1 });
  return $dbh_connections{$db_name};
}

sub _db_exists {
  my ( $db_name ) = @_ or die 'db_name is required';
  return (_db_conn()->selectrow_array('SELECT count(*) FROM pg_database WHERE datname = ?', {}, $db_name))[0];
}

sub get_psgi_app {
    create_test_db();

    my $config = config;
    $config->{plugins}->{Database}->{database} = $TEST_DB_NAME;
    $config->{plugins}->{Database}->{host} = $TEST_DB_HOST;
    $config->{plugins}->{Database}->{username} = $TEST_DB_USER;
    $config->{plugins}->{Database}->{password} = $TEST_DB_PASS;

    return Api::to_app();
}
1;

Conclusion

Testing a website written with a PSGI server is pretty easy using tools like Plack::Test. However, testing with a PostgreSQL database is not that easy. That post contains a few tips that might help for that.

Sample implementation:

lib/Api.pm

package Api;
use Dancer2;
use Dancer2::Plugin::Database;

get '/movies' => sub {
    my @list = database->quick_select('movies',{});
    return {
        movies => \@list
    };
};

get '/movies/:id' => sub {
    my $movie = database->quick_select('movies',{ id => param( 'id' ) });
    if( $movie ) {
        return $movie;
    } else {
        status 404;
        return {};
    }
};

post '/movies' => sub {
    my $title = param( 'title' );
    if( defined $title ) {
        database->quick_insert('movies',{ title => $title });
        status 201;
        return {};
    } else {
        status 400;
        return { error => 'Title is mandatory' };
    }
};

true;

config.yml

serializer: JSON

environments/development.yml

plugins:
  Database:
    driver: 'Pg'
    database: 'local'
    host: 'localhost'
    port: 5432
    username: 'test'
    password: 'test'
    dbi_params:
      RaiseError: 1
      AutoCommit: 1
      pg_enable_utf8: 1
      pg_server_prepare: 0

bin/app.psgi

#!/usr/bin/env perl

use strict;
use warnings;
use lib "lib";

use Api;
Api->to_app;