Languages: Python C C++ Go Java Ruby Rust Perl R ← Full TOC

🗄️ DBI — Database interface — MySQL, PostgreSQL, SQLite, Oracle

Perl's DBI is the universal database interface. Combine with DBD::mysql, DBD::Pg, DBD::SQLite, or DBD::Oracle driver modules. Always use placeholders to prevent SQL injection.

📋 Functions & Syntax

Name	Signature	Description
DBI->connect	DBI->connect($dsn, $user, $pass, \%attr) → $dbh	Connect to database
$dbh->prepare	prepare($sql) → $sth	Prepare SQL statement
$sth->execute	execute(@bind_values) → bool	Execute prepared statement
$sth->fetchrow_arrayref	fetchrow_arrayref() → \@row	Fetch next row as array ref
$sth->fetchrow_hashref	fetchrow_hashref() → \%row	Fetch next row as hash ref
$sth->fetchall_arrayref	fetchall_arrayref() → \@rows	Fetch all rows
$sth->fetchall_hashref	fetchall_hashref($key) → \%h	Fetch all rows keyed by column
$dbh->do	do($sql, undef, @binds) → $rows	Execute single SQL statement
$dbh->selectall_arrayref	selectall_arrayref($sql, \%attr, @binds)	Select and fetch all
$dbh->selectrow_hashref	selectrow_hashref($sql, \%attr, @binds)	Select single row as hashref
$dbh->last_insert_id	last_insert_id(undef,undef,undef,undef)	Get last auto-increment ID
$dbh->begin_work	begin_work() → bool	Start transaction
$dbh->commit	commit() → bool	Commit transaction
$dbh->rollback	rollback() → bool	Roll back transaction
$dbh->disconnect	disconnect()	Close database connection
RaiseError	{ RaiseError => 1 }	Auto-die on DB errors (recommended)
AutoCommit	{ AutoCommit => 1/0 }	Auto-commit mode
Slice	{ Slice => {} }	Return rows as hashrefs in selectall_arrayref
dbi:SQLite	dbi:SQLite:dbname=file.db	SQLite DSN — no server needed
dbi:mysql	dbi:mysql:database=mydb;host=localhost	MySQL/MariaDB DSN
dbi:Pg	dbi:Pg:dbname=mydb;host=localhost	PostgreSQL DSN

💡 Example

Run with perl script.pl.

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

my $dbh = DBI->connect("dbi:SQLite:dbname=university.db", "", "",
                        { RaiseError => 1, AutoCommit => 1 })
    or die "Connect failed: $DBI::errstr";

$dbh->do(q{CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL, subject TEXT NOT NULL, score INTEGER NOT NULL)});

my $insert = $dbh->prepare(
    "INSERT INTO students (name, subject, score) VALUES (?, ?, ?)");
$insert->execute(@$_) for
    (["Alice","Math",95],["Bob","Science",87],["Carol","Math",92],
     ["Dave","Science",78],["Eve","Math",98]);

print "=== All students ===\n";
my $rows = $dbh->selectall_arrayref(
    "SELECT name, subject, score FROM students ORDER BY score DESC",
    { Slice => {} });
printf "  %-10s %-10s %d\n", $_->{name}, $_->{subject}, $_->{score} for @$rows;

print "=== Average by subject ===\n";
my $sth = $dbh->prepare(
    "SELECT subject, AVG(score) avg, COUNT(*) cnt FROM students GROUP BY subject");
$sth->execute();
while (my $row = $sth->fetchrow_hashref) {
    printf "  %-10s avg=%.2f cnt=%d\n", $row->{subject}, $row->{avg}, $row->{cnt};
}

eval {
    $dbh->begin_work;
    $dbh->do("UPDATE students SET score = score + 2 WHERE subject = 'Science'");
    $dbh->commit;
    print "Transaction committed.\n";
};
if ($@) { $dbh->rollback; die "Rolled back: $@"; }

$dbh->disconnect;
unlink "university.db";
print "Done.\n";
# perl dbi_demo.pl  (requires: cpan install DBI DBD::SQLite)

← File IO | 🏠 Index