Use SQLite with PHP

I'm writing these mostly as a shorthand for myself when I need to cobble together some simple PHP thing with a database (and also to get better with writing out SQL by hand). Most of what's here is taken almost verbatim from a much more comprehensive gist.

Please ignore the fact that these todos are neither completable nor removable.


The first argument here is the name of the database you're connecting to. The second argument should be pretty clear: if the database doesn't exist, create it; and if it does exist, open it for reading and writing.

$db = new SQLite3('db_name.sqlite', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);

Run a query

$db->query('CREATE TABLE IF NOT EXISTS "todos" (
 "name" TEXT

If you're doing multiple queries at once

If you're going to be running more than one query at a time, wrap them all in a 'transaction' (BEGIN and COMMIT). SQLite wraps each query individually by default. Breaking each query out into individual transactions seems to be a resource-intensive procedure, so wrapping multiple queries is a cheap way to boost performance.

$db->query('INSERT INTO "todos" ("name")
 VALUES ("Write the blog post about PHP and SQLite"');
$db->query('INSERT INTO "todos" ("name")
 VALUES ("Insert a second todo just for the purposes of clarification"');

Query a row -> associative array

You can 'prepare' a query, which helps SQLite plan out how to actually run the query, for performance reasons. For simple INSERTs and SELECTs, you probably won't see much of a difference in performance, but prepared queries with JOINs, indexes, or lots of iterations will see a marked improvement over unprepared $db->query calls.


$result = $db->query('SELECT * FROM "todos" WHERE "id" = "1");

Can be rewritten as:

$statement = $db->prepare('SELECT * FROM "todos" WHERE "id" = ?');
$statement->bindValue(1, 1);
$result = $statement->execute();

Either way, you have to get the todo itself by running fetchArray() on the SQLite3Result object:

$todo = result->fetchArray(SQLITE_ASSOC);

Query for all rows

$statement = $db->prepare('SELECT * FROM "todos"');
$result = $statement->execute();

// alternately:
// $result = $db->query('SELECT * FROM "todos"');

while($row = $result->fetchArray(SQLITE_ASSOC)) {
 $todos[] = $row

Query for a single value

$statement = $db->prepare('SELECT name FROM "todos" WHERE "id" = ?');
$statement->bindValue(1, 1);
$result = $statement->execute();
$name = $result->fetch();

Or the shorthand:

$name = $db->querySingle('SELECT name FROM "todos" WHERE "id" = "1"');

Insert new data from, say, a form

if(!empty($_POST)) {
 $db->query('INSERT INTO "todos" ("name") VALUES ("' . $_POST['name'] . '")');

Free the memory when you're done querying


Consider closing the database connection when you're through with it

That being said, SQLite will do this when the script ends anyway.




Build a realtime database

How to build a proof of concept for a realtime database, a la Firebase.


Deep Work