SQLite CRUD in PHP with PDO: Prepared Statements

Use PDO_SQLITE to perform create, read, update, and delete (CRUD) operations against a SQLite database file. Bind values with prepared statements rather than inserting input directly into SQL. See PHP’s PDO_SQLITE documentation and prepared-statement guidance.

Check the driver and choose a database path

This guide requires the pdo_sqlite driver. The extension may be disabled in a PHP installation; check the same runtime used by the application with php -m or extension_loaded('pdo_sqlite').

Use an absolute path to the SQLite database file. The parent directory must already exist and be writable by the PHP process. For a web app, store the file outside the public document root so it cannot be downloaded directly. The PDO_SQLITE DSN starts with sqlite: and accepts the path after it; see the PHP PDO_SQLITE DSN reference.

Connect and create a table

Set PDO’s error mode to exceptions so connection and SQL errors can be handled consistently:

<?php
$databaseFile = '/path/to/private/app.sqlite';

try {
    $pdo = new PDO('sqlite:' . $databaseFile);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit('Database connection failed.');
}

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

CREATE TABLE IF NOT EXISTS does not verify or update an existing table’s schema; use migrations to change a table definition. A database opened at a file path is persistent across requests. For a temporary in-memory database, use the PDO_SQLITE DSN sqlite::memory: instead.

Create: insert a row

Use named placeholders for values. Execute this statement only when the application creates a user, not on every page load:

$insert = $pdo->prepare(
    'INSERT INTO users (username, email) VALUES (:username, :email)'
);
$insert->bindValue(':username', 'john_doe', PDO::PARAM_STR);
$insert->bindValue(':email', '[email protected]', PDO::PARAM_STR);
$insert->execute();

When values come from a form or another request, pass them to bindValue() instead of concatenating them into the SQL string. Placeholders bind data values, not table names or SQL keywords; validate dynamic identifiers against an allowlist.

Read: select rows

Prepare a query with a filter value and fetch the result as an associative array:

$select = $pdo->prepare(
    'SELECT id, username, email FROM users WHERE id = :id'
);
$select->bindValue(':id', 1, PDO::PARAM_INT);
$select->execute();
$user = $select->fetch(PDO::FETCH_ASSOC);

$user is false when no row matches. For a list, iterate over the statement:

$users = $pdo->query('SELECT id, username, email FROM users ORDER BY id');
foreach ($users as $user) {
    echo (int) $user['id'] . ': '
        . htmlspecialchars($user['username'], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8')
        . "<br>\n";
}

Escape database text with htmlspecialchars() before placing it in HTML. SQL parameter binding prevents SQL injection; HTML escaping addresses a different risk.

Update: change an existing row

Bind the new value and the row identifier separately:

$update = $pdo->prepare(
    'UPDATE users SET email = :email WHERE id = :id'
);
$update->bindValue(':email', '[email protected]', PDO::PARAM_STR);
$update->bindValue(':id', 1, PDO::PARAM_INT);
$update->execute();

Keep the WHERE condition when only one row should change. Check $update->rowCount() if the application needs to know how many rows were affected.

Delete: remove a row

Bind the identifier in the delete statement as well:

$delete = $pdo->prepare('DELETE FROM users WHERE id = :id');
$delete->bindValue(':id', 1, PDO::PARAM_INT);
try {
    $delete->execute();
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    echo 'The database operation failed.';
}

The same handling applies to inserts, reads, and updates. Use transactions when several related writes must succeed or fail together. Keep exception details in server logs; return a generic error rather than exposing file paths or SQL text.

For a quick start using PHP’s SQLite3 class instead of PDO, see Basic Usage of SQLite in a PHP Application. For SQLite-specific SQL syntax, browse the SQLite reference.