I have a process that has only read only access to the database filesystem. It works fine and able to make select queries with a concurrent writer running on the same machine, but when the writer closes the database and delete those files a reader fails to continue. Correct me if I wrong, is this way of operation normal? If so, is it possible to make those files permanent so the reader will continue to read database even when writer is closed?

I am using 3.34.1. It is a python binding, I am opening it in a read only mode "sqlite3.connect('file:...?mode=ro', check_same_thread=False, uri=True)" and it gives me "sqlite3.OperationalError: unable to open database file" when no such files present in the directory. This confirms with a manual or it was not updated yet

Does it work like you want if you leave off the "mode=ro" option?

No it doesn't help

WAL only, by the way. Does not apply to write-behind logging.

Are you thinking an immutable database (no one is updating the database while it is open, ever) and read-only (which does require tracking transaction state because the database file may be updated by another connection)?

That would be immutable=1, not mode=ro. I can see that a database should be able to be opened and read when immutable=1, but mode=ro would require the wal/shm to exist and would fail if they cannot be created and do not exist.

It is possible to tell SQLite to not delete the WAL file, but to prevent other programs (such as the SQLite command shell) also doing so, you can set the sticky bit for the directory containing it. (That is what I did with sqlnetnews.)

The SQLITE_FCNTL_PERSIST_WAL option supports that. But it is not sticky and has to be done for each writer.

The sticky bit that I mean is not a part of SQLite; it is the operating system (I am using Linux). If you set the owner to others than the users accessing it, then the file will not be deleted automatically, due to not having the permission to delete the file.

Why do you thing it should be done on SQLite level that can't predict upfront which underlying file system will be in use (even on Linux, it can be exFAT, FAT or even NTFS where no such things as sticky bit). IMO such protection should be offloaded out of SQLite responsibility

and reader. Basically, every connection. Unless the database is immutable.

While that might work, why would you not set the same filectrl in the CLI?

SQLite version 3.38.0 2022-01-08 23:33:02
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .filectrl
Available file-controls:
  .filectrl chunk_size SIZE
  .filectrl data_version
  .filectrl has_moved
  .filectrl lock_timeout MILLISEC
  .filectrl persist_wal [BOOLEAN]
  .filectrl psow [BOOLEAN]
  .filectrl reserve_bytes [N]
  .filectrl size_limit [LIMIT]
  .filectrl tempfilename

Mainly to avoid having to do so every time.

I thought to make directory not writable to the writer on a OS level but is it safe? Wouldn't it break things or slow down sqlite?