To wit:

[msa@dandy tmp]$ rm /tmp/test.db*
rm: /tmp/test.db*: No such file or directory
[msa@dandy tmp]$ /usr/bin/sqlite3 /tmp/test.db
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode = WAL;
wal
sqlite> CREATE TABLE t(a);
sqlite> .quit
[msa@dandy tmp]$ ls /tmp/test.db*
/tmp/test.db		/tmp/test.db-shm	/tmp/test.db-wal
[msa@dandy tmp]$ 

A simple build of the SQLite 3.28.0 amalgamation (cc *.c *.h) deletes the wal and shm files when it exits. Any idea what's going wrong here? I'm running macOS Catalina, but I've seen this behavior on previous versions as well.

When the last connection to a WAL-mode database disconnects, SQLite normally tries to checkpoint the database and delete the -wal and -shm files. However, this can be disabled by enabling the SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE flag.

The version of SQLite shipped on MacOS and iOS contains a lot of custom modifications. Perhaps this flag has been turned on by default for the version of SQLite that Apple is shipping.

Try this: On your built-in "sqlite3", run the command:

.dbconfig

What is the value of the "no_ckpt_on_close" flags?

This is the whole shebang:

[msa@dandy tmp]$ /usr/bin/sqlite3 
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .dbconfig
       enable_fkey off
    enable_trigger on
    fts3_tokenizer off
    load_extension off
  no_ckpt_on_close off
       enable_qpsg off
       trigger_eqp off
    reset_database off
         defensive off
sqlite> 

One additional pointer I can offer is that the order in which the open database handles are closed seems to matter. If they are closed in the reverse order in which they were opened (like popping a stack), then SQLite deletes the files. If they are closed in the same order in which they were opened, then SQLite does not delete the wal and shm files.

See https://github.com/groue/GRDB.swift/issues/739 for more context.

I can't think of an explanation for that that isn't a bug. But the bug might be in the OS or the language shim, not SQLite.

It's interesting that you obtained the result using your own Swift code, not the CLI which comes with macOS. Can you post the results of these, as called from your Swift code ?

SELECT sqlite_version();
SELECT sqlite_source_id();

This may be an OS build issue, but it's not a language binding issue. I showed how the problem can be reproduced using the sqlite3 CLI in my first post. ;)

I reproduced your result in 3.28.0. But the problem didn't occur with the current version 3.31.1. Please download the current version of 'Precompiled Binaries' for macOS and try again.

My experience is likewise. Version 3.31.1 behaves as expected. The only outlier I've found is the built-in version from macOS. It would be helpful to nail down the issue there, because I'm using SQLite from a macOS app I'm developing. That uses the built-in version.