DBI Database Interface

A Perl DBI-like interface for database operations in Strada.

Overview

DBI provides a unified interface for working with databases in Strada. It supports multiple database backends:

Quick Start

use lib "lib";
use DBI;

# Connect to SQLite
my scalar $dbh = DBI::connect("dbi:SQLite:myapp.db", "", "");

# Create table
DBI::exec($dbh, "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
)", []);

# Insert with placeholders (safe from SQL injection)
DBI::exec($dbh, "INSERT INTO users (name, email) VALUES (?, ?)",
    ["Alice", "alice@example.com"]);

# Query all rows
my scalar $users = DBI::selectall_hashref($dbh, "SELECT * FROM users", []);
foreach my scalar $user (@{$users}) {
    say($user->{"name"} . " <" . $user->{"email"} . ">");
}

# Disconnect
DBI::disconnect($dbh);

DSN Formats

The Data Source Name (DSN) specifies which database to connect to:

Connection Functions

connect()

Connect to a database. Returns a database handle or undef on failure.

my scalar $dbh = DBI::connect($dsn, $username, $password);

# SQLite (no username/password needed)
my scalar $dbh = DBI::connect("dbi:SQLite:app.db", "", "");

# MySQL
my scalar $dbh = DBI::connect(
    "dbi:mysql:database=myapp;host=localhost",
    "root", "password");

connect_attrs()

Connect with additional attributes:

my scalar $dbh = DBI::connect_attrs($dsn, $user, $pass, {
    "AutoCommit" => 1,    # Auto-commit after each statement
    "PrintError" => 1,    # Print errors to stderr
    "RaiseError" => 1     # Throw exceptions on errors
});

disconnect()

Close the database connection:

DBI::disconnect($dbh);

ping()

Check if connection is still alive:

if (DBI::ping($dbh)) {
    say("Connection is alive");
}

Query Execution

exec()

Execute a SQL statement with parameters. Returns the number of affected rows.

# Insert
DBI::exec($dbh, "INSERT INTO users (name) VALUES (?)", ["Bob"]);

# Update
my int $rows = DBI::exec($dbh,
    "UPDATE users SET active = ? WHERE id = ?", [1, 42]);
say("Updated " . $rows . " rows");

# Delete
DBI::exec($dbh, "DELETE FROM sessions WHERE expired = ?", [1]);

do_sql()

Execute SQL without parameters (for DDL statements):

DBI::do_sql($dbh, "CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT)");
DBI::do_sql($dbh, "DROP TABLE IF EXISTS temp_data");

prepare() / execute()

For repeated queries, prepare once and execute multiple times:

my scalar $sth = DBI::prepare($dbh, "INSERT INTO logs (msg) VALUES (?)");

foreach my str $msg (@messages) {
    DBI::execute($sth, [$msg]);
}

DBI::finish($sth);

Fetching Results

selectall_hashref()

Execute query and return all rows as an array of hash references. This is the most common way to fetch results.

my scalar $users = DBI::selectall_hashref($dbh,
    "SELECT * FROM users WHERE active = ?", [1]);

foreach my scalar $user (@{$users}) {
    say($user->{"id"} . ": " . $user->{"name"});
}

selectrow_hashref()

Execute query and return first row as a hash reference:

my scalar $user = DBI::selectrow_hashref($dbh,
    "SELECT * FROM users WHERE id = ?", [42]);

if (defined($user)) {
    say("Found: " . $user->{"name"});
} else {
    say("User not found");
}

selectcol()

Execute query and return first column of first row (useful for COUNT, MAX, etc.):

my int $count = DBI::selectcol($dbh, "SELECT COUNT(*) FROM users", []);
say("Total users: " . $count);

my str $name = DBI::selectcol($dbh,
    "SELECT name FROM users WHERE id = ?", [1]);

selectall_arrayref()

Execute query and return all rows as an array of array references:

my scalar $rows = DBI::selectall_arrayref($dbh,
    "SELECT id, name FROM users");

foreach my scalar $row (@{$rows}) {
    say($row->[0] . ": " . $row->[1]);  # id: name
}

Row-by-Row Fetching

For large result sets, fetch one row at a time:

my scalar $sth = DBI::prepare($dbh, "SELECT * FROM large_table");
DBI::execute($sth, []);

while (my scalar $row = DBI::fetchrow_hashref($sth)) {
    say($row->{"name"});
}

DBI::finish($sth);

Insert and Get ID

insert_get_id()

Insert a row and return the auto-generated primary key:

my int $id = DBI::insert_get_id($dbh,
    "INSERT INTO users (name, email) VALUES (?, ?)",
    ["Charlie", "charlie@example.com"]);

say("Created user with ID: " . $id);

Transactions

Use transactions to group multiple operations atomically:

# Begin transaction
DBI::begin_work($dbh);

try {
    # Transfer money between accounts
    DBI::exec($dbh,
        "UPDATE accounts SET balance = balance - ? WHERE id = ?",
        [100, $from_id]);

    DBI::exec($dbh,
        "UPDATE accounts SET balance = balance + ? WHERE id = ?",
        [100, $to_id]);

    # Commit if successful
    DBI::commit($dbh);
    say("Transfer complete");

} catch ($e) {
    # Rollback on error
    DBI::rollback($dbh);
    say("Transfer failed: " . $e);
}

Error Handling

RaiseError Mode

Enable exceptions on database errors:

DBI::set_raise_error($dbh, 1);

try {
    DBI::exec($dbh, "INSERT INTO users (name) VALUES (?)", [$name]);
} catch ($e) {
    say("Database error: " . $e);
}

Manual Error Checking

my str $error = DBI::errstr($dbh);
if (length($error) > 0) {
    say("Error: " . $error);
}

my int $errcode = DBI::err($dbh);

Utility Functions

quote()

Quote a string value for safe use in SQL (when placeholders aren't available):

my str $safe = DBI::quote($dbh, "O'Brien");
# Returns: 'O''Brien'

column_names()

Get column names from a prepared statement:

my scalar $sth = DBI::prepare($dbh, "SELECT * FROM users");
DBI::execute($sth, []);
my scalar $cols = DBI::column_names($sth);
# $cols = ["id", "name", "email", ...]

rows()

Get number of rows affected by last statement:

my int $affected = DBI::rows($sth);

Complete Example

use lib "lib";
use DBI;

func main() int {
    # Connect to database
    my scalar $dbh = DBI::connect("dbi:SQLite:blog.db", "", "");
    if (!defined($dbh)) {
        say("Failed to connect");
        return 1;
    }

    # Create tables
    DBI::do_sql($dbh, "CREATE TABLE IF NOT EXISTS posts (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        body TEXT,
        created_at INTEGER
    )");

    # Insert a post
    my int $id = DBI::insert_get_id($dbh,
        "INSERT INTO posts (title, body, created_at) VALUES (?, ?, ?)",
        ["Hello World", "My first blog post!", core::time()]);
    say("Created post ID: " . $id);

    # Query all posts
    my scalar $posts = DBI::selectall_hashref($dbh,
        "SELECT * FROM posts ORDER BY created_at DESC", []);

    say("All posts:");
    foreach my scalar $post (@{$posts}) {
        say("  [" . $post->{"id"} . "] " . $post->{"title"});
    }

    # Count posts
    my int $count = DBI::selectcol($dbh, "SELECT COUNT(*) FROM posts", []);
    say("Total posts: " . $count);

    # Disconnect
    DBI::disconnect($dbh);
    return 0;
}

Function Reference