Connecting PHP to SQLite with PDO

Use sqlite: and a local database path for a lightweight relational store.

The examples focus on the operation itself, including the important distinction: Network MySQL and PostgreSQL use server connections; SQLite opens a database file.

What It Does

Use the examples below to observe the behavior on a small dataset before extending it to application code.

Before You Start

The examples use PHP 8.2+ features where shown. Database snippets require the corresponding PDO driver, demo tables, and credentials configured outside source code.

Example 1: Basic Use

The SQLite file must be writable by the PHP worker and should not be exposed under a public document root.

<?php
$pdo = new PDO("sqlite:" . __DIR__ . "/demo.sqlite", null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, text TEXT NOT NULL)");
$pdo->prepare("INSERT INTO notes(text) VALUES (?)")->execute(['note-0']);
echo $pdo->query("SELECT COUNT(*) FROM notes")->fetchColumn();

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 2: Checking Behavior

The SQLite file must be writable by the PHP worker and should not be exposed under a public document root.

<?php
$pdo = new PDO("sqlite:" . __DIR__ . "/demo.sqlite", null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, text TEXT NOT NULL)");
$pdo->prepare("INSERT INTO notes(text) VALUES (?)")->execute(['note-1']);
echo $pdo->query("SELECT COUNT(*) FROM notes")->fetchColumn();

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 3: Practical Example

The SQLite file must be writable by the PHP worker and should not be exposed under a public document root.

<?php
$pdo = new PDO("sqlite:" . __DIR__ . "/demo.sqlite", null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, text TEXT NOT NULL)");
$pdo->prepare("INSERT INTO notes(text) VALUES (?)")->execute(['note-2']);
echo $pdo->query("SELECT COUNT(*) FROM notes")->fetchColumn();

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Important Behavior and Edge Cases

File permissions and concurrent writes require attention; in-memory sqlite::memory: databases vanish with the connection.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• Use sqlite: and a local database path for a lightweight relational store.
• File permissions and concurrent writes require attention; in-memory sqlite::memory: databases vanish with the connection.
• Network MySQL and PostgreSQL use server connections; SQLite opens a database file.

Use Connecting PHP to SQLite with PDO when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.