Hello, I have several questions about opening db in Read Only mode. Db uses WAL journal mode.

• If/Why opening db in read only mode and querying against this db for read data, has to create WAL file? Documentation describe WAL file as required to support transaction log for write operations. What is the reason of this file existance in RO mode?

• If there is a need to create WAL file in RO Mode, why closing all db connections not removes the WAL file? The same code in RW Mode cleans up everthing nicely.

• Is it possible to point path where WAL file should be created?

But in the Read Only mode we are completely sure that there is no following write transactions so why we need to create markers in WAL file? I think its true unless we open first Write Mode connection.

Anyway what is a preffered solution to open db file from the location with read only access. Copying the file to RW location is of course one of the possibility but Is there anything else?

But in the Read Only mode we are completely sure that there is no following write transactions so why we need to create markers in WAL file?

Because a different client may open the db file in write mode. sqlite can't know that your connection will be the only one.

Do you mean that another connection could be later opened in write mode and it is to late to make markers for aleady existed transactions?

Let me know if I'm right please.

In case when file has read only file attrib set, sqlite opens all connections in read only mode. I think this could be the missing key. I consider some kind of db configuration like for example PRAGMA read_only=true

All my speculatiations are due to fact that in my opinion it looks strange that there is no possibility to open db file directly from read only access location/directory.

Thanks. It was really very helpful.

The previous replies are already correct, but it seems you may be missing some basics, which I will try to fill in here.

When you open an SQLite database in read-only mode, it doesn't change the database into some form of "read-only" database, it merely means that you promise (and can be held to the promise) that you will will not try to make changes to the DB from THIS connection you are making.

It says nothing about any other connection or the DB itself. It's like when you go to a shop and you tell the shopkeeper that you promise to not buy anything while you are inside, and the shopkeeper says Ok, but then leave your wallet/cash/cards outside please, now the shopkeeper knows for a fact you cannot buy anything, but you can look around all you want. This does not mean that the shop will stop selling stuff, it's still open for business to other people. It also does not prevent anyone else from using the shop, they can still use it and some of them might still buy stuff from it.

it looks strange that there is no possibility to open db file directly from read only access location/directory.

There is no way to make an SQLite DB file become "read-only", other than perhaps making the OS / Folder treat it as read-only, but that's like shutting the shop's doors - now nobody can use it. Even if the DB is opened in read-only mode, the connection must still write locks or WAL logs to simply have read-transactions on the DB file.

If you really have a use-case for a read-only DB, then store the DB file in a read-only location, and before using it from a new connection, first copy the DB file to a temp folder (with read-access), OR serialize it into memory then open that temporary/memory DB and read it to your heart's content. Once done, kill the file or the release the memory and all is back as it was before.

Thanks for detailed explanation - I understood why I think differently. It is because sqlite could work in general in much advanced mode in contrast to my very simple use case. In my scenario there is only one process with one write connection and few read connections. I think problem with opening db from RO media could be simple solved by loading whole db to memory or by immutable flag.

Thank you for the very detail and live reply. It just explains everything. WAL is great, as my project does have many customers (some are buyers in the shop). But I suffer from that when I open an RO connection when all codes are running (i.e, shop is running), and I close the RO connection, I PAUSE (in debug mode) my running project,

I've forotten second question.

Is it safe to manually delete this files (wal an shm) in RO mode.

Thank you very much.