WAL mode: *-shm and *-wal files are not properly cleaned up

[Prinzhorn]

This is about as minimal of an example as I could extract from my code base. After terminating the process wal.db-shm and wal.db-wal are still there. The order of the close calls matters.

The WAL file is created when the first connection to the database is opened and is normally removed when the last connection to the database closes. However, if the last connection does not shutdown cleanly, the WAL file will remain in the filesystem and will be automatically cleaned up the next time the database is opened.

https://sqlite.org/tempfiles.html

I assume for readonly: true the connection is not properly cleaned up? That's why closing the readonly connection last does not clean up. The code contains two places that each individually change the behavior.

const Database = require('better-sqlite3');
const write = new Database('./wal.db');
// <-----------------------
// If you remove readonly then the files are properly removed.
// <-----------------------
const read = new Database('./wal.db', { fileMustExist: true, readonly: true });

write.pragma('journal_mode = WAL');
read.pragma('journal_mode = WAL');

write.exec('CREATE TABLE my_favorite_numbers (value TEXT)');
write.exec('INSERT INTO my_favorite_numbers VALUES (3)');

process.on('exit', () => {
  console.log('Exiting');

  // <------------------------------
  // If you switch these two lines then the files are properly removed.
  // <------------------------------
  write.close();
  read.close();
});
process.on('SIGHUP', () => process.exit(128 + 1));
process.on('SIGINT', () => process.exit(128 + 2));
process.on('SIGTERM', () => process.exit(128 + 15));

// Keep running
setTimeout(function() {}, 100000);

[Prinzhorn]

However, if the last client did not call sqlite3_close() before it shut down, or if the last client to disconnect was a read-only client, then the final cleanup operation does not occur and the shm and wal files may still exist on disk even when the database is not in use.

https://sqlite.org/draft/walformat.html

Oof.

I can control the order, so not a big deal.