How to fetch data as hash of hashes in Perl DBI?

When using Perl's DBI module to fetch database query results, organizing data as a hash of hashes (HoH) keyed by a unique identifier is a common and effective technique. Each row of data is stored as a hash reference keyed by column names, and then the entire dataset is built into a nested structure for easy access.

A notable Perl-specific gotcha occurs when inserting string literals containing @ characters (such as email addresses) using double-quoted strings. Perl attempts to interpolate @ as an array variable, leading to compilation errors like Global symbol "@example" requires explicit package name. To prevent this, always use single quotes around such string literals or escape the @ with a backslash.

Key Perl/DBI Concepts

• fetchrow_hashref() returns a reference to a hash for the current row, keyed by column names.
• This hashref may be reused internally by DBI, so you must clone its contents (e.g., { %$row }) before storing it permanently.
• Using a primary key (like id) as the outer hash key enables fast lookups.
• Single quoting string literals avoids unwanted interpolation of sigils like @ or $.

Runnable Perl Example

use strict;
use warnings;
use DBI;

# Connect to an in-memory SQLite database
my $dbh = DBI->connect(
    "dbi:SQLite:dbname=:memory:",
    "",
    "",
    { RaiseError => 1, PrintError => 0 }
);

# Create table and insert sample data
$dbh->do("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)");
$dbh->do("INSERT INTO users (name, email) VALUES ('Alice', 'alice\@example.com')");
$dbh->do("INSERT INTO users (name, email) VALUES ('Bob', 'bob\@example.com')");

# Prepare and execute SQL query
my $sth = $dbh->prepare("SELECT id, name, email FROM users");
$sth->execute();

# Fetch rows as a hash of hashes keyed by 'id'
my %users;
while (my $row = $sth->fetchrow_hashref) {
    my $id = $row->{id};
    # Clone to avoid DBI reusing the same hashref
    $users{$id} = { %$row };
    delete $users{$id}->{id};  # remove redundant id from inner hash (optional)
}

# Print the results
for my $user_id (sort keys %users) {
    print "User ID: $user_id\n";
    while (my ($col, $val) = each %{ $users{$user_id} }) {
        print "  $col: $val\n";
    }
}

Summary

• Use single quotes or escape @ in string literals to prevent Perl interpolation errors.
• fetchrow_hashref returns internally reused references; clone before storing.
• Hash of hashes keyed by primary key is a clean pattern to organize DBI query results.
• Removing the key column from inner hashes is optional and up to your structure design.

Common Pitfalls

• Unescaped @ in double quotes causes compilation errors due to interpolation.
• Storing raw fetchrow_hashref references without cloning leads to overwritten data.
• Ensure your chosen hash keys are unique in the dataset to avoid collisions.

This approach works generally with any DBI-supported database and provides a flexible method to convert query results into nested Perl data structures for further processing.