How to Connect to SQLite from PHP

SQLite is the easiest database to reach from PHP because there is no server, no host, and no port. You open a file. PHP ships two ways to do it: the database-agnostic PDO layer and the SQLite-specific SQLite3 class. For most code, PDO with the pdo_sqlite driver is the right default; it gives you the same API you would use for MySQL or PostgreSQL, so switching databases later means changing a DSN rather than rewriting every query. The SQLite3 class exposes a few SQLite-only features and is fine when you know you will only ever talk to SQLite. This guide covers both, the file-path gotcha that bites everyone, WAL mode, the busy timeout, transactions, and the errors you will meet. It is written against PHP 8.5 (8.5.7 as of June 2026).

Which extension to use

Both extensions ship with PHP and are enabled by default in most builds:

• PDO with pdo_sqlite is the portable, object-oriented choice. Same interface across databases, exceptions on error, named or positional placeholders. Use this unless you have a specific reason not to.
• SQLite3 is the SQLite-specific class. It exposes a few SQLite-only features such as createFunction() for user-defined functions and loadExtension(). Reach for it when you actually need those.

Check that the driver is loaded:

php -m | grep sqlite
# expect: pdo_sqlite  (and/or sqlite3)

Connecting with PDO

<?php
$dsn = 'sqlite:/var/data/shop.db';
 
try {
    $pdo = new PDO($dsn, null, null, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (PDOException $e) {
    error_log('DB connection failed: ' . $e->getMessage());
    throw $e;
}

There is no username or password; SQLite has no authentication. The DSN is just sqlite: plus a path. Use an absolute path in production: a relative path is resolved against the process's current working directory, which is rarely what you expect under a web server. For a throwaway database that lives only in RAM, use sqlite::memory:.

The file-creation trap

By default, opening a path that does not exist creates an empty database there. If you mistype the filename, you do not get an error; you get a brand-new empty database and a stream of "no such table" errors that look like data loss. This is the single most common SQLite-from-PHP confusion.

PDO has no built-in read-write-but-do-not-create mode, but you can express it with a file URI:

// Fail loudly if the file is missing, instead of creating it
$dsn = 'sqlite:file:/var/data/shop.db?mode=rw';
$pdo = new PDO($dsn);

The mode=rw flag opens the existing file read-write and throws if it is absent. mode=ro opens read-only. If you genuinely want to create a missing file, the default behavior is what you want, but be deliberate about it.

Set these PRAGMAs on every connection

Three connection-scoped settings make SQLite behave well under PHP. They are not persisted in the file, so run them right after connecting:

$pdo->exec('PRAGMA journal_mode = WAL');
$pdo->exec('PRAGMA busy_timeout = 5000');   // milliseconds
$pdo->exec('PRAGMA foreign_keys = ON');

• WAL (write-ahead logging) lets readers and a writer work at the same time instead of blocking each other. For any app with concurrent requests, this is the difference between smooth operation and constant lock errors. WAL is a persistent property of the file once set, but setting it per connection is harmless.
• busy_timeout tells SQLite to wait up to N milliseconds for a lock to clear before giving up. Without it, the default is zero: the moment another connection holds a write lock, you get an immediate database is locked error. Setting 5000 ms eliminates the vast majority of those errors in practice.
• foreign_keys = ON enforces foreign-key constraints, which SQLite leaves off by default for backward compatibility. This is per-connection and must be set every time.

Prepared statements

Never interpolate user input into SQL. Use placeholders:

$stmt = $pdo->prepare('SELECT id, name FROM products WHERE price > :minPrice');
$stmt->execute(['minPrice' => 100]);
 
foreach ($stmt as $row) {
    echo "{$row['id']}: {$row['name']}\n";
}

PDO supports named placeholders (:minPrice) and positional ones (?). Pick one style per statement; you cannot mix them.

Getting a generated key

After an INSERT into a table with an INTEGER PRIMARY KEY (or explicit AUTOINCREMENT), read the row id with lastInsertId():

$stmt = $pdo->prepare('INSERT INTO products (name, price) VALUES (:name, :price)');
$stmt->execute(['name' => 'Widget', 'price' => 9.99]);
$newId = $pdo->lastInsertId();

Modern SQLite (3.35+) also supports RETURNING, so INSERT ... RETURNING id works if you prefer to read the value back as a column.

Transactions are also a performance tool

In SQLite, every standalone INSERT or UPDATE is its own transaction, and each commit waits for the write to reach disk. Wrapping many writes in one transaction turns thousands of disk syncs into one and can make a bulk load orders of magnitude faster:

$pdo->beginTransaction();
try {
    $stmt = $pdo->prepare('INSERT INTO events (name) VALUES (?)');
    foreach ($names as $name) {
        $stmt->execute([$name]);
    }
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

This is not only about correctness; it is the standard way to make batch inserts fast in SQLite.

Connection model: no pooling

SQLite is an in-process library, not a server, so there is no connection pool to manage and no network round trip. Open one connection per request (or one long-lived connection for a CLI worker) and reuse it. Because writes are serialized at the file level, a single writer with WAL and a sensible busy_timeout handles typical web traffic well. If you find yourself fighting persistent lock errors under heavy write load, that is usually the signal to move to a client-server database rather than to add more connections.

Using the SQLite3 class directly

If you need a SQLite-only feature, the SQLite3 class is there. Note that its busy timeout is a method call, not a PRAGMA, and it must be set after opening:

$db = new SQLite3('/var/data/shop.db', SQLITE3_OPEN_READWRITE);
$db->enableExceptions(true);
$db->busyTimeout(5000);
$db->exec('PRAGMA journal_mode = WAL');
 
$stmt = $db->prepare('SELECT id, name FROM products WHERE price > :min');
$stmt->bindValue(':min', 100, SQLITE3_INTEGER);
$result = $stmt->execute();
 
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo "{$row['id']}: {$row['name']}\n";
}

Call enableExceptions(true) for the same reason you set ERRMODE_EXCEPTION on PDO: otherwise errors surface as false return values you have to check by hand. Passing SQLITE3_OPEN_READWRITE without SQLITE3_OPEN_CREATE is the SQLite3 equivalent of the mode=rw trick: it fails on a missing file instead of creating one.

Errors you actually hit

For exploring SQLite data and drafting queries before they go into PHP, Mako connects to SQLite with AI-powered autocomplete. Try it free at mako.ai.