How to connect to SQLite database using Perl DBI?

Connecting to an SQLite database using Perl's DBI module is straightforward once you understand the basic DBI concepts. DBI (Database Interface) is Perl's standard database access module, providing a consistent interface to interact with various databases. SQLite is a lightweight, serverless SQL database engine, popular for its simplicity and embedded use.

Key Concepts

• DBI->connect(): Method to establish a database connection.
• DSN (Data Source Name): Specifies the driver and database file location. For SQLite, it looks like dbi:SQLite:dbname=filename.db.
• Handle: The returned database handle lets you prepare and execute SQL statements.
• Placeholders: Use ? in SQL for safe parameter binding.

SQLite connectivity requires the DBD::SQLite driver, which, starting from Perl 5.8+, is often bundled or easy to install via CPAN. It does not require any external server or setup.

Example: Connect and Query SQLite Using Perl DBI

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

# Define the SQLite database file
my $dbname = "test.db";

# Data Source Name: specifies the driver and database file
my $dsn = "dbi:SQLite:dbname=$dbname";

# Connect to the database (no username/password for SQLite)
my $dbh = DBI->connect($dsn, "", "", {
    RaiseError => 1,
    AutoCommit => 1,
}) or die $DBI::errstr;

print "Connected to SQLite database '$dbname' successfully.\n";

# Create a table
$dbh->do("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)");

# Insert a few records
my $sth = $dbh->prepare("INSERT INTO users (name) VALUES (?)");
for my $name ("Alice", "Bob", "Charlie") {
    $sth->execute($name);
}
print "Inserted 3 records.\n";

# Query the table
$sth = $dbh->prepare("SELECT id, name FROM users ORDER BY id");
$sth->execute();

print "Users in 'users' table:\n";
while (my @row = $sth->fetchrow_array) {
    print "ID: $row[0], Name: $row[1]\n";
}

# Disconnect properly
$dbh->disconnect;
print "Disconnected cleanly.\n";

Explanation

• The $dsn specifies "dbi:SQLite:dbname=test.db", telling DBI to use the SQLite driver with the database file "test.db".
• DBI->connect() returns a database handle $dbh. With SQLite, no username or password is needed.
• RaiseError => 1 makes DBI throw exceptions on errors, simplifying error handling.
• We create a table if it doesn't exist and insert users using placeholders ? for security and clarity.
• Fetching rows uses fetchrow_array. Remember, fetching in void or scalar context has different semantics.
• Finally, disconnect is called to close the connection cleanly.

Common Pitfalls

• Forgetting to install DBD::SQLite will cause DBI->connect to fail. Install it using cpan DBD::SQLite if needed.
• SQLite is just a file, so ensure your Perl process has write permissions on the database file location.
• Failing to handle errors or set RaiseError manually can make debugging harder.
• Remember that SQLite locks the database file during writes, so concurrent writes need careful handling if your app scales.

Version Notes

• The example is compatible with Perl 5.10+ comfortably.
• RaiseError and AutoCommit are available since early DBI versions; no modern Perl feature required.
• SQLite versions managed by DBD::SQLite may vary; most core functionality is consistent.

With this, you can quickly integrate SQLite databases into your Perl scripts without much setup. DBI's flexibility means you can later switch databases with minimal code changes, thanks to its consistent API style.