How to connect to MySQL database using Perl DBI?

Connecting to a MySQL database using Perl's DBI module requires the MySQL driver DBD::mysql which is an external module usually installed via CPAN or your OS package manager. If this module is not installed or unavailable (as in many sandboxed or minimal environments), attempting a MySQL connection will fail.

Since your environment does not have DBD::mysql installed and no network/filesystem access is allowed to install it, a direct MySQL connection isn't possible in this sandbox. However, for demonstration purposes, you can use DBD::SQLite, which is included with Perl core since 5.10 and requires no external dependencies or installation. This lets you practice DBI concepts with a lightweight in-memory database that runs entirely in memory and prints results to STDOUT.

Using DBI with an In-Memory SQLite Database (Sandbox-Friendly Example)

This example mimics connecting, creating a table, inserting data, querying, and disconnecting, all within an ephemeral SQLite database. This approach lets you practice DBI without external drivers or MySQL setup.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

# Connect to an in-memory SQLite database (no external files)
my $dsn = "DBI:SQLite:dbname=:memory:";
my $user = "";
my $password = "";

# Connect with RaiseError to catch any errors
my $dbh = DBI->connect($dsn, $user, $password, {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
});

print "Connected to SQLite in-memory database successfully.\n";

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

# Insert some data using placeholders (safe from SQL injection)
my $insert_sth = $dbh->prepare("INSERT INTO users (name) VALUES (?)");
$insert_sth->execute("Alice");
$insert_sth->execute("Bob");
$insert_sth->finish();

# Select and fetch the data
my $select_sth = $dbh->prepare("SELECT id, name FROM users ORDER BY id");
$select_sth->execute();

print "User data:\n";
while (my $row = $select_sth->fetchrow_hashref) {
    print "  User ID: $row->{id}, Name: $row->{name}\n";
}

$select_sth->finish();

# Disconnect cleanly
$dbh->disconnect;
print "Disconnected from database.\n";

Explanation

• The DSN DBI:SQLite:dbname=:memory: creates a temporary in-memory SQLite database with no persistent storage.
• $dbh is a scalar holding the database handle – a DBI convention where $ sigil means a scalar.
• RaiseError => 1 makes DBI die with an exception on errors, simplifying error handling.
• We use placeholders (?) in prepare statements to safely insert data, avoiding SQL injection risks.
• The while loop uses scalar context to fetch each row as a hashref, from which we print fields.
• No external modules beyond core Perl and DBI are required here; this runs out of the box.

Common Pitfalls & Notes

• Missing DBD driver: Always ensure you have the appropriate DBD::* driver installed for your database (MySQL needs DBD::mysql).
• Context matters: DBI methods return different results in scalar vs. list context. For example, fetchrow_hashref returns a hash reference in scalar context.
• Placeholder usage: Always use placeholders in SQL for dynamic values to avoid injection and syntax errors.
• Perl sigils: Scalars ($) store single values/handles; arrays (@) store lists.
• TMTOWTDI: "There's More Than One Way To Do It" – DBI lets you prepare/execute separately or combine calls via $dbh->do().

In summary, while connecting to MySQL requires DBD::mysql and a running MySQL instance, you can use DBI with SQLite for quick, sandbox-friendly database demos that require no external setup. You can adapt the same DBI patterns to MySQL once the appropriate modules and server are available.