SQLite con PDO en PHP: tablas, consultas preparadas y transacciones

SQLite con PDO en PHP: PDO es la forma estándar de hablar con bases de datos en PHP, y SQLite la base más sencilla, porque cabe en un archivo y viene incluida. Lo que aprendas aquí sirve igual para MySQL o PostgreSQL cambiando una línea.

Conectar y crear la tabla

<?php
declare(strict_types=1);

$ruta = sys_get_temp_dir() . '/tienda.sqlite';
@unlink($ruta);

$pdo = new PDO('sqlite:' . $ruta, options: [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$pdo->exec('CREATE TABLE productos (
    id INTEGER PRIMARY KEY,
    nombre TEXT NOT NULL UNIQUE,
    precio_centimos INTEGER NOT NULL CHECK (precio_centimos >= 0),
    stock INTEGER NOT NULL DEFAULT 0
)');

echo "Base creada: ", basename($ruta), "\n";
echo "Versión de SQLite: ", $pdo->query('SELECT sqlite_version()')->fetchColumn(), "\n";
echo "Errores como excepciones: ", var_export($pdo->getAttribute(PDO::ATTR_ERRMODE) === PDO::ERRMODE_EXCEPTION, true), "\n";

Probar en el editor →

salidaBase creada: tienda.sqlite
Versión de SQLite: 3.46.1
Errores como excepciones: true

SQLite guarda la base entera en un solo archivo y viene incluida en PHP: no hay servidor que instalar. Con sqlite::memory: la base vive solo en memoria, perfecta para pruebas. Desde PHP 8, PDO lanza excepciones cuando algo falla, sin tener que configurarlo. FETCH_ASSOC hace que cada fila llegue como un array con los nombres de las columnas.

Insertar con consultas preparadas

<?php
declare(strict_types=1);

$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec('CREATE TABLE productos (id INTEGER PRIMARY KEY, nombre TEXT NOT NULL UNIQUE, precio_centimos INTEGER NOT NULL, stock INTEGER NOT NULL DEFAULT 0)');

$alta = $pdo->prepare('INSERT INTO productos (nombre, precio_centimos, stock) VALUES (:nombre, :precio, :stock)');

$productos = [['Café de Veracruz', 1250, 40], ['Taza de barro', 800, 15], ["Té de \"jazmín\" y O'Brien", 950, 0]];
foreach ($productos as [$nombre, $precio, $stock]) {
    $alta->execute(['nombre' => $nombre, 'precio' => $precio, 'stock' => $stock]);
    echo "Insertado con id ", $pdo->lastInsertId(), "\n";
}

try {
    $alta->execute(['nombre' => 'Taza de barro', 'precio' => 900, 'stock' => 1]);
} catch (PDOException $e) {
    echo "No se pudo: ", $e->getMessage(), "\n";
}

Probar en el editor →

salidaInsertado con id 1
Insertado con id 2
Insertado con id 3
No se pudo: SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: productos.nombre

La consulta se prepara una vez con marcadores (:nombre) y se ejecuta muchas con datos distintos, que viajan aparte del SQL. Por eso el nombre con comillas dobles y un apóstrofo no rompe nada, y por eso las consultas preparadas protegen de la inyección SQL. La restricción UNIQUE rechaza el producto repetido con una PDOException.

Consultar

<?php
declare(strict_types=1);

$pdo = new PDO('sqlite::memory:', options: [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
$pdo->exec('CREATE TABLE productos (id INTEGER PRIMARY KEY, nombre TEXT, precio_centimos INTEGER, stock INTEGER)');
$pdo->exec("INSERT INTO productos (nombre, precio_centimos, stock) VALUES ('Café', 1250, 40), ('Taza', 800, 15), ('Té', 950, 0), ('Molinillo', 3400, 3)");

$consulta = $pdo->prepare('SELECT nombre, precio_centimos FROM productos WHERE stock > :minimo ORDER BY precio_centimos DESC');
$consulta->execute(['minimo' => 0]);
foreach ($consulta as $fila) {
    echo mb_str_pad($fila['nombre'], 10), str_pad(number_format($fila['precio_centimos'] / 100, 2), 8, ' ', STR_PAD_LEFT), "\n";
}

$uno = $pdo->prepare('SELECT * FROM productos WHERE id = ?');
$uno->execute([2]);
print_r($uno->fetch());

$uno->execute([99]);
var_dump($uno->fetch());

$nombres = $pdo->query('SELECT nombre FROM productos ORDER BY nombre')->fetchAll(PDO::FETCH_COLUMN);
echo implode(', ', $nombres), "\n";
echo "Valor del inventario: ", $pdo->query('SELECT SUM(precio_centimos * stock) FROM productos')->fetchColumn() / 100, "\n";

Probar en el editor →

salidaMolinillo    34.00
Café         12.50
Taza          8.00
Array
(
    [id] => 2
    [nombre] => Taza
    [precio_centimos] => 800
    [stock] => 15
)
bool(false)
Café, Molinillo, Taza, Té
Valor del inventario: 722

Una consulta ejecutada se puede recorrer con foreach. fetch() devuelve una fila o false si no hay más (compruébalo con === false), fetchAll() todas a la vez, FETCH_COLUMN solo la primera columna y fetchColumn() un único valor. Los marcadores pueden ser con nombre o con ?, pero no mezclados en la misma consulta.

Actualizar y transacciones

<?php
declare(strict_types=1);

$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE cuentas (titular TEXT PRIMARY KEY, saldo INTEGER NOT NULL CHECK (saldo >= 0))');
$pdo->exec("INSERT INTO cuentas VALUES ('ana', 10000), ('luis', 2000)");

function transferir(PDO $pdo, string $de, string $a, int $importe): void
{
    $pdo->beginTransaction();
    try {
        $pdo->prepare('UPDATE cuentas SET saldo = saldo + ? WHERE titular = ?')->execute([$importe, $a]);
        $pdo->prepare('UPDATE cuentas SET saldo = saldo - ? WHERE titular = ?')->execute([$importe, $de]);
        $pdo->commit();
    } catch (PDOException $e) {
        $pdo->rollBack();
        throw $e;
    }
}

transferir($pdo, 'ana', 'luis', 3000);
try {
    transferir($pdo, 'luis', 'ana', 99000);
} catch (PDOException $e) {
    echo "Transferencia anulada: ", $e->getMessage(), "\n";
}

foreach ($pdo->query('SELECT titular, saldo FROM cuentas') as [$titular, $saldo]) {
    echo "$titular: $saldo\n";
}

$cambio = $pdo->prepare('UPDATE cuentas SET saldo = saldo + 1 WHERE saldo > ?');
$cambio->execute([4000]);
echo $cambio->rowCount(), " filas actualizadas\n";

Probar en el editor →

salidaTransferencia anulada: SQLSTATE[23000]: Integrity constraint violation: 19 CHECK constraint failed: saldo >= 0
ana: 7000
luis: 5000
2 filas actualizadas

La segunda transferencia suma primero a Ana y luego falla al restar a Luis, porque su saldo quedaría negativo. Sin transacción, Ana habría recibido un dinero que no salió de ninguna parte; con ella, rollBack deshace también la primera mitad. rowCount() dice cuántas filas cambió un UPDATE o un DELETE.

Resumen

Todo el código de esta página se ejecutó con PHP 8.5.12 en un contenedor limpio, sin acceso a la red, antes de publicarla; las salidas y los mensajes de error están copiados de esa ejecución.