databases storage and caching

Error Handling

Database errors should be visible to developers and operators, but safe for users. A good PHP application uses exceptions internally, rolls back failed transactions, handles expected cases deliberately, and avoids leaking SQL details in public responses.

Error handling is not only about crashes. Missing rows, duplicate emails, invalid foreign keys, deadlocks, timeouts, and connection failures all need different responses.

Enable Exceptions

PDO should be configured to throw exceptions for database errors.

PHP example

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

echo 'PDO exceptions enabled' . PHP_EOL;

// Prints:
// PDO exceptions enabled

Without exception mode, database failures are easier to miss and harder to handle consistently.

Expected Missing Rows

A missing row is usually not an exception. It is an expected outcome that should become null, a 404, or a domain-specific response.

PHP example

<?php

declare(strict_types=1);

function userFromRow(array|false $row): ?array
{
    if ($row === false) {
        return null;
    }

    return $row;
}

var_dump(userFromRow(false));

// Prints:
// NULL

Do not treat every "not found" as a server error.

Constraint Failures

Unique constraints, foreign keys, NOT NULL, and CHECK constraints protect data. When they fail, the application should respond with a useful message where possible.

PHP example

<?php

declare(strict_types=1);

function publicDatabaseErrorMessage(PDOException $exception): string
{
    $sqlState = $exception->errorInfo[0] ?? null;

    return match ($sqlState) {
        '23000', '23505' => 'A record with that value already exists.',
        default => 'The data could not be saved.',
    };
}

$exception = new PDOException('Unique constraint failed');
$exception->errorInfo = ['23000'];

echo publicDatabaseErrorMessage($exception) . PHP_EOL;

// Prints:
// A record with that value already exists.

SQLSTATE codes vary by database and driver. Use them carefully and test against the database your project actually runs.

Transactions And Rollback

If an exception happens inside a transaction, roll back before rethrowing or converting the error.

PHP example

<?php

declare(strict_types=1);

function transactionFailureAction(bool $inTransaction): string
{
    return $inTransaction ? 'roll back then handle error' : 'handle error';
}

echo transactionFailureAction(true) . PHP_EOL;

// Prints:
// roll back then handle error

Leaving a transaction open can hold locks and break later work.

Safe Logging

Logs should help developers diagnose the issue without exposing secrets or private data.

Log useful context such as:

Operation name.
Table or repository area.
SQLSTATE or exception class.
Request ID or job ID.
Non-sensitive identifiers such as user ID or order ID.

Avoid logging:

Passwords, tokens, API keys, and session IDs.
Full payment data.
Raw SQL with sensitive bound values.
Full personal data unless the logging policy allows it.

What To Check

Before moving on, make sure you can:

Enable PDO exception mode.
Distinguish a missing row from a database failure.
Handle unique or constraint failures deliberately.
Roll back transactions on exceptions.
Return safe public messages.
Log enough context without logging secrets.

Practice

Practice: Handle Duplicate Emails Safely

Write a PHP script that inserts users and handles a duplicate email safely.

Requirements

Use SQLite in memory.
Enable PDO exception mode.
Create a users table with a unique email column.
Insert one user successfully.
Attempt to insert the same email again.
Catch the database exception.
Print a safe public message without printing raw SQL or a password.

Show solution

This solution catches the duplicate insert and returns a safe message.

PHP example

<?php

declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

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

$insert = $pdo->prepare('INSERT INTO users (email) VALUES (:email)');
$insert->execute(['email' => 'sam@example.com']);

try {
    $insert->execute(['email' => 'sam@example.com']);
    echo 'User created' . PHP_EOL;
} catch (PDOException $exception) {
    echo 'A user with that email already exists.' . PHP_EOL;
    echo 'log_context=' . json_encode([
        'operation' => 'create_user',
        'sqlstate' => $exception->errorInfo[0] ?? null,
    ], JSON_THROW_ON_ERROR) . PHP_EOL;
}

// Prints:
// A user with that email already exists.
// log_context={"operation":"create_user","sqlstate":"23000"}

The public message is safe. The log context is useful without exposing raw SQL, passwords, or the submitted email address.