Learn ORM in Perl with DBIx::Class - Part 2

Generating the schema classes

source: https://www.flickr.com/photos/kirklewis/32579951031/

In part 1 we laid the foundation for our project, and in this part we will focus on the database and class schema creation. The ‘class schema’ refers to the classes which are based on the database and its tables.

After we generate the classes, we will then create an accessor to return data in a special way, and then a method to perform a custom search. After each stage we can run the tests to check what is both missing and working.

Generating the Schema

We’ll now use the function make_schema_at which is exported by the DBIx::Class::Schema::Loader, to generate the schema classes. The make_schema_at simply reads a RDBMS schema, in our case app.db, and creates the classes based on its tables.

Make Schema Script

Open the file scripts/make_schema.pl and enter the following lines.

#!/usr/bin/env perl
use strict;
use warnings;
use DBIx::Class::Schema::Loader qw(make_schema_at);
my @dsn     = 'dbi:SQLite:dbname=app.db';
my %options = (
    dump_directory => './lib'
);
make_schema_at('App::Schema' => \%options, \@dsn);

A quick explanation of this file - @dsn defines how a database connection is established, and %options contains information which can be used to help create the classes. In this case, dump_directory indicates what directory the classes will be created in. Finally make_schema_at is called to generate the classes under the namespace App::Schema.

Now run our generation script:

perl scripts/make_schema.pl

The contents of the lib directory should now look like this:

lib/
└── App
    ├── Schema
    │   └── Result
    │       └── User.pm
    └── Schema.pm

Run the test with prove -lv t/basic.t and the output should be similar to the following.

ok 1 — use App::Schema;
not ok 2 — App::Schema::Result::User->can('fullname')
…

The first test should pass since App::Schema now exists. However the other four will fail because neither the accessor fullname nor the method age_less_than have yet been implemented. This is covered in the next sections.

Custom Fields and Searches

Okay, now that the class schema is generated it’s time to add some custom functionality to it. We will start by defining a custom accessor to return the full name of a user. This is a shorter way than having to manually concatenate the first and last name wherever a user’s full name is required.

Adding the fullname accessor

Open the file lib/App/Schema/Result/User.pm and add the following method.

sub fullname {
    my $self = shift;
    return $self->firstname . ' ' . $self->lastname;
}

Note: The above sub-routine must be defined below the line that reads as follows:

# Created by DBIx::Class::Schema::Loader v0.07045 @ 2016–08–07 10:02:31
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:

If you make changes to your database such as add a new column to the users table, running the make_schema.pl again will update the User.pm module to reflect the changes. However, it does not remove any code written below the line mentioned. Making any changes above this line can cause the class schema generation to fail.

Test the Accessor

Run the test again and the results should look like this:

ok 1 — use App::Schema;
ok 2 — App::Schema::Result::User->can('fullname')
ok 3 — Should read from set using a custom accessor
not ok 4 — App::Schema::ResultSet::User->can('age_less_than')

The second and third tests now pass since fullname has been defined and is functioning correctly.

As you can see the fullname accessor acts like a Virtual Column in SQL. The fullname field is considered virtual because it is not a column of the users table but it appears that way. Instead the full name is computed using the firstname and lastname fields.

Define the Custom Search Method

Now we will define a method to perform a custom search on the users. This method will be defined on a ResultSet class which can be created as follows:

mkdir -p lib/App/Schema/ResultSet && \
touch lib/App/Schema/ResultSet/User.pm

Now open the file lib/App/Schema/ResultSet/User.pm and enter the following code:

package App::Schema::ResultSet::User;
use base 'DBIx::Class::ResultSet';
sub age_less_than {
   my $self = shift;
   my $age  = shift;
   return $self->find({ age => { '<' => $age }});
}
1;

Test the Custom Method

Run the test again and the output should be as follows.

t/basic.t ..
ok 1 — use App::Schema;
ok 2 — App::Schema::Result::User->can('fullname')
ok 3 — should read from set using a custom accessor
ok 4 — App::Schema::ResultSet::User->can('age_less_than')
ok 5 — should perform search using a custom method
1..5
ok
All tests successful.

The output above indicates that everything is working as we intended. That is, we’re able get the full name of a user using a custom accessor, and we’re able to find all users less than a specified age.

Summary

This post wraps up this brief two part introduction to ORM in Perl with DBIx::Class. Generating the class schema was achieved using a single call to make_schema_at. Adding a custom accessor and method just required adding a sub-routine to both the Result::User and ResultSet::User modules respectively. We also proved our functionality with Test Driven Development.

Thank you for reading and you can recommend this series of posts using the 💚 button.

The code for both parts of this article can be found here.