Handy Tricks with SQL::Abstract::More and Mojolicious


In writing REST style interfaces, we would like to minimize code and increase transparancy by passing sort, limit and offset parameters from the HTTP request into your SQL queries.

Note that SQL::Abstract::More throws an error if you specify -offset without -limit, so I always include my own defaults for those instead of relying on the database.

# In your model

use List::Util qw(pairmap);
use SQL::Abstract::More;

my $sqla = SQL::Abstract::More->new;

sub _query_params {
    my ($self, @params) = @_;

    # Select request query parameters, skipping any omitted.
    # Mogrify keys from e.g., 'offset' → '-offset' as per
    # SQL::Abstract::More.  Any non-word characters in the
    # parameter's value are excised to prevent injection
    # attacks. When building hash, provide default values (using
    # -offset style) before calling this.
    map { my $value = $self->param($_) =~ s/\W/ /gr;
          defined $value ? 
              pairmap { ('-'.$a) => $b } ( $_, $value) :
              ()  # empty list skips
        } @params;

# Example model method

sub get_something {
    my ($self) = @_;   # $self passed from your Controller
    my %qp = ( -offset => 10, -limit => 3,
               $self->_query_params(qw(offset limit order_by)) );
    my ($sql, @bind) = $sqla->select(-from => 'mytable', %qp );

Here is a complete example:

 #!/usr/bin/env perl
 use Mojolicious::Lite;

 #  Gazetteer browser
 #  Demonstrating
 #  1. Retrieval of tab-delimited file from U.S. Census Bureau into a SQLite database
 #  2. Using SQL::Abstract::More to build queries
 #  3. Safely passing Mojolicious query parameters to the database query
 #  4. Returning JSON

 use Mojo::UserAgent;

 # Fetch remote resource.
 my $ua = Mojo::UserAgent->new;
 # This is the list of place names in Vermont from the 2015 U.S. Census
 my $tx = $ua->get('http://www2.census.gov/geo/docs/maps-data/data/gazetteer/2015_Gazetteer/2015_gaz_place_50.txt');

 # For now, assume resource exists in the first chunk.
 my $data_text = $tx->res->content->asset->get_chunk(0);

 my @lines = split(/\s*\n/, $data_text);

 my @fields = map { lc } split(/\t/, shift @lines);

 # data as:
 #VT    5000400 02378297    Albany village  47  A   3992913 25599          1.542           0.010     44.729932   -72.381758

 use Mojo::SQLite;

 # Create a table, using field names from first line of file
 my $sql = Mojo::SQLite->new('sqlite:gaz_place.db');
 $sql->db->query('drop table if exists places');
 $sql->db->query('create table places (id integer primary key autoincrement, ' .  join(',', map { "$_ text" } @fields ). ')');

 # Populate table
 foreach my $place_line (@lines) {
     my %place;
     @place{@fields} = map { s/^\s+//; s/\s+$//; $_; } split (/\t/, $place_line);
     if (defined $place{name}) {  # Remove trailing USGS designation
         $place{name} =~ s/\s+(village|city|CDP)//;
     $sql->db->query('insert into places (' . join(',', keys %place ). ') '.
                     'values (' . join(',', (map {'?'} keys %place)) . ');',
                     values %place);

 use SQL::Abstract::More;
 my $sa = SQL::Abstract::More->new();

 use List::Util qw(pairmap);

 sub _query_params {
     my ($self, @params) = @_;

     # Select request query parameters, skipping any omitted.
     # Mogrify keys from e.g., 'offset' → '-offset' as per
     # SQL::Abstract::More.  Any non-word characters in the
     # parameter's value are excised to prevent injection
     # attacks. When building hash, provide default values (using
     # -offset style) before calling this.
     map { my $value = $self->param($_) =~ s/\W/ /gr;
           defined $value ? 
               pairmap { ('-'.$a) => $b } ( $_, $value) :
               ()  # empty list skips
         } @params;

 get '/places' => sub {
     my ($c) = @_;

     # Build a SQL query parameter list with defaults, with selected HTTP query parameters.
     # Invoke with $c->_query_params once you move that into a package
     my %qp = ( -offset => 0, -limit => 10, _query_params($c, qw(offset limit order_by)));

     $c->render(json => $sql->db->query($sa->select(-columns => '*', -from => 'places', %qp))->hashes);

 get '/' => sub {
   my $c = shift;
   $c->render(template => 'index');


 @@ index.html.ep
 % layout 'default';
 % title 'Gazetteer';
 <h1>Gazetteer Place Name Browser</h1>
 This demonstrates passing parametersthe
 <%= link_to 'list of place names' => '/places' %>.

 To see how parameters are passed through to the SQL,
   try appending query strings like this:

 <li><a href="/places">Example 1.</a> No parameter passed.</li>
 <li><a href="/places?offset=0&limit=20&order_by=aland_sqmi+desc">Example 2</a>
   has offset 0, limit 20, sorting by land area in square miles, largest first</l1>
 <li><a href="/places?offset=20&limit=10&order_by=aland_sqmi+desc">Example 3</a>
   has offset 20, limit 10, like a page following Example 2.</li>
 <li><a href="/places?offset=0&limit=10&order_by=name+desc">Example 4</a>
   has offset 0, limit 10, sorting by name, reverse alphabetically</l1>

 @@ layouts/default.html.ep
 <!DOCTYPE html>
   <head><title><%= title %></title></head>
   <body><%= content %></body>

Using Mojolicious templates with JSON


Although templates are primarily used with HTML when writing programs in Mojolicious, it is entirely possible and quite simple to use them to respond to any of the MIME types that Mojolicious::Types defines. You merely use the appropriate one-word type name along with .ep, like this Mojolicious::Lite example:

@@ index.json.ep
% use Mojo::JSON qw(to_json);
%== to_json { foo => 3 };

Here, we permit the /index route to respond with JSON by using Mojo's convenient module. We use the double-equal percent-print form so that quote characters and other HTML-ish bits are output as-is, not ampersand-encoded.

It is important to not have empty lines above or below the content, as the newlines would appear in the output.

Also note the use of to_json (which returns characters) rather than encode_json (which returns bytes) to avoid double-encoding UTF8 characters.

With that in mind, you should be able to produce any file format you desire.

Zero to Mojolicious: Building a Site with Zurb Foundation


Combining several of my previous posts, let's build a quick site with Mojolicious and Zurb Foundation. We will use the AssetPack plugin. Although we are serving fairly simple Javascript and CSS files now, later we can switch to using Coffeescript, Less or Sass or anything which AssetPack can pre-process.

First, install the AssetPack plugin and create a fresh "app" −

$ cpanm Mojolicious::Plugin::AssetPack
$ mojo generate app FoundationExample

Method 1 − Local copy of Foundation

Download the latest Foundation and put the files under a foundation/ directory under our "app" directory:

$ mkdir foundation
$ cd foundation
$  wget http://foundation.zurb.com/cdn/releases/foundation-5.5.2.zip
$ unzip foundation-5.5.2.zip

Now let's create a set of relative symbolic links for each file in the Foundation directory:

$ mkdir  public/foundation/
$ ln -s -r foundation/* public/foundation/

In lib/FoundationExample.pm, change the startup to −

# This method will run once at server start
sub startup {
  my $self = shift;


  my $source = "/foundation/";
  $self->asset("foundation.css" => $source."css/foundation.min.css");
  $self->asset("foundation.js" => $source."js/foundation.js");
  $self->asset("modernizr.js" => $source."js/vendor/modernizr.js");
  $self->asset("jquery.js" => $source."js/vendor/jquery.js");

  # Router
  my $r = $self->routes;

  # Normal route to controller

Continue with Both Methods, below.

Method 2 − CDN method

Use only the changes in lib/FoundationExample.pm shown above, but replace the source with −

  my $source = "https://cdnjs.cloudflare.com/ajax/libs/" .

Continue with Both Methods, below.

Both Methods.

In lib/FoundationExample/Controller/Example.pm − change the welcome routine to:

sub welcome {
  my $self = shift;

  $self->stash(copyright_holder => 'Someone');
  $self->stash(keywords => 'something, somebody, somewhere');
  $self->stash(author => 'William Lindley');

  # Render template "example/welcome.html.ep" with message
  $self->render(msg => 'Welcome to the Mojolicious real-time web framework!');

In templates/example/welcome.html.ep − change the layout to:

% layout 'zurbish';

and templates/layouts/zurbish.html.ep −

<!DOCTYPE html>
<html lang="en">
 <meta charset="utf-8">
 <!-- Zurb Foundation stuff -->
 <meta name="viewport" content="width=device-width, initial-scale=1.0">
%= asset 'foundation.css'
%= asset 'modernizr.js'
 <!-- end Zurb -->
 <title><%= title %></title>
 <meta name="copyright" content="Copyright ⓒ <%= ((localtime())[5]+1900) %> <%= $copyright_holder %>" />
 <meta name="keywords" content="<%= $keywords %>" />
 <meta name="author" content="<%= $author %>" />
%= asset 'jquery.js'
%= asset 'foundation.js'
<%= content %>

For more, see Jan Henning Thorsen's talk on Mojolicious::Plugin::AssetPack at MojoConf 2014.

What Happens After You Start Your Mojolicious 'app'


Please note, THIS IS A WORK IN PROGRESS, not a final article.

First let's look at the skeleton version of the main script that gets created when you execute "mojo generate app Foo" − this is foo/script/foo −

#!/usr/bin/env perl

use strict;
use warnings;

use lib 'lib';

# Start command line interface for application
require Mojolicious::Commands;

what Mojolicious::Commands does:

Then we do Mojolicious::Commands->start_app('Foo') which:

The source for start_app (in Mojolicious/Commands.pm) looks like this:

sub start_app {

This calls the new() method of Mojo::Server to create a Mojo server object (and subscribe to the "request" event with default request handling); the server object is returned and upon that we call build_app(). The argument to build_app will in this example be "Foo" which is the name of the application class for which we are building an instance.

→ Q: What does it mean, subscribe to the request event?

Then the call to start() runs as shown in Mojolicious.pm, namely by invoking the commands() and run() methods of Mojolicious, which have the effect of processing any commands and then running the event loop.

The only other code, aside from a stock Controller, is in lib/Foo.pm:

package Foo;
use Mojo::Base 'Mojolicious';

# This method will run once at server start
sub startup {
  my $self = shift;

  # Documentation browser under "/perldoc"

  # Router
  my $r = $self->routes;

  # Normal route to controller

What's going on here?

the argument is the 'app' (instance of _ class) that _ creates

From this we can call built-ins like plugin() and routes()



from http://mojolicio.us/perldoc/Mojolicious/Guides/FAQ#What-is-an-event-loop

An event loop is basically a loop that continually tests for external events and executes the appropriate callbacks to handle them, it is often the main loop in a program. Non-blocking tests for readability/writability of file descriptors and timers are commonly used events for highly scalable network servers, because they allow a single process to handle thousands of client connections concurrently.

  1. Set up all the routes, timers, and other reactions to be processed by the event engine.

  2. The script calls app->start (for Lite) or −−− (for Mojolicious)

  3. Event loop runs − Mojo::IOLoop (detail?)

from http://mojolicio.us/perldoc/Mojolicious/Guides/Cookbook#Timers

Timers, another primary feature of the event loop, are created with


"timer" in Mojo::IOLoop ](http://mojolicio.us/perldoc/Mojo/IOLoop#timer) and can for example be used to delay rendering of a response, and unlike sleep , won't block any other requests that might be processed concurrently… Recurring timers created with "recurring" in Mojo::IOLoop are slightly more powerful, but need to be stopped manually, or they would just keep getting emitted.

from http://mojolicio.us/perldoc/Mojolicious/Guides/Cookbook#Exceptions-in-events

Since timers and other non-blocking operations are running solely in the event loop, outside of the application, exceptions that get thrown in callbacks can't get caught and handled automatically. But you can handle them manually by subscribing to the event


"error" in Mojo::Reactor ](http://mojolicio.us/perldoc/Mojo/Reactor#error) or catching them inside the callback.

Why I'm Excited about Mojo::Pg


Marcus Ramberg wrote in the December 2014 Perl Advent Calendar about Mojo::Pg, the new DBI wrapper for the Mojolicious framework.

Particularly of interest are

On top of the upcoming "INSERT … ON CONFLICT …" grammar in Postgres 9.5, which lets you do things like:

INSERT INTO distributors (did, dname)
  VALUES (5, 'Gizmo transglobal'), (6, 'Associated Computing, inc')

it's well worth considering whether to keep using MySQL at all.