Automatic versioned backups of Sqlite in a Docker Compose container

For relatively lightweight applications that are either in development, or single user, or have limited requirements for concurrency and massive data sets, SQLite is a superb, full-function, but compact, almost ubiquitous database (it's used on every mobile device, for example).

What's more, it's also Free and Open Source Software (FOSS) - its code has been dedicated to the public domain - making it an ideal tool for a principled open organisation like the OER Foundation to incorporate in its fully Free and Open Source Software stack.

We use SQLite for quite a few things here at the OER Foundation. For example, this implementation of the backup script was developed for the very impressive fully FOSS version of the password manager BitWarden we use to manage our secrets at the OER Foundation. Because the data it holds, although very compact, is very precious, we want to back it up right, and ensure we can go back in time and recreate the data from the past (like, if we realised at some point that in the past we'd deleted the wrong secrets and needed to recover them! It hasn't happened yet, but it's inevitable it will happen at some point). That's why we've created this backup solution that creates dated dumps of an SQLite database that it keeps to ensure that relevant past versions of the data can be recovered, but without storing more data than necessary (a full hard disk is never fun).

Those among you who know about SQLite might rightly point out that an SQLite database (unlike more powerful databases) is simply a single file that can be copied to make a backup. In this case, we're using SQLite's ".backup" command to create the database backup, which we see as a mechanism for future-proofing - at some future time, it might be that the option to copy the file without corrupting the database may no longer be available, but we'd expect that the .backup command would continue to work.

This backup process involves 3 files: the backup BASH script itself, which has all the smarts, the .conf file which defines the file paths and other details specific to your installation - like where the SQLite database actually is, and where to put the backup files - and a cron file which runs the script automatically when you tell it to run on your server.

We've got the whole thing in our Gitlab instance, which is the best place to learn more about how to use it - see the README.md for installation instructions!