Store data in SQLite with PDO
Open a SQLite database from PHP with PDO and run CREATE, INSERT and SELECT — on a file on the microSD that survives resets, or an in-memory database that does not. PDO and the SQLite driver are built into the firmware.
SQLite is the natural database for a microcontroller: no server, no network, the whole engine and the data in one file. On php-esp32 you reach it through PDO — ext/pdo and ext/pdo_sqlite are built together from one flag, and the SQLite amalgamation is compiled straight into the firmware. This recipe opens a database, creates a table, writes a row on every boot, and reads it back.
What you need
- A board flashed with php-esp32 (the examples target the ESP32-P4-Pico).
- The
sqliteextension enabled at build time — one flag turns on bothext/pdoandext/pdo_sqlite. Nothing else is required; SQLite has no external dependencies. - A microSD card if you want the database to persist across resets. An in-memory database needs no card.
The standalone SQLite3 class is not built. Use the PDO driver — new PDO('sqlite:...') — for everything. That is the same API the Eloquent example below runs on.
Two locations for the database
A SQLite database is either a file on disk or a scratch database that lives only in RAM. Pick by the DSN you pass to PDO.
sqlite:/sdcard/app.db opens (or creates) a file on the card mounted at /sdcard. The file — and every row in it — survives resets and power cycles. This is what you want for anything you plan to keep.
A writable database file must sit on the microSD (/sdcard/...). If your project uses an embedded source image instead of a card, that image is read-only at runtime — you can ship a pre-populated .db and read from it, but you cannot INSERT into it. To write, either use the microSD or an ::memory: database.
The code
Put this in project-src/index.php. It opens a database on the card, creates a notes table on the first run, records one row per boot, and prints the last few back with a prepared SELECT.
Everything here is stock PDO: a DSN to open the connection, exec() for statements with no result, prepare()/execute() with bound parameters for the write, and query() returning an iterable of rows. Turning on PDO::ERRMODE_EXCEPTION means a SQL error throws instead of failing silently.
The microSD is a FAT filesystem, which has no POSIX file locking. For plain PDO writes this is fine. If a library opens the database its own way and trips over locking or realpath(), open the file yourself with a file: URI and ?nolock=1 — for example new PDO('sqlite:file:/sdcard/app.db?nolock=1') — and hand that PDO to the library. The eloquent-demo example does exactly this.
Config
A minimal microSD project with the SQLite extension enabled. The one [extensions.sqlite] table builds both ext/pdo and ext/pdo_sqlite; phpflash fetches the SQLite amalgamation on demand.
Build & flash
Build the firmware, write it to the board, then open the serial monitor.
With storage_type = "microsd" the PHP source and the database both live on the card. To try a change to index.php, copy it to the card root and press reset — no rebuild needed. The .db file stays where it is, so the rows you already wrote are still there.
What you'll see
The first boot creates the database and writes note #1; every reset after that opens the existing file and appends another row. Because the file is on the card, the count keeps climbing across power cycles.
Going further: Eloquent
Because it is real PDO underneath, an ORM works too. The eloquent-demo example runs Laravel's Eloquent (illuminate/database) standalone, without the Laravel framework: the Capsule manager, a Post model, the schema builder, and the same SQLite file on the card.
Eloquent needs its dependencies and a fuller extension set. Run composer install on your PC and copy the whole vendor/ folder to the card next to index.php (the firmware has FAT long filenames on by default). The build must also enable mbstring (the Str:: helpers), ctype, filter and date (Carbon timestamps) alongside sqlite. See examples/eloquent-demo/README.md for the exact flags and the one-line mb_split polyfill it uses when mbstring is built without oniguruma.