How to integrate DBIx with Mojolicious?
Tags: perl, mojolicious, dbix
Published: 2020-03-24 10:51:55

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](https://ashutosh.dev/blog/post/2020/03/how-to-install-mojolicious).

## 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:

```shell
mojo generate app MyApp
```

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

Go to my_app.

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

Folder structure

![](https://user-images.githubusercontent.com/12954663/77341969-67274380-6d27-11ea-99d5-d06b5dad049b.png)



Visit http://localhost:3000

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

![](https://user-images.githubusercontent.com/12954663/77342525-3a276080-6d28-11ea-8007-2c72b146b65b.png)



## Setting up the database

Now create the database myApp_database;

```shell
mysql -u username -p
```

And create the database by writing the following command.

```sql
create database myApp_database;
```

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

```sql
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

```sql
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.

```shell
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.

```shell
dbicdump dbdump.conf
```

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

```shell
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:

![](https://user-images.githubusercontent.com/12954663/77397784-23bbec00-6d9e-11ea-94d0-2626119dbcd8.png)

In DB.pm write the following content

```perl
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 :

```perl
use MyApp::Model::DB ;
```

And then under the **statup** subroutine

```perl
$self->_set_db_operation_handler();
```

And now create the function **_set_db_operation_handler()** 

```perl
# 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.

```perl
$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.

```perl
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.

```perl
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.

```perl
$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".

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

Open the file list_books.html.ep

```html
<%= $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.  :) :) :)

![](https://user-images.githubusercontent.com/12954663/77416502-d2225a00-6dbb-11ea-9db0-3daef58359d4.png) 



Delicious Mojolicious!!!!