Applications with Mojolicious – Part Seven: Relationships in DBIx::Class

Please note! These articles were written well over a year ago, so parts of the article may be outdated.

In this post, we extend our database schema with two more tables: Users and Comments. This will demonstrate the use of relationships between tables using DBIx::Class.

Please note! These articles were written early 2014 and kept updated for Mojolicious 5 only, so the majority of this article may be outdated.

Mojolicious Series

Relationships

In SQL, relations between data is best represented with foreign keys between tables and that related data columns is later retrieved through JOINs. DBIC allows us to hide details of queries through straight-forward accessors in the related result classes.

Let's incorporate relationships in Moblo by introducing two new user stories:

As a registered user, I want to be able to create new posts. Currently, there is only one logged in user named foo.

As a registered user, I want to be able to comment on posts in the blog.

Updated Database Schema

First, we identifiy the requirements given in the three user stories as new tables (depicted in green in the figure beneath). The user table contains a username and password hash (so far underspecified) for login, as well as the full name of the user. If the user is an author, zero or more posts will be attributed to her. Comments are created by a single user and related to a single post.

The updated database schema containing 'users' and 'comments'
table.

Now, to reproduce these changes in the actual DBIx::Class schema, we create a new result class for each new table.

lib/Moblo/Schema/Result/User.pm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
package Moblo::Schema::Result::User;
use base qw/DBIx::Class::Core/;

# Associated table in database
__PACKAGE__->table('users');

# Column definition
__PACKAGE__->add_columns(

    id => {
        data_type => 'integer',
        is_auto_increment => 1,
    },

    pw_hash => {
        data_type => 'text',
    },

    username => {
        data_type => 'text',
    },

    fullname => {
        data_type => 'text',
    },

);


# We want 'username' to be unique,
# this adds an SQL UNIQUE constraint
__PACKAGE__->add_unique_constraint(
    [ qw/username/ ],
);


# Tell DBIC that 'id' is the primary key
__PACKAGE__->set_primary_key('id');

1;

lib/Moblo/Schema/Result/Comment.pm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
package Moblo::Schema::Result::Comment;
use base qw/DBIx::Class::Core/;

use DateTime;

# Associated table in database
__PACKAGE__->table('comments');

# Load ColumnDefault for datetime
__PACKAGE__->load_components(qw/ColumnDefault Core/);
# Automatically load datetime columns into DateTime objects
__PACKAGE__->load_components(qw/InflateColumn::DateTime/);


# Column definition
__PACKAGE__->add_columns(

    id => {
        data_type => 'integer',
        is_auto_increment => 1,
    },

    post_id => {
        data_type => 'integer',
    },

    user_id => {
        data_type => 'integer',
    },

    created_at => {
        data_type => 'datetime',
        # call 'now()' SQL for current time
        is_nullable => 1,
        default_value => \"(datetime('now'))",
    },

    content => {
        data_type => 'text',
    }

);

# Tell DBIC that 'id' is the primary key
__PACKAGE__->set_primary_key('id');

__PACKAGE__->belongs_to(
    # Name of the accessor for this relation
    post =>
    # Foreign result class
    'Moblo::Schema::Result::Post',
    # Foreign key in THIS table
    'post_id'
);

__PACKAGE__->belongs_to(
    commenter =>
    'Moblo::Schema::Result::User',
    'user_id'
);

1;

We'll also have to update the Post result class and change the author text column to a foreign key author_id.

lib/Moblo/Schema/Result/Post.pm

1
2
3
4
5
6
7
8
9
# Column definition
__PACKAGE__->add_columns(

    # .. snip ..

    author_id => {
        data_type => 'integer',
    },
);

You'll note that this contains the column information, but no definition about foreign keys. We have to tell DBIC about the kind of relationship. For our new tables, these are as follows:

has_many relationship (that is, zero or more records in the foreign table posts): Users -> Posts, Posts -> Comments

has_one relationship. Posts -> User (one author per post)

belongs_to relationship. Comments -> Posts, Comments -> Users. Each comment has a foreign key to one user and post.

We add the relationships for each table to the bottom of each associated result class:

Moblo::Schema::Result::User

1
2
3
4
5
6
7
8
9
10
11
12
13
14
__PACKAGE__->has_many(
    # Name of the accessor for this relation
    posts =>
    # Foreign result class
    'Moblo::Schema::Post',
    # Foreign key in the table 'posts'
    'author_id'
);

__PACKAGE__->has_many(
    comments =>
    'Moblo::Schema::Comment',
    'user_id'
);

Moblo::Schema::Result::Comment

1
2
3
4
5
6
7
8
9
10
11
12
13
14
__PACKAGE__->belongs_to(
    # Name of the accessor for this relation
    post =>
    # Foreign result class
    'Moblo::Schema::Post',
    # Foreign key in THIS table
    'post_id'
);

__PACKAGE__->belongs_to(
    commenter =>
    'Moblo::Schema::User',
    'user_id'
);

Moblo::Schema::Result::Post

1
2
3
4
5
6
7
8
9
10
11
12
13
14
__PACKAGE__->belongs_to(
    # Name of the accessor for this relation
    author =>
    # Foreign result class
    'Moblo::Schema::User',
    # Foreign key in THIS table
    'author_id'
);

__PACKAGE__->has_many(
    comments =>
    'Moblo::Schema::Comment',
    'post_id'
);

Overview: Using Relationships

If you're in a Mojo::Controller, and access a resultset, you can fetch all related rows with one method call:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
my $post = $self->db->resultset('Post')->first;

# Access the relationship through the name we gave it above.
my $comments = $post->comments;
my $author = $post->author;

say $author->username . " created this post.";
say "It contains " . $comments->count . " comments";

# Iterate related comments
while (my $comment = $comments->next) {
    say $comment->content;
}

# Creating a comment
my $commenter = $self->db->resultset('User')->first;
$post->create_related('comments' {
    # Column data
    user_id => $commenter->id,
    content => 'This is awesome.',
});

Database Migration

We still need to update our database to reflect the schema, or the changes in our schema will conflict with the database, leading to errors when starting / using the app.

Building the Second Migration

As this is the second migration, raise the schema version in lib/Moblo/Schema.pm to 2. Next, let dbic-migration prepare the migration path.

1
2
dbic-migration -S 'Moblo::Schema' -Ilib --database SQLite prepare
 Copying Fixture Confs from share/fixtures/1/conf to share/fixtures/2/conf

When applying the upgrade, the migration will fail. Can you guess why?

1
2
3
dbic-migration -S 'Moblo::Schema' -Ilib --database SQLite upgrade
Reading configurations from /share/fixtures/1/conf
DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator::try {...} (): DBI Exception: DBD::SQLite::db do failed: posts_temp_alter.author_id may not be NULL [for Statement "INSERT INTO posts_temp_alter( id, title, content, date_published) SELECT id, title, content, date_published FROM posts"] at [*snip*] line 254.

The important part is author_id may not be NULL. Recall that we've removed the author column and instead created an author_id foreign key to the users table, which must not be NULL. There is no way dbic-migration can upgrade that for us. We will have to think of some alternative.

But first, note the new files in the migrations folder:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
─ SQLite
  ├── deploy
  │   ├── 1
  │   │   └...
  │   │
  │   └── 2
  │       ├── 001-auto-__VERSION.sql
  │       └── 001-auto.sql
  ├── downgrade
  │   └── 2-1
  │       └── 001-auto.sql
  └── upgrade
      └── 1-2
          └── 001-auto.sql

DBIx::Class::Migration has generated SQL for upgrading (from version 1), downgrading from 2 to 1 and deploying a database at 2. Obviously, deploying a database at version 2 is fine, as no data will be available to conflict with the schema.

The workaround is either to delete your posts or to create a user in the migration process to pass to the posts table foreign key. This is a bit more tricky in SQLite, as it does not have MODIFY COLUMN, so we create a temporary table instead.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE TEMPORARY TABLE posts_temp_alter (
  id INTEGER PRIMARY KEY NOT NULL,
  author_id integer,
  title text NOT NULL,
  content text NOT NULL,
  date_published datetime NOT NULL,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Create legacy user 'foo' to cover posts from first revision
INSERT INTO users (username, pw_hash, fullname) VALUES('foo', '--INVALID--', 'Legacy Author');
-- Copy old posts
INSERT INTO posts_temp_alter( id, title, content, date_published) SELECT id, title, content, date_published FROM posts;
-- Set legacy author
UPDATE posts_temp_alter SET author_id = (select id from users where users.username = "foo" LIMIT 1);


;
DROP TABLE posts;

;
CREATE TABLE posts (
  id INTEGER PRIMARY KEY NOT NULL,
  author_id integer NOT NULL,
  title text NOT NULL,
  content text NOT NULL,
  date_published datetime NOT NULL,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX posts_idx_author_id02 ON posts (author_id);
INSERT INTO posts SELECT id, author_id, title, content, date_published FROM posts_temp_alter;
DROP TABLE posts_temp_alter;
COMMIT;

If you re-run upgrade, it should complete without any errors. To check that the database and schema modules are in sync, run the status command:

1
2
3
$> dbic-migration -S 'Moblo::Schema' -Ilib --database SQLite status
Schema is 2
Deployed database is 2

That's it! With this step done, we're ready to implement the new features in our app.

Integration with Moblo

We still need to implement the necessary changes in our blog app to use the new features.

Remember the mocked method user_exists in the Login controller? It checked only against one valid combination. Time to replace that.

We will replace it with a login form that provides us with a username and a (plaintext) password. We want to check the password against our database without storing the actual plaintext password there, so we'll need a password-based key derivation function. As the time of this writing, bcrypt comes to mind and is simply good enough, however there's also scrypt which theoretically claims to be better (harder/slower) than bcrypt. There's also PBKDF2.

There exists a plugin for bcrypt in Mojolicious Mojolicious::Plugin::Bcrypt, which will make it very easy to employ it in our app. First, install from CPAN and enable the plugin in the main Moblo.pm module by adding $self->plugin('bcrypt'); to it.

Next, we alter the user_exists method in the Login controller, and add the logged in user id to the session data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
sub user_exists {
    my ($username, $password) = @_;

    # Determine if a user with 'username' exists
    my $user = $self->db->resultset('User')
        ->search({ username => $username })->first;

    # Validate password against hash, if any user found
    return (
        defined $user &&
        $self->bcrypt_validate( $password, $user->pw_hash )
    );
}

sub on_user_login {
    my $self = shift;

    # Grab the request parameters
    my $username = $self->param('username');
    my $password = $self->param('password');

    if (my $user = user_exists($username, $password)) {

        $self->session(logged_in => 1);
        $self->session(username => $username);
        $self->session(user_id => $user->id);

        $self->redirect_to('restricted_area');
    } else {
        $self->render(text => 'Wrong username/password', status => 403);
    }
}

Creating Users

You've already seen how to create new records for a given result class. For creating users/authors, we'll instead create a custom Mojolicious::Command to create users from the commandline.

To create the command, create the folders lib/Mojolicious/Command/generate/ and within it the file user.pm:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package Mojolicious::Command::generate::user;
use Mojo::Base 'Mojolicious::Command';

has description => 'Generate a new user for Moblo.';
has usage => 'APPLICATION generate user [USERNAME] [PASSWORD] [FULL NAME]';

sub run {
  my ($self, $user, $password, $name) = @_;

  die "Missing attributes" unless ($user && $password && $name);

  # Get the resultset
  my $users = $self->app->db->resultset('User');

  # Create the new record
  my $created = $users->create({
    username => $user,
    pw_hash => $self->app->bcrypt($password),
    fullname => $name,
  });

  say "Created user '$user' with id " . $created->id;
}

1;

To generate a user from the command-line, execute:

1
$> ./script/moblo generate user yourname secure! "Your full name"

Note: If you want to allow users to register themselves, use the post form as a starting point and create route, template and controller method for it. It is really straight-forward.

Commenting

For comments to make any sense, we'll first create a distinct page for each blog entry. First, create the necessary routes:

lib/Moblo.pm

1
2
3
4
5
# View posts (no login)
$r->get('/post/:id', [id => qr/\d+/])->name('show_post')->to('Post#show');

# Create comments
$authorized->post('/post/:id/comments', [id => qr/\d+/])->name('create_comment')->to('Post#comment');

And create the two route methods, Post#show and Post#comment.

lib/Moblo/Post.pm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
sub show {
    my $self = shift;
    my $post = $self->_post_from_stash;
    my $user = $self->_user_from_session;

    if (defined $post) {
        $self->render(post => $post, logged_in => defined($user), user => $user);
    } else {
        $self->render_not_found;
    }
}

sub comment {
    my $self = shift;

    # Retrieve dbic objects
    my $post = $self->_post_from_stash;
    my $user = $self->_user_from_session;


    $post->create_related('comments', {
        user_id => $user->id,
        content => xml_escape($self->param('content')),
    });

}

# This should be a helper.
sub _user_from_session {
    my $self = shift;

    return $self->db->resultset('User')->find($self->session('user_id'))
    if ($self->session('logged_in'));
}

sub _post_from_stash {
    my $self = shift;
    return $self->db->resultset('Post')->find($self->stash('id'));
}

and update the create method to use the user id, instead of the username:

lib/Moblo/Post.pm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
sub create {
    my $self = shift;
    my $user = $self->_user_from_session;

    # Persist the post
    $self->db->resultset('Post')->create({
            title => $self->param('title'),
            content => $self->param('content'),

            author_id => $user->id,

            # Published now
            date_published => DateTime->now->iso8601,

        });


    $self->flash(post_saved => '1');
    $self->redirect_to('restricted_area');
}

And the related post/show template:

templates/post/show.html.ep

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<h2>Moblo &mdash; Blog</h2>
<h3><%= $post->title %></h3>

<div>
  <p>
    %= $post->content
  </p>
</div>

% my $comments = $post->comments;
% if ($comments->count > 0) {
  <h3>Comments</h3>
  % while (my $comment = $comments->next) {
    <div class="comment">
      <span>From <%= $comment->commenter->fullname %>, posted <%= $comment->created_at %></span>
      <p>
        %= $post->content
      </p>
    </div>
  % }
% }

%# Allow comments, if user is logged in.
% if ($logged_in) {
  <h3>Write comment</h3>
  <form action="<%= url_for('create_comment') %>" method="POST">
    <small>Writing comment as <%= $user->fullname %></small>
    <div>
      <textarea name="content">
      </textarea>
      <p>
        <button type="submit" value="Create">Create</button>
      </p>
    </div>
  </form>
% }

Now, we'll only have to refer to that page from the index template:

templates/post/show.html.ep

1
2
3
4
5
6
7
8
9
10
11
12
<!-- snip -->
% while (my $post = $posts->next) {
  <div class="post">
    <h4>
      <a href="<%= url_for('show_post', id => $post->id) %>"
        <%= $post->title %>
        <br/>
        <small>(published: <%= $post->date_published %>)</small>
      </h4>
    </h4>
  </div>
% }

Additional Documentation

Link to Detailled information about the role and syntax of relationships.

Code at GitHub

You can browse the app at the current state as well as its history on Github.

oliver günther