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.
