Perl DBI Guide — Database Access with DBI and SQL Integration

In this tutorial, you will learn about Perl DBI Guide. We cover key concepts, practical examples, and best practices to help you master this topic.

Perl DBI is the standard database interface providing consistent methods for connecting, querying, and Transaction management across databases -- using DBD::mysql, DBD::Pg, DBD::SQLite, and other drivers for backend-specific access.

Connecting to Database

use DBI;

# SQLite
my $dbh = DBI->connect("dbi:SQLite:dbname=mydb.sqlite")
    or die "Connect failed: $DBI::errstr";

# MySQL
my $dbh = DBI->connect(
    "dbi:mysql:database=mydb;host=localhost",
    "user", "password"
) or die "Connect failed: $DBI::errstr";

Query Execution

# Simple query
my $users = $dbh->selectall_arrayref("SELECT * FROM users");

# Fetch as hashref
my $users = $dbh->selectall_hashref("SELECT * FROM users", "id");

# Single value
my $count = $dbh->selectrow_array("SELECT COUNT(*) FROM users");

Prepared Statements

# Prepare and execute
my $sth = $dbh->prepare("SELECT name, age FROM users WHERE age > ?");
$sth->execute(18);

# Fetch results
while (my $row = $sth->fetchrow_hashref) {
    print "$row->{name} is $row->{age}\n";
}

# Fetch all at once
my @results = @{$sth->fetchall_arrayref({})};

Insert with Placeholders

my $sth = $dbh->prepare(
    "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
);
$sth->execute("Alice", "alice@example.com", 30);
$sth->execute("Bob", "bob@test.com", 25);

print "Inserted rows: " . $sth->rows;

Transactions

eval {
    $dbh->begin_work;
    
    $dbh->do("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $dbh->do("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    
    $dbh->commit;
};
if ($@) {
    $dbh->rollback;
    print "Transaction failed: $@\n";
}

Common Mistakes

1. SQL Injection with interpolated values

Always use placeholders: $dbh->do("SELECT * FROM users WHERE id = ?", undef, $id).

2. Not checking return values

DBI methods return undef on failure. Check $DBI::errstr or use RaiseError => 1 in connect.

3. Forgetting to disconnect

$dbh->disconnect releases database resources. Use in END block for safety.

Practice Questions

1. How do you prevent SQL injection in DBI? Use placeholders with ? marks: $dbh->prepare("SELECT * FROM t WHERE id = ?")->execute($id).

2. How do you fetch a single row? my $row = $dbh->selectrow_hashref("SELECT * FROM t WHERE id = ?", undef, $id).

3. How do you handle transactions? begin_work, do operations, commit on success, rollback on failure (in eval).

FAQ

{{< faq question="What DBD drivers are available?" >}} DBD::mysql, DBD::Pg (PostgreSQL), DBD::SQLite, DBD::Oracle, DBD::ODBC, DBD::Sybase, and many more. {{< /faq >}}

{{< faq question="What is the difference between do and prepare/execute?" >}} do runs a non-SELECT statement immediately. prepare/execute is needed for SELECT statements and parameterized queries. {{< /faq >}}

{{< faq question="How do I get the last inserted ID?" >}} $dbh->last_insert_id(undef, undef, undef, undef) returns the auto-generated ID from the last INSERT. {{< /faq >}}

What's Next

Now learn about command-line Perl.