I don't even know how many SQL statements I wrote in my programming life so far. Embedding the SQL into your code, whatever language you use, makes it less readable, uglier and - especially if the complex queries - particularly inelegant. Even when coding in a MVC fashion (which is highly recommended in some cases, like web applications) you end up to just moving the ugly code to the model part of your program, while removing it entirely would probably be the best choice. The problem is that you actually need to query the database somehow, so you can't remove the SQL code; however, you can put something else in place of SQL statements. Let's see what, from the point of view of a Perl programmer of course. ;-)
There are modules which allow SQL abstraction by generating SQL code from data structures. They let you use an arbitrary complex structure to build an arbitrary complex query without inserting any database-related code into your Perl program. Note that this is different from what ORMs - Object Relational Mappers - do: the latter abstract the SQL database into objects, while what I'm talking about abstracts the queries. However, a good ORM such as DBIx::Class provides query abstraction as well, usually by relying on one of the modules available on CPAN. You'll probably want to use an ORM, but that's not what I'm discussing here.
In this short article I'll cover SQL::Abstract, which is also the module DBIx::Class uses. It is fairly easy to get used to it to write simple queries, and with a bit of thought also the complex queries can factored into Perl data structures. For instance if you have:
my $query = "SELECT users.name, users.phone, users.email
FROM users
WHERE users.id = 1 OR users.id = 2
ORDER BY id
";
my $sth = $dbh->prepare($query) or die $dbh->errstr;
$sth->execute() or die $dbh->errstr;
You can write this instead with SQL::Abstract:
my $sql = SQL::Abstract->new();
my ($query, @bind) = $sql->select(
'users',
[ 'name', 'phone', 'email' ],
{ id => [ 1, 2 ] },
[ 'id' ],
);
my $sth = $dbh->prepare($query) or die $dbh->errstr;
$sth->execute(@bind) or die $dbh->errstr;
You might protest that the code is longer, and it actually is a bit (even though code formatting contributes to that). It's however much clearer: there's no extra-Perl code around field names and values, and you can easily understand what the query does at first sight. The select method accepts four parameters: the name of the table on which you want to work, the fields you want to retrieve, the where clause, and the optional ordering of the results. You get, as return values, a string containing the query, and the array of the bound values which you need to use when calling execute().
