Databases

45.1 DBIish

Native drivers: DB::Pg, DB::SQLite, DB::MySQL (by CurtTilmes) offer connection pooling and convenient APIs. Redis, MongoDB clients exist in the ecosystem.

use DBIish;                                       # zef install DBIish
my $dbh = DBIish.connect('SQLite', database => 'app.db');
# PostgreSQL: DBIish.connect('Pg', :host<localhost>, :database<shop>, :user<me>, :password(%*ENV<DB_PASS>));

$dbh.execute(q:to/SQL/);
    CREATE TABLE IF NOT EXISTS users (
        id    INTEGER PRIMARY KEY AUTOINCREMENT,
        name  TEXT NOT NULL,
        email TEXT UNIQUE
    )
    SQL

my $insert = $dbh.prepare('INSERT INTO users (name, email) VALUES (?, ?)');   # placeholders prevent SQL injection
$insert.execute('Ana', '[email protected]');
$insert.execute('Bo', '[email protected]');

my $sth = $dbh.execute('SELECT id, name, email FROM users WHERE name LIKE ?', 'A%');
for $sth.allrows(:array-of-hash) -> %row {
    say "%row<id>: %row<name> <%row<email>>";
}
my ($count) = $dbh.execute('SELECT COUNT(*) FROM users').row;
say "Total: $count";

# Transactions
$dbh.execute('BEGIN');
try {
    $dbh.execute('UPDATE users SET name = ? WHERE id = ?', 'Ana B.', 1);
    $dbh.execute('COMMIT');
    CATCH { default { $dbh.execute('ROLLBACK'); .rethrow } }
}
$dbh.dispose;

45.2 Red ORM

use Red;                                          # zef install Red
model Person {
    has Int $.id    is serial;
    has Str $.name  is column;
    has Str $.email is column{ :unique };
    has @.posts     is relationship(*.author-id, :model<Post>);
}
model Post {
    has Int $.id        is serial;
    has Str $.title     is column;
    has Int $.author-id is referencing(*.id, :model<Person>);
}
red-defaults 'SQLite', database => 'red.db';
Person.^create-table: :if-not-exists;
Post.^create-table: :if-not-exists;
my $ana = Person.^create(name => 'Ana', email => '[email protected]');
$ana.posts.create(title => 'Learning Raku');
.say for Person.^all.grep(*.name.starts-with('A'))».name;   # translated to SQL WHERE