Databases with DBI
| Connecting with DBI | |
| Queries and placeholders | |
| Inserts and updates | |
| Transactions |
Connecting with DBI
DBI is one interface for many databases. The database-specific driver is a separate module: DBD::SQLite for a file, DBD::Pg for PostgreSQL, DBD::mysql for MySQL.
cpanm DBI DBD::SQLite
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect(
"dbi:SQLite:dbname=todo.db", "", "",
{
RaiseError => 1,
PrintError => 0,
AutoCommit => 1,
},
) or die "cannot connect: ", DBI->errstr;
The connection string has three parts: the driver, the database name, and driver options. For PostgreSQL it looks like dbi:Pg:dbname=shop;host=localhost, and the second and third arguments are the user and password.
RaiseError => 1 turns every failed statement into a die, so you cannot forget to check a return value. PrintError => 0 stops DBI from printing the same error twice.
Queries and placeholders
Prepare a statement once, execute it with values, then read rows. The question marks are placeholders: DBI sends them separately, so a value can never break out of the SQL.
my $sth = $dbh->prepare("SELECT id, title FROM todo WHERE done = ?");
$sth->execute(0);
while (my $row = $sth->fetchrow_arrayref) {
print "$row->[0] $row->[1]\n";
}
1 Ship the course 3 Review lesson 45
Never build a query by pasting a variable into the string. The classic injection "x' OR '1'='1" turns a filter into a full table read, and placeholders make it impossible:
my $name = $ARGV[0];
$dbh->prepare("SELECT * FROM users WHERE name = ?"); # safe
$dbh->prepare("SELECT * FROM users WHERE name = '$name'"); # unsafe
Placeholders only stand for values, not for table or column names. If those come from outside, check them against a fixed list before putting them in the SQL.
For one-shot queries, the convenience methods save the boilerplate. selectrow_array returns the first row, and selectall_arrayref returns a reference to all rows:
my $count = $dbh->selectrow_array("SELECT COUNT(*) FROM todo");
my $rows = $dbh->selectall_arrayref("SELECT title FROM todo");
Inserts and updates
do runs a statement that returns no rows. For an insert with values, prepare once outside the loop and execute inside it — that is what a prepared statement is for.
$dbh->do(q{
CREATE TABLE IF NOT EXISTS todo (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
done INTEGER NOT NULL DEFAULT 0
)
});
my $ins = $dbh->prepare("INSERT INTO todo (title) VALUES (?)");
$ins->execute("Ship the course");
my $id = $dbh->last_insert_id(undef, undef, "todo", "id");
print "inserted row $id\n";
inserted row 1
last_insert_id needs the table and column because different databases generate keys differently. $ins->rows tells you how many rows the statement changed, which is how you detect an update that matched nothing.
Transactions
When several statements must succeed or fail together, turn off autocommit and manage the transaction yourself. begin_work starts one and temporarily disables autocommit:
$dbh->begin_work;
my $ok = eval {
$dbh->do("UPDATE account SET balance = balance - 100 WHERE id = 1");
$dbh->do("UPDATE account SET balance = balance + 100 WHERE id = 2");
$dbh->commit;
1;
};
$dbh->rollback unless $ok;
die "transfer failed: $@" unless $ok;
With RaiseError on, a failed statement dies, the eval catches it, and the rollback undoes the first update. Money either moves completely or not at all.
Call $dbh->disconnect when the program is done, or let the process exit do it. Long-running servers should keep one connection per process and avoid sharing a handle between threads; use a connection pool instead.
Next: Capstone: todo CLI
Article author: Arthur Isaev