SQLite

Almost every program needs to remember things: your chat messages, your bookmarks, your appointments. This kind of information can be stored in a table, with columns like “name”, “date”, or “text”. A collection of such tables is called a database. SQLite is a small, free software package that manages exactly these tables and answers questions about them, such as “show me all appointments from May”. The difference from many other solutions: SQLite doesn’t run on its own server on the internet, but directly inside the app, storing everything in a single file on the device. It was developed starting in the year 2000 by the US programmer Richard Hipp, and anyone is free to use it.

Probably the most installed database in the world

SQLite is so widespread because it makes almost no demands. At its core, it consists of a single program file just a few hundred kilobytes in size. Nobody needs to set up a server, set a password, or establish a network connection. A developer simply drops the file alongside their program, and that’s it.

The result: SQLite is embedded in Android and iOS, in Chrome and Firefox, in Windows, in cars, in in-flight entertainment systems, and in countless apps. The developers themselves estimate that there are more than a trillion active copies. Most users never notice, because SQLite always works in the background.

Reliability is also crucial. The code is checked with millions of automated tests, because a single bug could otherwise destroy data on billions of devices. What’s more, the file format has not fundamentally changed since 2004. The developers promise that today’s files will still be readable in the year 2050. That’s why even the US National Library recommends SQLite as a format for long-term archiving.

One file, many tables, the language SQL

An SQLite database is exactly one file, often with the extension .db or .sqlite. It contains all the tables, all the rows, and additional directories called indexes, which speed up searching. An index works like the keyword index at the back of a book: instead of reading through everything, the program jumps directly to the right spot. That’s why SQLite can find a specific row in milliseconds, even in tables with millions of rows.

Questions are asked in SQL, a query language that has been in use since the 1970s. A command might look like this: SELECT title FROM songs WHERE year = 2024. Translated, that means: give me the titles of all songs from the year 2024. SQLite reads this statement, plans the fastest route through the tables, and returns the result.

One special feature is transactions. With these, SQLite bundles several changes into a single package that either applies completely or is discarded completely. If the battery dies in the middle of saving, the file is still left in a clean state afterward. The limit lies in simultaneous writing: multiple users may read, but only one may write at any given time. For an app on a single device, this is more than enough, but not for a large website with thousands of simultaneous orders. For that, one uses databases like PostgreSQL or MySQL, which run on their own server.

Where you encounter the little .db file

On your phone, SQLite is constantly active. Your WhatsApp messages sit in an SQLite file, as does your browser history and your password list in Chrome or Firefox. Anyone who has ever seen files with the extension .db in a phone backup has been looking at such databases. Police and IT forensic experts also read out exactly these files when examining a device.

In the AI world, too, SQLite is popular. Small tools that log texts, results, or user queries like to use it, because it needs no server. And in weather-data, statistics, or research projects, SQLite is used to pass along large tables without requiring the recipient to install extra software.

By the way, a common misconception is that SQLite is merely a stripped-down toy version. “Lite” refers to the low effort required to set it up, not to weak performance. SQLite can manage files of several hundred gigabytes and is often faster at reading than a server, because there is no network path in between.