Most embedded Linux projects do not need a database server. They need something that stores data reliably when the network is down, survives a power cut, does not eat RAM, and requires zero administration. SQLite does all of that. It is a single .db file on disk, it has been in production use for over twenty years, and it is already installed on virtually every Linux system you will ever deploy to. If you are currently writing sensor data to a flat file or a custom binary format, this article will show you why SQLite is almost always the better choice — and exactly how to use it correctly.
See the project on GitHub: Resilient Edge MQTT client
Enable WAL Mode Before Anything Else
The single most impactful thing you can do is switch from the default journal mode to Write-Ahead Logging. In the default mode, SQLite locks the entire database file during a write, which means a reader has to wait. WAL mode allows reads and writes to happen simultaneously, which matters enormously when you have one thread logging sensor data and another thread reading it for transmission. It also makes crash recovery safer — incomplete writes never corrupt the database.
import sqlite3
