A WebSocket Mojolicious/DBI Example

Building on my (second) example from my recent post A Simple Mojolicious/DBI Example, I thought I would take it just a little bit futher. One of my favorite features of Mojolicious is that it comes with WebSockets out of the box! In this example I show how you can take the example script and allow it to run without refreshing the window.

WebSockets, part of the new HTML5 standard, provide a two way means of communication between the client and server. While there have been hackish means of accomplishing this task for years (AJAX), WebSockets make these connections very simple. Of course the style changes from a procedural (“render this content”, “POST new data”, etc) to event driven (“on click”, “on message”, etc)

In this version of the example, notice that the /insert route has been declared as a websocket. This new route defines an on message handler for what to do when the server receives a websocket message. First it decodes the JSON data, inserts it into the database, then it generates a new HTML row for the client and sends it back to the client via that same websocket connection.

Correspondingly in the HTML template I now have a javascript function called insert which opens a websocket and defines an onopen handler for sending the data, and an onmessage handler which receives the new HTML row and inserts it into the displayed table. This function makes use of jQuery which I pull in from the Google CDN.

One more thing to notice is that I now have a separate template for the table rows. The index page uses this to build the initial table, but since it is separated I can use it to generate the new inserted row HTML too!

Finally one might notice the use of Mojolicious’ tag helpers in places. I especially like using the javascript tag both to link to a source and to write inline javascript. There are many such tag helpers available and even some other helpers which may also be useful in your scripts/templates (some of which I have used without introduction).

#!/usr/bin/env perl

use Mojolicious::Lite;
use Mojo::JSON;
my $json = Mojo::JSON->new;

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

# add helper methods for interacting with database
helper db => sub { $dbh };

helper create_table => sub {
  my $self = shift;
  warn "Creating table 'people'\n";
  $self->db->do('CREATE TABLE people (name varchar(255), age int);');
};

helper select => sub {
  my $self = shift;
  my $sth = eval { $self->db->prepare('SELECT * FROM people') } || return undef;
  $sth->execute;
  return $sth->fetchall_arrayref;
};

helper insert => sub {
  my $self = shift;
  my ($name, $age) = @_;
  my $sth = eval { $self->db->prepare('INSERT INTO people VALUES (?,?)') } || return undef;
  $sth->execute($name, $age);
  return 1;
};

# if statement didn't prepare, assume its because the table doesn't exist
app->select || app->create_table;

# setup base route
any '/' => sub {
  my $self = shift;
  my $rows = $self->select;
  $self->render('index', rows => $rows );
};

# setup websocket message handler
websocket '/insert' => sub {
  my $self = shift;
  $self->on( message => sub {
    my ($ws, $message) = @_;

    my $row = $json->decode($message);
    $self->insert(@$row);

    my $html = $ws->render( 'table', rows => [$row], partial => 1 );
    $ws->send($json->encode({row => $html}));
  });
};

app->start;

__DATA__

@@ index.html.ep

<!DOCTYPE html>
<html>
<head>
  <title>People</title>
  <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
</head>
<body>
  <div>
    Name: <input type="text" id="name"> 
    Age: <input type="text" id="age" > 
    <input type="submit" value="Add" onclick="insert()">
  </div>
  <br>
  Data: <br>
  <table border="1">
    <thead>
      <tr>
        <th>Name</th>
        <th>Age</th>
      </tr>
    </thead>
    <tbody id="table">
      %= include 'table'
    </tbody>
  </table>
  %= javascript begin
    function insert () {
      if (!("WebSocket" in window)) {
        alert('Your browser does not support WebSockets!');
        return;
      }
      var ws = new WebSocket("<%= url_for('insert')->to_abs %>");
      ws.onopen = function () {
        var name = $('#name');
        var age = $('#age');
        ws.send(JSON.stringify([name.val(),age.val()]));
        name.val('');
        age.val('');
      };
      ws.onmessage = function (evt) {
        var data = JSON.parse(evt.data);
        $('#table').append(data.row);
      };
    }
  %= end
</body>
</html>

@@ table.html.ep

% foreach my $row (@$rows) {
  <tr>
    % foreach my $text (@$row) {
      <td><%= $text %></td>
    % }
  </tr>
% }

Leave a comment

About Joel Berger

user-pic As I delve into the deeper Perl magic I like to share what I can.