Basic Usage of SQLite in a PHP Application

PHP can access SQLite through the SQLite3 class or PDO_SQLITE. This quick start uses SQLite3; for a PDO-based CRUD walkthrough, see SQLite CRUD tutorials in PHP.

To try SQLite statements separately from a PHP connection, use the SQLite SQL Playground. It runs in your browser; check SELECT sqlite_version(); there and in your PHP runtime if a query depends on a newer SQLite feature.

The exception-handling example below uses PHP 7 or later (Throwable) and requires the sqlite3 extension to be enabled.

Check the PHP extension and database path

The SQLite3 class is provided by PHP’s sqlite3 extension. Check that it is enabled in the PHP runtime used by your application:

<?php
var_dump(extension_loaded('sqlite3'));

Use a path to a database file, not a directory. SQLite3 opens an existing file or creates it if it does not exist. The parent directory must already exist and be writable by the PHP process. For a web application, store the database outside the public document root so visitors cannot download it directly. See the PHP SQLite3::__construct() documentation.

Create a table and insert data with a prepared statement

Enable exceptions so database errors can be caught. Bind values instead of concatenating strings into SQL; prepared statements keep input separate from the statement text.

<?php
$db = null;

try {
    $db = new SQLite3('/path/to/private/app.sqlite');
    $db->enableExceptions(true);

    $db->exec(
        'CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            username TEXT NOT NULL,
            email TEXT NOT NULL
        )'
    );

    $insert = $db->prepare(
        'INSERT INTO users (username, email) VALUES (:username, :email)'
    );
    if ($insert === false) {
        throw new RuntimeException($db->lastErrorMsg());
    }
    $insert->bindValue(':username', 'john_doe', SQLITE3_TEXT);
    $insert->bindValue(':email', '[email protected]', SQLITE3_TEXT);
    $insertResult = $insert->execute();
    if ($insertResult === false) {
        throw new RuntimeException($db->lastErrorMsg());
    }
    $insertResult->finalize();
    $insert->close();

    $result = $db->query(
        'SELECT id, username, email FROM users ORDER BY id'
    );
    if ($result === false) {
        throw new RuntimeException($db->lastErrorMsg());
    }
    try {
        while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
            echo (int) $row['id'] . ': '
                . htmlspecialchars((string) $row['username'], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8')
                . "<br>\n";
        }
    } finally {
        $result->finalize();
    }
} catch (Throwable $e) {
    error_log($e->getMessage());
    http_response_code(500);
    echo 'The database operation failed.';
} finally {
    if ($db instanceof SQLite3) {
        $db->close();
    }
}

The table setup is safe to repeat, but the sample INSERT intentionally creates one row each time it runs. In an application, run schema setup through a migration or initialization step and execute the insert only when the application creates a user—not on every page request.

Replace /path/to/private/app.sqlite with an absolute path whose parent directory exists and is writable by PHP. The constructor’s default flags are SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, so it creates the file if missing. SQLite3::enableExceptions(true) makes SQLite3, statement, and result errors throw exceptions; log details on the server and show visitors a generic message. See PHP’s SQLite3::enableExceptions() documentation.

SQLite3::prepare() returns a statement, and SQLite3Stmt::bindValue() binds each value to its placeholder. This avoids building SQL by concatenating user-supplied data. See the PHP SQLite3::prepare(), SQLite3Stmt::bindValue(), and SQL injection guidance.

Update or delete rows

Use the same prepared-statement pattern for changing data. Place statements like this inside the open-connection try block from the previous example, before its finally block closes $db:

<?php
$stmt = $db->prepare('UPDATE users SET email = :email WHERE id = :id');
$stmt->bindValue(':email', '[email protected]', SQLITE3_TEXT);
$stmt->bindValue(':id', 1, SQLITE3_INTEGER);
$stmt->execute()->finalize();
$stmt->close();

Keep a WHERE condition on updates and deletes unless you intend to affect every row. For a fuller create, read, update, and delete walkthrough using PDO, see SQLite CRUD tutorials in PHP. For SQL syntax and function details, browse the SQLite reference.