How to fetch data row by row using Perl DBI fetchrow_hashref?

When fetching data row by row using Perl's DBI module, fetchrow_hashref provides a convenient way to retrieve each row as a hash reference. The keys in this hash correspond to column names, making your code more readable and easier to maintain than using array indexes.

How to Use fetchrow_hashref

You need to prepare and execute your SQL statement, then call fetchrow_hashref inside a while loop to iterate over each row. This method returns a hash reference on each call or undef when no more rows remain, which naturally ends the loop. Because it returns a reference, the variable uses the scalar $ sigil.

One tricky aspect when inserting string literals in SQL statements is Perl's interpolation of strings with @ in double quotes. If you use double-quoted strings in Perl and include an email like 'alice@example.com', Perl tries to interpolate @example as an array, causing errors. To avoid this, use single-quoted strings for SQL literals inside Perl double quotes or escape the @.

The fixed example below connects to an in-memory SQLite database (no external dependencies or file access), creates a sample table, inserts data, then fetches rows printing their contents one by one.

use strict;
use warnings;
use DBI;

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

# Create table and insert data - use single quotes inside SQL for string literals to avoid interpolation
$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\', NULL)');
$dbh->do('INSERT INTO users (name, email) VALUES (\'Carol\', \'carol@example.com\')');

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

# Fetch rows one by one as hash references
while (my $row = $sth->fetchrow_hashref) {
    my $id    = $row->{id};
    my $name  = $row->{name};
    my $email = defined $row->{email} ? $row->{email} : '(NULL)';

    print "User ID: $id, Name: $name, Email: $email\n";
}

$sth->finish;
$dbh->disconnect;

Key Points and Pitfalls

• Sigils: The scalar $ is used to hold the hash reference returned by fetchrow_hashref.
• Context: fetchrow_hashref is called in scalar context, returning one row at a time or undef when done.
• String Interpolation: Perl will interpolate @ in double-quoted strings, so avoid using double quotes around SQL literals containing @. Prefer single quotes or escape characters.
• NULL Handling: Database NULLs become Perl undef; explicitly check with defined before use.
• TMTOWTDI: Perl allows various fetch methods (e.g., fetchrow_array, fetchrow_hashref)—choose based on your needs for named access versus performance.

This example uses only core modules DBI and DBD::SQLite (commonly shipped) and runs safely in sandboxed environments without filesystem or network access.