SQLite

What it is

SQLite is a SQL database engine that runs inside the program using it. There is no server to install or connect to. The whole database is one ordinary file on disk, and the library reads and writes it directly. It supports tables, indexes, transactions and most of standard SQL. Because the database is one file, backing it up or copying it is as simple as copying that file.

Because it is small and self-contained, SQLite ships inside phones, browsers, desktop apps and countless devices. Both iOS and Android include it. It is the usual choice when an app needs structured local storage that survives a restart, without the cost of running a separate database service. Its file format is stable and documented, so data written today can still be read years from now.

How I use it

• Rental Pilot's field app keeps its own SQLite queue on the phone for each area of work: deliveries, pickup assignments and image uploads.
• Work done with no signal is saved locally and synced when the network comes back, with network state watched by the app.
• A Sync Queue screen shows the crew exactly what is still waiting to go up.
• Photos are staged on the phone before upload, so a dropped connection does not lose a picture.
• The server side stays in MySQL; SQLite is only the phone's local holding area.

SQLite is the right tool when data has to live on the device itself, close to the user, and still be queried properly. Field crews lose signal on job sites, so I reach for it to hold their work on the phone until the network comes back. It gives me real transactions and queries without asking a phone to run a server.

Questions