How to integrate DBIx with Mojolicious?

In this article, we learn how to set up the DBIX with the Mojolicious web framework. By default, when we create the project in the Mojolicious, unlike Perl Catalyst, it doesn't come with the database Integration. We have to write our own database handler for Mojolicious framework.

This article doesn't cover the installation, if you want to know how to install, visit here.

PreRequisites

Before proceeding, there are some prerequisites:

1. Perl elementary knowledge required.
2. Understanding of Mojolicious
3. Familiarity with the DBIx.
4. dbicdump must be installed on your system.
5. Mojolicious, Perl and DBIx installed on your system.
6. MySQL must be installed on your system.
7. Familiarity with MySQL basic commands.

You are reading further. Good!

Setup Mojolicious

Let's create the project using the following command:

mojo generate app MyApp

Once the command completes, it generates some folders, configuration file to start the app.

Go to my_app.

cd my_app
ls                                       # To view directories
morbo script/my_app    # To start the default web server

Folder structure

Visit http://localhost:3000

if you are seeing this page, then you are good to go with

Setting up the database

Now create the database myApp_database;

mysql -u username -p

And create the database by writing the following command.

create database myApp_database;

The database is created now it's time to create few tables and insert data into it.

CREATE TABLE `books` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) DEFAULT NULL,
  `author_name` varchar(80) DEFAULT NULL,
  `price` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Lets add few rows into the table

INSERT INTO books (name, author_name, price) 
VALUES
('book1', 'Author1', 750),
('book2', 'Author2', 1750),
('book3', 'Author3', 6500)

Integrate with Mojolicious

So far, we created database, table and add some data to it. In this section, we learn how to integrate with the database.

Create another file 'dbdump.conf' under the my_app directory.

schema_class MyAPP::Schema

# connection string
<connect_info>
    dsn     dbi:mysql:myApp_database
    user    your_db_user
    pass    your_db_password
</connect_info>

# dbic loader options
<loader_options>
    dump_directory ./lib/
    components     InflateColumn::DateTime
    components     TimeStamp
    components     EncodedColumn
</loader_options>

In the next step, we use dbicdump to built the schema under the lib folder.

dbicdump dbdump.conf

if run successfully, following message will appear on command line.

Dumping manual schema for MyApp::Schema to directory ./lib/ ...
Schema dump completed.

We have dumped the database successfully. Make sure the Schema.pm created under the lib/MyApp folder and, Book.pm created under lib/MyApp/Schema/Book.pm.

Next is to Create the file DB.pm under my_app/lib/MyApp/DB.pm. See the picture below:

In DB.pm write the following content

package MyApp::Model::DB;

use MyApp::Schema;
use DBIx::Class ();

use strict;

my ($schema_class, $connect_info);

BEGIN {
    $schema_class = 'MyApp::Schema';
    $connect_info = {
        dsn      => 'dbi:mysql:myApp_database',
        user     => 'db_username',
        password => 'db_password',
    };
}

sub new {
    return __PACKAGE__->config( $schema_class, $connect_info );
}

sub config {
    my $class = shift;

    my $self = {
        schema       => shift,
        connect_info => shift,
    };

    my $dbh = $self->{schema}->connect(
        $self->{connect_info}->{dsn}, 
        $self->{connect_info}->{user}, 
        $self->{connect_info}->{password}
    );

    return $dbh;
}

1;

We have created a module, where we setup the database. But to use it, we need to call it from our App file i.e. MyApp.pm

Open the file and add the following :

use MyApp::Model::DB ;

And then under the statup subroutine

$self->_set_db_operation_handler();

And now create the function _set_db_operation_handler()

# Database operations handler object
sub _set_db_operation_handler {
    my $self = shift;

    $self->{ _dbh } = MyApp::Model::DB->new();

    return $self;
}

So far, so good. Next step is not query the table and show the results on browsers.

Add routes

Ok, so we need to list down the book details in web app. It is always better to create a new route for it.

In the MyApp.pm file, add one more route in the startup subroutine.

$r->get('/books')->to('books#list_books');

*What does it implies? *

It says,

• we create a new route '/books'
• Request type is get
• It goes to books controller.
• And under the controller, we have an action (subroutine) with a name list_books.
• It also says, whatever be result or output of the root, there should a template list_book.html.ep, under template/list_books.html.ep

Lets implement all of the above. Go to the Controller directory and create Books.pm and add the following.

package MyApp::Controller::Books;
use Mojo::Base 'Mojolicious::Controller';

# This action will render a template
sub list_books {
    my $self = shift;

    my @books = $self->app->{_dbh}->resultset('Book')->search({});

    @books = map { { 
        id => $_->id,
        name => $_->name,
        author_name => $_->author_name,
        price => $_->price
    } } @books;

    $self->render(msg => 'List of books!', books => \@books);
}

1;

Let's understand it now.

my @books = $self->app->{_dbh}->resultset('Book')->search({});

This will fetch all the results for Book table and store it in @books array. And then we need to iterate and restructure the array on format we want.

$self->render(msg => 'List of books!', books => \@books);

It will render the variables 'msg' and 'books' to the list_books.htmls.ep as we discussed before.

Now it's time to create the template list_book.html.ep under "template/books".

cd template
mkdir books
cd books
touch list_books.html.ep

Open the file list_books.html.ep

<%= $msg %>
<br />
<br />
% for my $item (@$books) {
  <strong><%= $item->{name} %> <==></strong>
  <strong><%= $item->{author_name} %> <==></strong>
  <strong><%= $item->{price} %></strong> <br /><br />
% }

Now, visit http://localhost:3000/books

You will see the list of books, that we inserted earlier in the database. :) :) :)

Delicious Mojolicious!!!!