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

Wrap your database with OO abstraction

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

This post will provide a breif hands-on introduction to ORM in Perl using a few DBIx::Class sub classes.

In detail, this means we will generate Schema, Result and ResultSet classes (Perl modules) which will be used to interface with a SQLite database. The database will contain a single users table. Once the database has been created, we will implement functionality to both return the full name of a user, and find all users under a given age. To ensure the functionality of the schema, this code will be implemented using Test Driven Development (TDD), where the tests will be written first, then made to pass as we add new functionality.

This article is divided into two parts. The first part is doing the groundwork — installing Perl modules, setting up the directory structure, the database to test against, and a unit test against which we can measure our progress. In the second part we will create a database and use DBIx::Class modules to make the tests pass.

Prerequisites

• Understanding of Object Relational Mapping (ORM)
• Basic knowledge of Perl — including sub-routines, references, packages and of course, writing tests.
• SQLite will be used to generate the database.
• cpanm a CLI tool for downloading, building and installing Perl modules.
• Perlbrew is optional but recommended as it’s good for managing module dependencies independently per project.

Getting Started

Start by creating a project directory and empty files where you’ll put your code. The simplest way to do this is to copy and paste the lines below into your terminal and then press enter.

mkdir -p dbix-proj/{lib,scripts,t} && \
touch dbix-proj/{cpanfile,t/basic.t,scripts/{schema.sql,make_schema.pl}}

This gives you a directory structure and files which looks like this:

dbix-proj
├── cpanfile
├── lib
├── scripts
│   ├── make_schema.pl
│   └── schema.sql
└── t
    └── basic.t

Project Dependencies

The modules required for the tests and generating the class schema need to be added to the cpanfile and then installed.

Open the cpanfile and add the following modules.

requires 'DBIx::Class';
requires 'DBIx::Class::Schema::Loader';
requires 'Test::More';

Install Modules

From within the dbix-proj directory install the modules using the cpanm command.

cd dbix-proj
cpanm --installdeps .

The modules should now be installed.

Note: Depending on what you already have installed (and your bandwidth) this can take a few minutes minutes. This is because cpanm will run the tests which ship with each module. Scan ahead or go and make a coffee — you’ll see it’s well worth the wait!

To make sure these packages are found by Perl (if not using perlbrew) run this:

perl -e 'use DBIx::Class'

If you get an error message like:

Can't locate DBIx/Class.pm in @INC (you may need to install
the DBIx::Class module) (@INC contains:...

then you need to update @INC to include the directory where the packages were installed:

export PERL5LIB=~/perl5/lib/perl5/

The Database

Here we will create a database schema and later use it to create our class schema in part 2.

Writing the SQL

Open the file scripts/schema.sql and enter the following SQL statements.

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id          INTEGER PRIMARY KEY,
    age         INTEGER NOT NULL,
    firstname   TEXT    NOT NULL,
    lastname    TEXT    NOT NULL
);
INSERT INTO users VALUES (NULL, 55, 'Bob', 'Doe');
INSERT INTO users VALUES (NULL, 30, 'Sarah', 'Connor');

Create the Database

Now lets create the database and its tables, and populate it with data in one go using SQLite. In the root directory enter the following command in your terminal.

sqlite3 app.db < scripts/schema.sql

Verify the Database

To verify the database schema has been created, open the app.db database as follows.

sqlite3 app.db

Now select all the users in the users table.

select * from users;

The results should read as follows

1|55|Bob|Doe
2|30|Sarah|Connor

Writing a Test

Now some basic tests will be written to check whether our class schema exists and the modules within its namespace ‘work as intended’.

t/basic.t

Open the file t/basic.t and write the following:

#!/usr/bin/env perl
use strict;
use warnings;
use Test::More;

# Check the schema exists
use_ok('App::Schema');
my $schema  = App::Schema->connect('dbi:SQLite:app.db');
my $user_rs = $schema->resultset('User');
# Check custom accessors are defined
can_ok($user_rs->result_class, qw(fullname));
is($user_rs->find(1)->fullname,
  'Bob Doe', 'Should read from set using custom accessor');
# Check custom methods are defined
can_ok($user_rs, qw(age_less_than));
is($user_rs->age_less_than(50)->fullname,
  'Sarah Connor', 'Should perform search using custom method');
done_testing;

And that’s our test. Although commented I will briefly explain the entire file.

First Test::More is imported so we can use its functions use_ok, can_ok and is which it exports. Each of these functions is used to prove a test case.

• Next, our soon to be generated ‘App::Schema’ module is imported. Since it is not yet implemented the use_ok just below it will fail.
• The next part is connecting to the database and creating a ResultSet instance for the User source. This is how we will interface with the user table in the second part of this article.
• Finally the tests which follow use can_ok and is, to check whether a custom accessor fullname or method age_less_than have been defined first, before trying to use them.

Run the Test

Now let’s run the test using prove and the flags -l which means check the lib directory for modules, and -v to provide verbose output.

prove -lv t/basic.t

The test should fail and the output should look similar to this:

t/basic.t .. Can’t locate App/Schema.pm in @INC (you may need to install the App::Schema module)
...
Result: FAIL

The failure occurred because there is no App::Schema defined since it has not been generated yet. This will be done in part 2.

Summary

The sections above focused on getting the project set up, so that in the next part we can just focus on generating the class schema. One important file that was created here is t/basic.t, our test file. This will be used throughout the second part to check whether everything is working as intended.