Advent 2021: SQLite

For me it’s surprising how often I can make use of a “small” database. I write web applications, and setting up a database is usually no fun, especially when I have at most one concurrent user (me) and a few MiB of data to handle. There’s really no need at that stage to set up a separate database server, and when it comes to in-process SQL databases, there is really only one solution: SQLite.

There is a lot to like about SQLite – for me, there are three main reasons why I use it. The first one is the ease of integration. It’s standard SQL, so no need to learn some weird dialect, and it’s a single header/source file which is trivial to integrate into build systems. That’s for C applications – and for many (all?) other languages, there are typically bindings available. Some languages like Python ship with SQLite as part of the language runtime itself. At runtime you point it at a file and that’s it.

The second reason I like SQLite is stability. It’s a battle tested project – the developers claim that there are more than one trillion SQLite databases active. The file format is incredibly resilient. That said, I guess I did get lucky and I found one bug in SQLite in my career, but that was a rather obscure corner case in a newly added feature. In practice I never had an issue with SQLite not working as expected or corrupting data.

The third reason I like SQLite is because it’s fast. Like, seriously fast. If you’re one user accessing the database, it’s often faster to use SQLite than rolling your own map join or similar operation on native data structures like lists and dictionaries. I had one project where I was serializing data to disk myself and then trying to match rows in the data set using hash maps and whatnot, and converting it to SQLite resulted in less code and faster operation as the indices and join calculations in SQLite are actually quite sophisticated.

Even for this blog post, which is written using my static page generator, SQLite was involved, as the on-disk cache uses SQLite to avoid creating/deleting tons of files. Another project of mine where you can see SQLite in action is my GPU database which uses SQLite as the backend to simplify the deployment. It’s a fantastic piece of software and a great example of the kind of project we sometimes forget to value properly!