Exploring DBIish

Previously I wrote that I need to plumb my web app to a DB and was told that DBIish was the tool for the job. I was glad to see it has a migrations module to go along with it. I'll go ahead and add my first migration which will create the table for a pastebin. These simply consist of SQL statements in a file I creatively named 'migrations':

-- 1 up
CREATE TABLE IF NOT EXISTS pastes (
         paste_id SERIAL PRIMARY KEY,
       paste_body TEXT
       );

-- 1 down
DROP TABLE pastes;

Now I'll need to connect to my DB. I honestly didn't have time to figure out how I'll pass this object to my model module(s) so for now I'm using a dynamic variable and rather than reading the credentials from a file I'll just use prompt() for now:

my $*dbh = DBIish.connect:
    'Pg',
    :host<devbussy.swagg.net>,
    :database<pastes_bin>,
    :user<pastes_bin>,
    password => prompt 'enter DB password: ';

my $m = DB::Migration::Simple.new:
    :$*dbh,
    :migration-file<migrations>;

$m.migrate: :version<1>;

Now if we run the web app we get prompted for the DB password and then we can see the migration be applied:

$ ./bin/pastes-bin
enter DB password: hogrider69
line: -- 1 up
version: 1, direction: up
line: CREATE TABLE IF NOT EXISTS pastes (
line:          paste_id SERIAL PRIMARY KEY,
line:        paste_body TEXT
line:        );
line: -- 1 down
version: 1, direction: down
line: DROP TABLE pastes;
initializing db-migrations-simple-meta
set initial version to 0
{1 => {down => DROP TABLE pastes;
, up => CREATE TABLE IF NOT EXISTS pastes (
         paste_id SERIAL PRIMARY KEY,
       paste_body TEXT
       );
}}
migrating from version '0' to version '1'
True migrating 'up' from version '0' to version '1'
doing 'up' migrations for 1
executing CREATE TABLE IF NOT EXISTS pastes (
         paste_id SERIAL PRIMARY KEY,
       paste_body TEXT
       )
Humming-Bird listening on port http://localhost:3000

We can check the DB now and see our new table applied along with the metadata for the migrations module itself:

$ psql -h devbussy.swagg.net -U pastes_bin
Password for user pastes_bin: 
psql (15.3 (Debian 15.3-0+deb12u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

pastes_bin=> \d
                     List of relations
 Schema |           Name            |   Type   |   Owner    
--------+---------------------------+----------+------------
 public | db-migrations-simple-meta | table    | pastes_bin
 public | pastes                    | table    | pastes_bin
 public | pastes_paste_id_seq       | sequence | pastes_bin
(3 rows)

pastes_bin=> \d pastes;
                                 Table "public.pastes"
   Column   |  Type   | Collation | Nullable |                 Default                  
------------+---------+-----------+----------+------------------------------------------
 paste_id   | integer |           | not null | nextval('pastes_paste_id_seq'::regclass)
 paste_body | text    |           |          | 
Indexes:
    "pastes_pkey" PRIMARY KEY, btree (paste_id)

Now I wanted to have separate model and controller modules but I'm still working on how to pass $dbh around sanely so for now I'm going to just add this to test:

use Pastes-Bin::Model::Paste;

# No routes yet just prompt to 'fake it'
my $new-paste = prompt 'enter a new paste: ';
Pastes-Bin::Model::Paste.create: $new-paste;

Here's the model code:

unit class Pastes-Bin::Model::Paste;

submethod create(Str $new-paste) {
    $*dbh.execute(q:to/END_SQL/, $new-paste)
        INSERT INTO pastes (paste_body)
        VALUES (?);
       END_SQL
}

And now we run it again:

$ ./bin/pastes-bin 
enter DB password: hogrider69
line: -- 1 up
version: 1, direction: up
line: CREATE TABLE IF NOT EXISTS pastes (
line:          paste_id SERIAL PRIMARY KEY,
line:        paste_body TEXT
line:        );
line: -- 1 down
version: 1, direction: down
line: DROP TABLE pastes;
current-version: allrows: [[1]]
{1 => {down => DROP TABLE pastes;
, up => CREATE TABLE IF NOT EXISTS pastes (
         paste_id SERIAL PRIMARY KEY,
       paste_body TEXT
       );
}}
migrating from version '1' to version '1'
DB already at version 1
enter a new paste: testing 123...
Humming-Bird listening on port http://localhost:3000
^C

And now in the DB:

pastes_bin=> SELECT * FROM pastes;
 paste_id | paste_body 
----------+------------
(0 rows)

pastes_bin=> SELECT * FROM pastes;
 paste_id |   paste_body   
----------+----------------
        1 | testing 123...
(1 row)

It's nice to see I can talk to a DB with ease with DBIish and it handles migrations. I need to next dig into how I can organize my code in Raku where $dbh can be 'passed around'. I tried creating a separate controller module that calls the model class but in that configuration it did not see the $*dbh dynamic variable... If/when I figure that out then that'll be the next blog entry on my journey into Raku.

#webDev #noScript #RakuLang