DEV Community

Ashutosh
Ashutosh

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

And create the database by writing the following command.

create database myApp_database;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Lets add few rows into the table

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

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>

Enter fullscreen mode Exit fullscreen mode

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

dbicdump dbdump.conf
Enter fullscreen mode Exit fullscreen mode

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

Dumping manual schema for MyApp::Schema to directory ./lib/ ...
Schema dump completed.
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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 ;
Enter fullscreen mode Exit fullscreen mode

And then under the statup subroutine

$self->_set_db_operation_handler();
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

*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;
Enter fullscreen mode Exit fullscreen mode

Let's understand it now.

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

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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 />
% }
Enter fullscreen mode Exit fullscreen mode

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

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

Delicious Mojolicious!!!!

Discussion (0)