PHP SQLite Tutorial: PDO and SQLite3 Examples (Tested)

PHP can use SQLite with nothing extra to run: the database is a single file next to your code, and PDO talks to it like any other database. In this article I’ll walk you through building a small guestbook with PHP and SQLite, from the first connection to a working form.

TLDR

• PHP talks to SQLite through PDO (pdo_sqlite) or the SQLite3 class. Check with php -m | grep -i sqlite.
• Connect with new PDO('sqlite:/path/to/app.db'). The file is created if it doesn’t exist.
• Set PDO::ERRMODE_EXCEPTION so errors throw, and use prepared statements with placeholders for every value.
• Wrap related writes in beginTransaction() and commit(), with rollBack() on failure.

What is PHP SQLite, and what will we build?

PHP ships two ways to use SQLite:

	PDO with pdo_sqlite	SQLite3 class
API	The same PDO API as MySQL and PostgreSQL	SQLite-only
Connect	new PDO('sqlite:/path/to/app.db')	new SQLite3('/path/to/app.db')
Best for	Code that may move to another database later	Scripts that use only SQLite

Both are documented in the PHP manual (PDO_SQLITE, SQLite3). I’ll use PDO for the main build, because the same code style carries over to other databases.

To show you each step, I will build a guestbook with one entries table and a page that saves form posts.

Prerequisites

Requirement	Version used here
PHP	8.4.26 (the official php:8.4-cli Docker image)
Extensions	pdo_sqlite and sqlite3, both included in that image
SQLite library	3.46.1, bundled with PHP

PHP 8.4.26 | SQLite 3.46.1

Check that your PHP has the extensions:

$ php -m | grep -i sqlite
pdo_sqlite
sqlite3

If pdo_sqlite is missing, install your distribution’s SQLite package for PHP (for example php-sqlite3 on Debian and Ubuntu) and restart PHP.

Building a guestbook with PHP and SQLite

Each step is a separate PHP file in the same folder.

Step 1: Open the database

Keep the connection in one function so every script uses the same settings:

<?php
// db.php: open (or create) the database and make PDO throw exceptions on errors.
function db(): PDO {
    $pdo = new PDO('sqlite:' . __DIR__ . '/guestbook.db');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo->exec('PRAGMA foreign_keys = ON');
    return $pdo;
}

The DSN is sqlite: followed by the absolute path to the file (PDO_SQLITE DSN). ERRMODE_EXCEPTION makes failed queries throw a PDOException instead of failing quietly.

Step 2: Create the table

<?php
require __DIR__ . '/db.php';

$pdo = db();
$pdo->exec('CREATE TABLE IF NOT EXISTS entries (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    message    TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
)');
echo "table ready\n";

$ php create.php
table ready
$ ls guestbook.db
guestbook.db

Connecting created guestbook.db, and CREATE TABLE IF NOT EXISTS makes the script safe to run again.

Step 3: Insert rows with a prepared statement

Prepare the statement once, then run it for each row. Named placeholders keep user input out of the SQL text:

<?php
require __DIR__ . '/db.php';

$pdo = db();
$stmt = $pdo->prepare('INSERT INTO entries (name, message) VALUES (:name, :message)');
foreach ([['Ada', 'Hello from SQLite!'], ['Linus', "It's one file."], ['Grace', 'Nice guestbook']] as [$name, $message]) {
    $stmt->execute([':name' => $name, ':message' => $message]);
    echo "inserted id ", $pdo->lastInsertId(), "\n";
}

$ php insert.php
inserted id 1
inserted id 2
inserted id 3

Step 4: Read rows

query() is fine for SQL with no user input. Anything with a value from outside goes through prepare() and execute():

<?php
require __DIR__ . '/db.php';

$pdo = db();
foreach ($pdo->query('SELECT id, name, message FROM entries ORDER BY id') as $row) {
    echo "{$row['id']}: {$row['name']} says {$row['message']}\n";
}

$stmt = $pdo->prepare('SELECT name FROM entries WHERE id = ?');
$stmt->execute([2]);
var_dump($stmt->fetch());

$ php select.php
1: Ada says Hello from SQLite!
2: Linus says It's one file.
3: Grace says Nice guestbook
array(1) {
  ["name"]=>
  string(5) "Linus"
}

Step 5: Update and delete rows

rowCount() tells you how many rows changed:

<?php
require __DIR__ . '/db.php';

$pdo = db();
$stmt = $pdo->prepare('UPDATE entries SET message = ? WHERE name = ?');
$stmt->execute(['Hello again!', 'Ada']);
echo "updated: ", $stmt->rowCount(), "\n";

$stmt = $pdo->prepare('DELETE FROM entries WHERE name = ?');
$stmt->execute(['Grace']);
echo "deleted: ", $stmt->rowCount(), "\n";
echo "left: ", $pdo->query('SELECT count(*) FROM entries')->fetchColumn(), "\n";

$ php update_delete.php
updated: 1
deleted: 1
left: 2

Step 6: Group writes in a transaction

The second insert breaks the NOT NULL rule, so the whole transaction rolls back and the first insert is undone too:

<?php
require __DIR__ . '/db.php';

$pdo = db();
try {
    $pdo->beginTransaction();
    $pdo->prepare('INSERT INTO entries (name, message) VALUES (?, ?)')->execute(['Kim', 'first']);
    $pdo->prepare('INSERT INTO entries (name, message) VALUES (?, ?)')->execute(['Kim', null]); // NOT NULL fails
    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "rolled back: ", $e->getMessage(), "\n";
}
echo "Kim's entries: ", $pdo->query("SELECT count(*) FROM entries WHERE name = 'Kim'")->fetchColumn(), "\n";

$ php transaction.php
rolled back: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: entries.message
Kim's entries: 0

Step 7: Save a form post from a web page

Save this as public/index.php. It inserts the posted name and message, then lists every entry. htmlspecialchars() escapes the output:

<?php
require __DIR__ . '/../db.php';

$pdo = db();
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $stmt = $pdo->prepare('INSERT INTO entries (name, message) VALUES (?, ?)');
    $stmt->execute([$_POST['name'] ?? '', $_POST['message'] ?? '']);
}
foreach ($pdo->query('SELECT name, message FROM entries ORDER BY id') as $row) {
    echo htmlspecialchars($row['name']), ': ', htmlspecialchars($row['message']), "\n";
}

Step 8: Read a row with the SQLite3 class

If you don’t need PDO, the SQLite3 class works the same way with its own method names:

<?php
// The same query with PHP's SQLite3 class instead of PDO.
$db = new SQLite3(__DIR__ . '/guestbook.db');
$stmt = $db->prepare('SELECT name, message FROM entries WHERE id = :id');
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);
$row = $stmt->execute()->fetchArray(SQLITE3_ASSOC);
echo "{$row['name']}: {$row['message']}\n";
echo "SQLite library: ", SQLite3::version()['versionString'], "\n";
$db->close();

$ php sqlite3_class.php
Ada: Hello again!
SQLite library: 3.46.1

Running it

PHP’s built-in server is enough to try the page locally. The PHP manual warns it “should not be used on a public network” (built-in web server):

php -S 127.0.0.1:8080 -t public

Posting the form with curl saves a row and returns the list:

$ curl -s -d 'name=Ravi' -d 'message=Posted from a form' http://127.0.0.1:8080/
Ada: Hello again!
Linus: It's one file.
Ravi: Posted from a form

The apostrophe in It's comes back as ', which is htmlspecialchars() doing its job.

Common problems

The first two errors came up while testing, each with its message first. The third couldn’t be reproduced here, because the test image has pdo_sqlite built in.

SQLSTATE[HY000] [14] unable to open database file

PHP can’t create or open the file, usually because the folder doesn’t exist or the web server user can’t write to it:

<?php
try {
    $pdo = new PDO('sqlite:/no/such/folder/app.db');
} catch (PDOException $e) {
    echo get_class($e), ": ", $e->getMessage(), "\n";
}

$ php bad_path.php
PDOException: SQLSTATE[HY000] [14] unable to open database file

SQLite also writes temporary journal files next to the database, so the web server needs write access to the folder, not only the file. See unable to open database file.

SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed

The row breaks a UNIQUE constraint. Catch the exception, or use an upsert (SQLite upsert):

<?php
require __DIR__ . '/db.php';

$pdo = db();
$pdo->exec('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, email TEXT UNIQUE)');
$pdo->exec("INSERT INTO users (email) VALUES ('a@example.com')");
try {
    $pdo->exec("INSERT INTO users (email) VALUES ('a@example.com')");
} catch (PDOException $e) {
    echo "SQLSTATE ", $e->getCode(), ": ", $e->getMessage(), "\n";
}

$ php duplicate.php
SQLSTATE 23000: SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: users.email

could not find driver

new PDO('sqlite:...') throws this when the pdo_sqlite extension isn’t loaded. Install it and check php -m again, as in the Prerequisites.

Conclusion

PDO with pdo_sqlite gives a PHP app a real SQL database with no server to run. Prepared statements and transactions cover the everyday work, and exceptions tell you when something fails. SQLite suits a site where one server writes at a time. Once many servers need to write at once, it’s time for a client/server database (SQLite vs MySQL).

Further reading

• SQLite create database if not exists
• SQLite CREATE TABLE IF NOT EXISTS
• SQLite upsert
• SQLite vs MySQL
• PHP docs: PDO_SQLITE, SQLite3

Tested with PHP 8.4.26 (SQLite 3.46.1) in the php:8.4-cli Docker image on 7 October 2026.