A Simple Plack/DBI Example

In A Simple Mojolicious/DBI Example Joel Berger demonstrates how to build a very simple CRUD web app (well, a CR one anyway) using Mojolicious and DBI. I was impressed at how concise it was, and wondered how my preferred technology stack would compare.

I'm not a fan of template languages, preferring DOM manipulation. And rather than Mojo, I'm using Plack here. Anyway, this is what I came up with...

#!/usr/bin/env plackup

use v5.14;
use DBI;
use HTML::HTML5::Parser;
use HTML::HTML5::Writer;
use Plack::Request;
use Plack::Response;

# connect to database
my $dbh = 'DBI'->connect("dbi:SQLite:database.db","","") or die "Could not connect";
my ($insert, $select);

while (1) {
   # create insert and select statements
   $insert = eval { $dbh->prepare('INSERT INTO people VALUES (?,?)') };
   $select = eval { $dbh->prepare('SELECT * FROM people') };
   # break out of loop if statements prepared
   last if $insert && $select;

   # if statements didn't prepare, assume its because the table doesn't exist
   warn "Creating table 'people'\n";
   $dbh->do('CREATE TABLE people (name varchar(255), age int);');

my $template = 'HTML::HTML5::Parser'->load_html(IO => \*DATA);
my $writer   = 'HTML::HTML5::Writer'->new(markup => 'html', polyglot => 1);

# the PSGI app itself
my $app = sub {
   my $req = 'Plack::Request'->new(shift);
   my $res = 'Plack::Response'->new(200);

   if ($req->method eq 'POST') {
      $insert->execute(map $req->parameters->{$_}, qw( name age ));
      $res->redirect( $req->base );
   else {
      my $page  = $template->cloneNode(1);
      my $table = $page->getElementsByTagName('table')->get_node(1);
      while (my @row = $select->fetchrow_array) {
         my $tr = $table->addNewChild($table->namespaceURI, 'tr');
         $tr->appendTextChild(td => $_) for @row;
      $res->body( $writer->document($page) );


<!DOCTYPE html>
<form action="insert" method="post">
   Name: <input type="text" name="name"> 
   Age: <input type="text" name="age">
   <input type="submit" value="Add">
Data: <br>
<table border="1">


Why are you quoting your class names? Are you afraid they might also be function names?

An excellent response indeed. Thanks I'm glad to see how Plack would handle this.

In fact, I had planned a follow-up that would show how to do this without refreshing, via websockets. However, I was left thinking about how I would append to the table. Instead I think I will mimic your example and append from the beginning.


For disambiguation I generally do:


Hmmmm, that's something to think about. I've never tripped over that one. I have had the indirect bite me a few times, so that is when I am careful do disambiguate. The times when I have a function which returns a valid class name usually are when I want to have some handler class, so I might have has ua_class => 'LWP::UserAgent', then invoking my $ua = $self->ua_class->new. Yet another thing to worry about!

I extended your comment into a small test. I used JSON instead of DateTime b/c it wanted a year.

which reminds me, in a recent conversation with a $cow-orker, I realized it could be interesting to have a module that allows python devs to be able to use the object construction syntax they're used to, something like Acme::Constructor::Pythonic, which would import package names as functions that simply call new(@_) on the corresponding class...

Or it would be totally idiotic :)

tobyink++! Nice implementation! I wouldn't have thought to use 'no thanks'. That's clever!

tobyink++ really nice ;)

So if table cannot be created (lack of free space) - it will loop forever?
Why eval{} if you don't use RaiseError ?

Leave a comment

About Toby Inkster

user-pic I'm tobyink on CPAN, IRC and PerlMonks.