NAME

DBIx::Class::Manual::SQLHackers::Introduction

Introduction (Why ORMs and DBIx::Class)

Almost every sizable Perl application these days needs a method of long term data storage. When the data needs to be easily retrieved as well as stored, we often use a relational database (or RDBMS). Most databases can be comfortably accessed using SQL. Using the DBI module, and a DBD for the particular database, we can write SQL in our Perl code, and retrieve the results as arrays or hashes.

## Example
my $dbh = DBI->connect("dbi:SQLite:mydb.db");
my $sth = $dbh->select("SELECT artist.id, artist.name FROM artists");
$sth->execute;
my $results = $sth->fetchrow_arrayref({});

foreach my $row (@$results) {
  print $row->{name};
}

There are several things we can do to make this code more usable, for example store the database connect string (DSN) in a configuration file so that users of the code can use different databases by simply changing the config. We can also write a separate method or module for creating and returning the $dbh, so that we don't create a lot of db connections unnecessarily.

The part we can't do much about is the SQL in the code. We can move it around, put it in libraries, but it's still there, somewhere.

Why is having SQL mixed with your Perl code not optimal? For a start, it's just a string to pass to the database interpreter, there is no syntax checking at the Perl compilation level. Thus it fails late, not early. Your editor will also not syntax check what it just sees as strings of text.

Modern Perl should also leverage code re-use and OO where it can. DBIx::Class promotes code re-use by allowing you to add methods for common queries, fetch related data in one query and cache data, also without refetching. DBIC still uses the DBI library underneath, so it gets things right while presenting the results in a more manageable way.

DBIx::Class solves these issues, you write your SQL in Perl instead of plain text. Pre-declaring your column names and types and using Perl data structures to create your queries means that the SQL produced will be at least syntactically correct. You can concentrate less on the structure of SQL, and more on the data your code is accessing. It uses objects so that you can write re-usable queries, and string methods together to create complex queries. You define the database layout once, or you export it from your actual database (with ability to re-export on update).