Syncing the WAL with SQLite

When you're working with SQLite, it's possible that you're using the Write Ahead Log (WAL) to improve performance of the database.

As part of this, you may notice that your SQLite database goes from a single-file database like dmd.db to a multi-file setup:

% ls -lrt dmd.db*
........................ 577536 Jul 29 11:47 dmd.db
........................  32768 Jul 29 11:51 dmd.db-shm
........................ 646872 Jul 29 11:51 dmd.db-wal

This is absolutely fine, and most tools support this.

I recently found that the Dependency Management Data example project's build on GitLab CI was archiving the database (to then be downloaded) without the other files needed, and so we were seeing partial data.

I didn't want to store all three files, and instead only have the single dmd.db as the source of truth.

Alternatively, sometimes you want to be able to leverage one of the great things about SQLite - being able to have a single file that you can i.e. send someone on Slack.

To do this, we need to perform an SQLite WAL checkpoint, which can be triggered i.e. via

% sqlite3 dmd.db 'PRAGMA wal_checkpoint(FULL);'
(output not relevant)

This then takes your WAL'd database, and will sync the data from the WAL to the database.

Note that - as you may expect - this has performance implications while undergoing the checkpoint, and care should be taken when performing this on a live database.