SQLite Forum

open db in read only; explicitly store diff as WAL ?

open db in read only; explicitly store diff as WAL ?

(1) By anonymous on 2021-04-05 18:45:53 [link] [source]

Is it possible to use sqlite in the following way:

  1. we open "old.db' in READ ONLY mode

  2. we do some changes, these changes are stored to "patch.wal"

At the end of the session:

  1. old.db is unchanged

  2. if we run "sqlite-patch old.db patch.wal", we get new.db

The goal here is that I want the old db to remain unchanged and have my changes be explicitly stored as a patch.wal .

(2) By Larry Brasfield (larrybr) on 2021-04-05 18:53:52 in reply to 1 [link] [source]

It's not exactly what you specify, but the sqldiff utility does something quite close.

(3) By anonymous on 2021-04-05 19:03:41 in reply to 2 [link] [source]

Interesting, so I think this is:

  1. we start with old.db in read only mode

  2. we make some changes, saving to new.db

  3. sqldiff.db old.db new.db ==> calculates a patch

So the only difference from the original question is that instead of "explicitly storing the WAL", we store new.db and then compute the diff later.


(4) By Keith Medcalf (kmedcalf) on 2021-04-05 22:38:01 in reply to 1 [link] [source]

I don't think that you can "write" to a "read-only" database. If you could that would almost certainly be a "bug".

You probably do not want to open the read-only database at all, but rather modify a copy of it, leaving the original intact.

(5) By anonymous on 2021-04-06 00:24:29 in reply to 4 [link] [source]

We don't "write" to a "read-only" database. We write to the writable write-ahead-log file.

This is like purely functional data structures (in particular purely functional btrees). You can build a new structure, leave the old one unmodified, and share nodes for common data.

Except in this case, we are building the new structure in a separate file, in the WAL.

(6) By Keith Medcalf (kmedcalf) on 2021-04-06 03:53:48 in reply to 5 [link] [source]

Clearly you do not understand what read-only means.

(7) By anonymous on 2021-04-06 07:26:01 in reply to 6 [link] [source]

"read only" here means no in-place updates.


  • http://www.bzero.se/ldapd/btree.html -- we can build a new btree without destructively modifying any of the existing nodes
  • https://en.wikipedia.org/wiki/Lightning_Memory-Mapped_Database (a kv store built on similar ideas)
  • https://github.com/LMDB/sqlightning -- port of sqlite to use lmdb
  • https://emilypost.com/advice/everyday-etiquette -- in case English is not your 1st language, as your above comment might come off as rude

(8) By anonymous on 2021-04-06 07:32:35 in reply to 6 [link] [source]

The original question talks about opening the file "old.db" in read-only mode. For whatever reason, you seem to assume this means treating the database as read-only (when in actuality, it is possible to just write the changes to an external 'diff' file.)

(10) By Keith Medcalf (kmedcalf) on 2021-04-07 00:22:52 in reply to 8 [link] [source]

No it is not. Unless you are not using SQLite3. If the database is opened "read-only" you cannot modify it. If you believe that this is somehow possible, then please explain how you reproduce this bug so that it can be fixed.

(9) By ddevienne on 2021-04-06 08:50:35 in reply to 1 [link] [source]

What you describe is exactly what SQLite's WAL mode does, if one does not checkpoint (which is active by default).

What's missing is the ability to use that .wal file independently, and apply it manually to the DB,
although someone managed to do that w/o patching SQLite (AFAIK), by careful use of the official SQLite3 API.

The SQLdiff utility is static. You'd need to keep an unchanged copy of your old.db, and diff with the resulting new.db,
to obtain SQL text for logical changes between the two. While the .wal file records physical changes to pages.

In any case, your use-case is not quite mainstream, and not directly supported by SQLite I'm afraid.

(11.1) By Keith Medcalf (kmedcalf) on 2021-04-07 00:30:56 edited from 11.0 in reply to 9 [link] [source]

This is incorrect. If you open a database in read-only mode you cannot modify it. It does not matter what journal mode is in effect. If you attempt to "open" a write transaction against a read-only database it will fail with the message that you cannot modify a read-only database.

SQLite version 3.36.0 2021-04-05 23:06:42
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open cases.db
sqlite> pragma journal_mode=wal;
│ journal_mode │
│ wal          │
sqlite> .open -readonly cases.db
sqlite> pragma journal_mode;
│ journal_mode │
│ wal          │
sqlite> .databases
main: D:\work\covid\cases.db r/o
sqlite> begin immediate;
sqlite> .databases
main: D:\work\covid\cases.db r/o read-txn
sqlite> create table main.x(x);
Error: attempt to write a readonly database

You cannot write to a read-only database.

(12) By anonymous on 2021-04-07 00:40:37 in reply to 11.1 [link] [source]


I asked the original question.

I apologize for the confusion caused by my use of the phrase "read-only".

The question I meant to ask is:

Is it possible[1] to:

  1. open the file old.db with "read-only" tag, as in https://www.cplusplus.com/reference/cstdio/fopen/ with "r"

  2. have changes to the db be written out to the writable WAL, with "old.db" unmodified

I hope this clears things up.

[1] I am a programmer myself, playing with trivial implementations of Relational DB's, so here "possible" includes "modifying the source code"

(14) By Keith Medcalf (kmedcalf) on 2021-04-07 04:51:57 in reply to 12 [link] [source]

Well, you cannot do that (not to mention that there is no point, really).

You can make a copy of the old.db to another file, say new.db, then update that, then generate a diff (either a binary diff in the usual fashion, on an SQL commands diff) between the two files. Applying the diff to the old.db will produce the new.db. (The SQL diff is merely the SQL commands that need to be applied to the old.db to get the new.db -- which you already have since you issued those commands).

Though I do have to wonder why you would want to go to all the bother?

You already know all the commands that you applied to the copy of old.db to get to the new.db, and that is what the diff will output (an SQL diff). On the other hand, a binary diff will only work to transform the exact old.db (not one that is merely similar) into exactly the new.db (which you already have).

Given this, I kind of wonder what the point would be?

You can also take a look at the session extension (https://sqlite.org/sessionintro.html) which will allow you to "record" the updates into a changeset while you make them for the purpose of applying them to a "similar database".

(15) By Keith Medcalf (kmedcalf) on 2021-04-07 04:57:58 in reply to 14 [link] [source]

Though you will note that the session extension records data changes, not schema changes.

(13) By anonymous on 2021-04-07 00:41:45 in reply to 9 [link] [source]

@ddevienne: Thanks for the litestream link. This looks like the same author as boltdb, which I'm a fan of. Thanks for sharing!

(16) By Scott Robison (casaderobison) on 2021-04-07 16:08:51 in reply to 1 [source]

I think it has been determined that SQLite doesn't do what you're asking for. One possibility that comes to mind would be to write your own VFS. In this VFS it would treat the source database as read only and would "magically" write all changes to a delta file of some sort. A separate utility could be used to merge the delta file back into the original database.