[dbsrgits/DBIx-Class-Manual-SQLHackers.git] / lib / DBIx / Class / Manual / SQLHackers / Introduction.pod

=head1 NAME

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

=head2 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 database. 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 without editing the code. 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 would you not want SQL in your Perl code? 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 OO where it can. DBI is a low level library that gets things right, but returns plain hashes and arrays, not objects.

Perl ORMs still use DBI underneath, extending it to catch coding errors early (names of columns, SQL clauses), and to return the results as objects containing the database values, which work just like any other Perl object.

DBIx::Class solves these issues, you write your SQL in perl instead of plain text. The syntax will be checked for you, existance of columns, catching typos and so on. 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).
Commit	Line	Data
6c2a4396	1	=head1 NAME
	2
	3	DBIx::Class::Manual::SQLHackers::Introduction
	4
	5	=head2 Introduction (Why ORMs and DBIx::Class)
	6
	7	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 database. 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.
	8
	9	## Example
	10	my $dbh = DBI->connect("dbi:SQLite:mydb.db");
	11	my $sth = $dbh->select("SELECT artist.id, artist.name FROM artists");
	12	$sth->execute;
	13	my $results = $sth->fetchrow_arrayref({});
	14
	15	foreach my $row (@$results) {
	16	print $row->{name};
	17	}
	18
	19	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 without editing the code. 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.
	20
	21	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.
	22
	23	Why would you not want SQL in your Perl code? 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.
	24
	25	Modern Perl should also leverage OO where it can. DBI is a low level library that gets things right, but returns plain hashes and arrays, not objects.
	26
	27	Perl ORMs still use DBI underneath, extending it to catch coding errors early (names of columns, SQL clauses), and to return the results as objects containing the database values, which work just like any other Perl object.
	28
	29	DBIx::Class solves these issues, you write your SQL in perl instead of plain text. The syntax will be checked for you, existance of columns, catching typos and so on. 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).
	30