=head1 NAME DBI: An interface to relational databases =head1 MODULES DBI =head1 PREAMBLE use DBI; sub _setup_db { my $schema = <<'EOF'; CREATE TABLE `users` ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, firstname VARCHAR(255) NOT NULL DEFAULT '', lastname VARCHAR(255) NOT NULL DEFAULT '' ); INSERT INTO `users` (firstname, lastname) VALUES ('John', 'Doe'); INSERT INTO `users` (firstname, lastname) VALUES ('Larry', 'Wall'); EOF my $dbh = DBI->connect('dbi:SQLite:database.db') or die DBI->errstr; $dbh->do($_) for split /;/, $schema; } _setup_db(); =head1 ABSTRACT You will learn how to make queries to relational databases. SQL knowledge is required. =head1 DESCRIPTION You will learn how to make queries to relational databases. SQL knowledge is required. =head1 TUTORIAL =head2 What is DBI? The L module is the standard database interface module for Perl. As you may know there are many relational databases out there, and every one them has different interface. In order to deal with this diversity L hides all the implementation details in database drivers providing a unified interface. =head2 Environment In this tutorial we are going to use C database because it's light, fast and suits well for learning purposes. Before every code example and exercise a C database is created with the following tables: # no-run CREATE TABLE `users` ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, firstname VARCHAR(255) NOT NULL DEFAULT '', lastname VARCHAR(255) NOT NULL DEFAULT '' ); Also all the example load L modules as: # no-run use DBI; =head2 Connecting to and disconnecting from a database Connecting and disconnecting is simple: just use C and C methods. In case of C where a database is a file, we do not have to provide a C and C. my $dbh = DBI->connect('dbi:SQLite:database.db') or die "Can't connect to database: " . DBI->errstr; # do some queries here $dbh->disconnect; The first C parameter is called a C where you should provide a database driver name (C in our case) and its parameters (path to database in our case). When database is not available C methods fails to return a C<$dbh> object and usually it is a good practice to stop right there dying with an error that is located in C<< DBI->errstr >>. Calling C usually is not required. Automatic disconnect is performed as soon as C<$dbh> variable is out of scope. If you don't want to check every call for a return value, you can set a C attribute and exceptions will be raise automatically. my $dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1}); # do some queries here $dbh->disconnect; =head2 General workflow A general L workflow might look like this: =over =item * Connect to database =item * Prepare query =item * Execute query =item * Fetch data =item * Disconnect from database =back Let's try to select all the users from database and print their first and last names. my $dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1}); my $sth = $dbh->prepare('SELECT firstname, lastname FROM users'); $sth->execute; while (my @row = $sth->fetchrow_array) { my ($firstname, $lastname) = @row; say "$firstname $lastname"; } Besides C there is also C which returns a reference instead of an array. Sometimes you might want to fetch all the data at once instead of getting row by row, you can use C method for that: my $dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1}); my $sth = $dbh->prepare('SELECT firstname, lastname FROM users'); $sth->execute; my $rows = $sth->fetchall_arrayref; foreach my $row (@$rows) { my ($firstname, $lastname) = @$row; say "$firstname $lastname"; } =head2 Placeholders and bind values When you want to insert into SQL your own parameters you of course can always do it this way: my $dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1}); my $firstname = 'John'; my $sth = $dbh->prepare( "SELECT firstname, lastname FROM users WHERE firstname = '$firstname'" ); $sth->execute; while (my @row = $sth->fetchrow_array) { my ($firstname, $lastname) = @row; say "$firstname $lastname"; } But B! This kind of interpolation when used not carefully can end up with an SQL injection which can be very dangerous and result into data loss and server damage. When you want to insert parameters into SQL use placeholders and bind values: my $dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1}); my $firstname = 'John'; my $sth = $dbh->prepare( 'SELECT firstname, lastname FROM users WHERE firstname = ?'); $sth->execute($firstname); while (my @row = $sth->fetchrow_array) { my ($firstname, $lastname) = @row; say "$firstname $lastname"; } Placeholders can also be used for performance gain, since the query doesn't have to be recompiled every time. my $dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1}); my $sth = $dbh->prepare( 'SELECT firstname, lastname FROM users WHERE firstname = ?'); for (qw/John Pete Sam/) { $sth->execute($_); while (my @row = $sth->fetchrow_array) { my ($firstname, $lastname) = @row; say "$firstname $lastname"; } } =head2 Transactions Transactions are important part of every database interaction. It allows you to do several queries in an atomic way. If something fails, everything is rolled back. All or nothing. The following workflow is usually used when working with transactions in L: =over =item * Set C to C<0> =item * Set C to C<1> =item * Wrap code into C =item * Commit or rollback when checking C<$@> variable =back Let's see how that looks in practice. The following transaction is going to be successful and all the users will be added. my $dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1}); $dbh->{AutoCommit} = 0; my @users; eval { my $sth = $dbh->prepare( 'INSERT INTO `users` (firstname, lastname) VALUES (?, ?)'); $sth->execute('Tom', 'Jones'); $sth->execute('Bill', 'Tree'); $dbh->commit; $dbh->{AutoCommit} = 1; 1; } or do { my $e = $@; $dbh->rollback; $dbh->{AutoCommit} = 1; warn "Error: $e\n"; }; my $sth = $dbh->prepare( 'SELECT firstname, lastname FROM users'); $sth->execute; while (my @row = $sth->fetchrow_array) { my ($firstname, $lastname) = @row; say "$firstname $lastname"; } The next example is going to fail and not even one user will be added. my $dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1}); $dbh->{AutoCommit} = 0; my @users; eval { my $sth = $dbh->prepare( 'INSERT INTO `users` (firstname, lastname) VALUES (?, ?)'); $sth->execute('Tom', 'Jones'); $sth->execute('Bill', 'Tree'); die 'Something bad happened'; $dbh->commit; $dbh->{AutoCommit} = 1; 1; } or do { my $e = $@; $dbh->rollback; $dbh->{AutoCommit} = 1; warn "Error: $e\n"; }; my $sth = $dbh->prepare( 'SELECT firstname, lastname FROM users'); $sth->execute; while (my @row = $sth->fetchrow_array) { my ($firstname, $lastname) = @row; say "$firstname $lastname"; } =head2 Tracing and profiling DBI =head3 Tracing Sometimes you want to see the actual queries that L is generating. This can be easily achieved by using C static method. When running a normal Perl script you can also set environment variable C to the same value. C can accept several values which control verbosity and output logging. In the following example we are going to set C to C<2>. DBI->trace(2); my $dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1}); my $firstname = 'John'; my $sth = $dbh->prepare( 'SELECT firstname, lastname FROM users WHERE firstname = ?'); $sth->execute($firstname); =head3 Profiling The easiest way to profile L is to set C<< $dbh->{Profile} >> attribute or C environment variable. my $dbh = DBI->connect('dbi:SQLite:database.db', undef, undef, {RaiseError => 1}); $dbh->{Profile} = 2; my $firstname = 'John'; my $sth = $dbh->prepare( 'SELECT firstname, lastname FROM users WHERE firstname = ?'); $sth->execute($firstname); Profiling itself can be very sophisticated. Check out L for more information on this topic. =head1 AUTHOR Viacheslav Tykhanovskyi, C =head1 LICENSE L