Here's something I've written for work and it's been extremely useful:
my $database = Test::Database->new;
my $dbh = $database->dbh;
What this does is read a config file which provides connection information for a database and the first thing it does is build a brand new test database. Internally, it instantiates a $dbh and loads a schema file, using Template Toolkit. The beginning of the file looks something like this:
DROP DATABASE IF EXISTS `[% database %]`;
CREATE DATABASE `[% database %]`;
use `[% database %]`;
CREATE TABLE `accounts` (
The load_static_data() method reads another SQL file which adds the static (non-changing) data to the database. This, of course, is optional and that's why it's a separate command. Then you can use your test database all you like. Further, when $database goes out of scope, the database is dropped -- but only if it's the parent process. Forking children and having them drop the database is disappointing.
Also, because you can specify your own test database name, every developer can easily use database tests and not worry about clashing with someone else.
It needs work. Specifically, it needs to not be *nix specific or MySQL specific. I'm thinking that the easiest thing to do would be to make it a base class and provide instructions on how to override certain methods.
Of course, it has helper methods for dumping table information and applying arbitrary SQL from files. It's saved us a huge amount of trouble at work and has the nice benefit that if you have a database server running, you're not just hacking up a quick SQLite database for testing (as many folks do).
My boss has agreed that it's not a core-business tool, so we can potentially release this to the CPAN. I'm surprised that I haven't found anything like this already.
Of course, there's also the matter of spare tuits. If anyone is willing to, I'd happily send the code along for you to fix up for a CPAN release under your own name.