How to test database code with Test::mysqld in Perl?

Testing Database Code Without Test::mysqld in a Sandbox

The Test::mysqld module is a convenient tool to spin up a temporary MySQL server for testing, but it requires external MySQL binaries and the module itself to be installed. Since neither network access nor filesystem access or external modules are allowed in your sandbox environment, using Test::mysqld is not feasible.

Instead, for sandboxed test examples or demonstration purposes, you can simulate database interaction with the core DBI module by using the built-in DBD::SQLite driver, which comes bundled with Perl distributions starting from 5.10. This allows you to create an in-memory SQL database without any external dependencies.

Why Use DBD::SQLite for Testing in a Sandbox?

• No extra Perl modules need installing beyond core DBI and DBD::SQLite.
• Runs a lightweight in-memory database, so no filesystem or network is required.
• Perfect for quick tests or examples of DBI code with standard SQL syntax.
• Supports most common SQL statements like CREATE TABLE, INSERT, SELECT.

Example: Testing Database Code Using DBI With an In-Memory SQLite Database

use strict;
use warnings;
use DBI;

# Connect to an in-memory SQLite database (no files, no external server)
my $dsn = "dbi:SQLite:dbname=:memory:";
my $user = "";
my $pass = "";

my $dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1, PrintError => 0 })
    or die "Could not connect to database";

# Create a test table
$dbh->do("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");

# Insert a row
$dbh->do("INSERT INTO users (name) VALUES (?)", undef, "Alice");

# Prepare and execute a query
my $sth = $dbh->prepare("SELECT id, name FROM users WHERE name = ?");
$sth->execute("Alice");

# Fetch the row back
my ($id, $name) = $sth->fetchrow_array;

# Print results to prove it worked
print "User id: $id, name: $name\n";

# Clean up
$sth->finish;
$dbh->disconnect;

Explanation

• DBI->connect is called with the SQLite DSN specifying :memory: to create a temporary in-memory database rather than connecting to MySQL.
• Perl sigils: Scalars like $dsn, $dbh, and $sth hold the database connection info, handle, and statement handle respectively.
• The code prepares and executes a statement with a placeholder (TMTOWTDI—using placeholders for clean SQL parameter binding).
• Fetching with fetchrow_array returns results in list context, here assigned to ($id, $name).
• Output is printed to standard output to verify the operation.

Common Gotchas

• SQLite syntax differs from MySQL in some advanced areas but core SQL works well for testing.
• Remember to set RaiseError to catch failures immediately.
• With SQLite in-memory, the database disappears when the handle disconnects—ideal for tests.

Summary

Without the ability or permission to use Test::mysqld or an actual MySQL server, DBI with DBD::SQLite offers an excellent alternative for writing and testing SQL-interacting Perl code in a sandboxed environment. This approach leverages Perl’s flexible DBI interface and familiar syntax, enabling quick experiments and reliable unit tests that can later be translated to MySQL as needed.