The problem

Most of those methods have pros and cons:

• using SQLite

  No setup needed, but the test script can only use SQLite's dialect of SQL. So much for portability across database engines.

• using environment variables

  The environment variables are different for every module to test, and usually only the main developers/testers know about them. Since most of the CPAN testers probably don't bother setting them up, these modules are most certainly undertested.

• using default credentials

  Typically using 'root' and '' to connect to the "test" MySQL database, these test script assume a default installation on the host system. These credentials often provide full access to the database engine, which is a security risk in itself (see below).

• setting up the database by itself

  This method usually uses the default credentials to access an account with enough privileges to create a database. The host system data may be at risk!

A solution: Test::Database

Wouldn't it be nice to be able to test on all the supported databases that are available on the test system? Without breaking (into) anything?

This is the goal of the Test::Database module. It supports:

• getting DSN information from a list of pre-configured database and engines
• automatic detection of "file-based" database engines (typically, SQLite).

The rest of this document describes various use cases for Test::Database.

Specific database support

    use Test::Database;

    # request database handles for all available databases
    my @handles = Test::Database->handles();

    # or for only the databases we support
    my @handles = Test::Database->handles(
        { dbd => 'SQLite' },
        { dbd => 'SQLite2' },
        { dbd => 'mysql', min_version => '4.0' },
    );

See Test::Database documentation for details about how to write a request.

Testing on a development box

A typical ~/.test-database configuration file would look like this:

    dsn      = dbi:mysql:database=test
    username = root

    dsn      = dbi:Pg:database=test
    username = postgres

    dsn      = dbi:Oracle:test

There is no need to add "dsn" sections for file-based drivers (at least the ones that have a corresponding Test::Database::Driver), since the module will automatically detect the available ones and create databases as needed.

To find out which of the DBD that Test::Database supports are installed, use the following one-liner:

    $ perl -MTest::Database -le 'print for Test::Database->list_drivers("available")'
    DBM
    SQLite
    mysql

With no parameter, it will return the list of configured ones:

    $ perl -MTest::Database -le 'print for Test::Database->list_drivers()'
    DBM
    SQLite

"dsn" versus "driver_dsn"

"driver_dsn" sections define the information needed to connect to a database engine (a "driver") with sufficient rights to run a "CREATE DATABASE" command. This allows Test::Database to create the databases on demand, thus ensuring every test suite will get a specific database.

If you have file-based database engine, there is nothing to setup, as Test::Database is able to detect available file-based engines and use them as needed.

Other database engines like "mysql" and "Pg" require a little more configuration. For example, here's the content of my ~/.test-database configuration file:

    driver_dsn  = dbi:mysql:
    username    = root
    
    driver_dsn  = dbi:Pg:
    username    = postgres

For "Pg", I had to edit the pg_hba.cong file in /etc to make sure anyone would be able to connect as the "postgres" user, for example.

Several test hosts accessing the same database engine

Databases created by Test::Database::Driver (using a configured "driver_dsn" have a name built after the following template: "tdd_driver_login_n", where driver is the DBD name, login is the login of the user running Test::Database and n a number that

If the same database server is used by several host running Test::Database from the same user account, there is a race condition during with two different host may try to create the a database with the same name. A simple trick to avoid this is to add a "key" section to the ~/.test-database configuration file.

If the "key" entry exists, the template used by Test::Database::Driver to create new databases is "tdd_driver_login_key_n".

Cleaning the test drivers

After a while, your database engine may fill up with unused test databases.

All drivers store their mapping information in the system's temporary directory, so the mapping information is relatively volatile, which implies more unused test databases (at least for non file-based drivers, since the file-based drivers store their database files in the system's temporary directory too).

The following one-liner will list all the existing databases that were created by Test::Database in your configured drivers:

    perl -MTest::Database -le 'print join "\n  ", $_->name, $_->databases for Test::Database->drivers'

Example output:

    CSV
      tdd_csv_book_0
      tdd_csv_book_1
    DBM
    SQLite
      tdd_sqlite_book_0
      tdd_sqlite_book_1
    SQLite2
      tdd_sqlite2_book_0
    mysql
      tdd_mysql_book_0
      tdd_mysql_book_1

The following one-liner will drop them all:

    perl -MTest::Database -le 'for$d(Test::Database->drivers){$d->drop_database($_)for$d->databases}'

If a "key" has been defined in the configuration, only the databases corresponding to that key will be dropped.