Databases with DBI and DBIx::Class

41.1 DBI Basics

Drivers (DBD): DBD::SQLite, DBD::Pg, DBD::mysql / DBD::MariaDB, DBD::Oracle, DBD::ODBC (SQL Server and more), DBD::CSV.

DBIx::Connector handles reconnection and fork safety. SQL::Abstract generates SQL from data structures. Mojo::Pg / Mojo::SQLite offer nice async APIs.

use v5.36;
use DBI;

my $dbh = DBI->connect(
    'dbi:Pg:dbname=shop;host=localhost',      # or dbi:SQLite:dbname=app.db, dbi:mysql:database=shop, dbi:MariaDB:...
    $ENV{DB_USER}, $ENV{DB_PASS},
    { RaiseError => 1, PrintError => 0, AutoCommit => 1, pg_enable_utf8 => 1 },
);

$dbh->do(q{
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMP DEFAULT now()
    )
});

# ALWAYS use placeholders (prevents SQL injection)
my $sth = $dbh->prepare('INSERT INTO users (name, email) VALUES (?, ?)');
$sth->execute('Ana', '[email protected]');
$sth->execute($_->{name}, $_->{email}) for @new_users;    # reuse a prepared statement

# Fetching
my $sel = $dbh->prepare('SELECT id, name, email FROM users WHERE name LIKE ? ORDER BY id');
$sel->execute('A%');
while (my $row = $sel->fetchrow_hashref) {
    say "$row->{id}: $row->{name} <$row->{email}>";
}
my $users = $dbh->selectall_arrayref('SELECT * FROM users', { Slice => {} });   # array of hashrefs
my ($count) = $dbh->selectrow_array('SELECT COUNT(*) FROM users');
my $by_id   = $dbh->selectall_hashref('SELECT * FROM users', 'id');
my @names   = $dbh->selectcol_arrayref('SE
…

41.2 DBIx::Class (ORM)

DBIx::Class::Schema::Loader generates Result classes from an existing database. Migrations: Sqitch (written in Perl, database-agnostic), App::DH, DBIx::Class::Migration.

NoSQL: MongoDB driver (now community-maintained), Redis / Redis::Fast, Search::Elasticsearch.

package MyApp::Schema::Result::User;
use base 'DBIx::Class::Core';
__PACKAGE__->table('users');
__PACKAGE__->add_columns(qw(id name email));
__PACKAGE__->set_primary_key('id');
__PACKAGE__->has_many(orders => 'MyApp::Schema::Result::Order', 'user_id');

package main;
my $schema = MyApp::Schema->connect('dbi:SQLite:app.db');
my $user = $schema->resultset('User')->create({ name => 'Ana', email => '[email protected]' });
my @devs = $schema->resultset('User')->search(
    { name => { -like => 'A%' } },
    { order_by => { -desc => 'id' }, rows => 10, prefetch => 'orders' },
)->all;
say $_->name, ' has ', $_->orders->count, ' orders' for @devs;