SQLite in PHP: file databases with PDO

The SQLite3 extension has been available by default since PHP 5.3.0. In practice you often talk to SQLite through PDO with the sqlite driver.

Checking the driver

print_r(PDO::getAvailableDrivers());

Look for sqlite in the list.

Connecting

try {
    $dbh = new PDO("sqlite:my/database/path/database.db");
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo $e->getMessage();
}

If the file does not exist yet, PDO creates it automatically — and it starts empty.

Unlike MySQL, you do not create a database with a special statement and then select it. The file is the database.

Note

This does not apply to SQLite:

$dbh->exec('SET NAMES "utf8"');

Creating a table

$dbh->exec("CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY,
    title TEXT,
    message TEXT,
    time INTEGER
)");

For general PDO patterns (attributes, prepared inserts, closing the connection), see PDO .