PostgreSQL - Perl

PostgreSQL - Modern Perl Interface

Introduction & Context

Perl can connect to PostgreSQL using the standard DBI (Database Independent Interface) module and a specific driver, DBD::Pg. While Perl is a robust and capable language, it’s worth noting that for new web application backends, ecosystems like Python (psycopg), Node.js (node-postgres), Go (pgx), and Java (JDBC) are currently more prevalent in the industry.

This guide demonstrates the modern and secure way to interact with PostgreSQL from Perl.

Installation with cpanm

The recommended way to install Perl modules is with a CPAN client like cpanm. Manual download and installation are outdated and not recommended.

First, install cpanm if you don’t have it. Then, install the required database modules:

# Install cpanminus (if you don't have it)
# curl -L https://cpanmin.us | perl - --sudo App::cpanminus

# Install the DBI and PostgreSQL driver modules
sudo cpanm DBI DBD::Pg

Secure Database Interaction: Placeholders

CRITICAL SECURITY WARNING: Never embed variables directly into an SQL string. This creates a vulnerability called SQL Injection, which can lead to data theft or destruction. Always use placeholders (?) with the prepare and execute methods.

Connecting to the Database

The following script shows a modern way to connect. We enable RaiseError and PrintError to ensure that any database error will automatically stop the script and report the problem.

#!/usr/bin/env perl
use strict;
use warnings;
use v5.10;

use DBI;

my $dsn = "DBI:Pg:dbname=mydb;host=localhost;port=5432";
my $user = "myuser";
my $password = "mypassword";

my $dbh = DBI->connect($dsn, $user, $password, {
    RaiseError => 1,
    PrintError => 0, # RaiseError is enough
    AutoCommit => 1, # Recommended for most cases
}) or die "Cannot connect to database: $DBI::errstr";

say "Database connection successful.";

$dbh->disconnect();

CRUD Operations Example

Let’s demonstrate creating a table, then performing Create, Read, Update, and Delete operations using secure, modern practices.

1. Create Table

my $create_sql = q{
    CREATE TABLE IF NOT EXISTS employees (
        id      SERIAL PRIMARY KEY,
        name    TEXT NOT NULL,
        role    TEXT NOT NULL,
        salary  INTEGER
    );
};
$dbh->do($create_sql); # 'do' is safe for statements without parameters.
say "Table 'employees' is ready.";

2. INSERT Operation (Create)

Notice the use of ? placeholders and passing values to execute.

my $insert_sql = "INSERT INTO employees (name, role, salary) VALUES (?, ?, ?)";
my $sth = $dbh->prepare($insert_sql);

$sth->execute('Alice', 'Engineer', 90000);
$sth->execute('Bob', 'Marketing', 65000);

say "Two records inserted successfully.";

3. SELECT Operation (Read)

Using selectall_arrayref with Slice gives us an array of hashrefs, which is easy to work with.

my $select_sql = "SELECT id, name, role, salary FROM employees WHERE salary > ?";
my $employees = $dbh->selectall_arrayref($select_sql, { Slice => {} }, 70000);

say "Employees earning more than 70000:";
foreach my $emp (@$employees) {
    say " - ID: $emp->{id}, Name: $emp->{name}, Role: $emp->{role}";
}

4. UPDATE Operation

my $update_sql = "UPDATE employees SET salary = ? WHERE name = ?";
my $rows_affected = $dbh->do($update_sql, undef, 95000, 'Alice');

say "Updated $rows_affected row(s). Alice got a raise!";

5. DELETE Operation

my $delete_sql = "DELETE FROM employees WHERE name = ?";
$rows_affected = $dbh->do($delete_sql, undef, 'Bob');

say "Deleted $rows_affected row(s). Goodbye, Bob.";

Remember to always disconnect at the end of your script: $dbh->disconnect();