[emadda]

E.g. if you have many websocket connections and they each have a snapshot at a point in time (that spans over many different await function calls/ws messages).

SQLite can have many readers and a single writer with WAL, so a many read transactions can exist whilst the writers move the db state forward.

[kentonv]

We (Cloudflare) have considered adding an API to create multiple "database connections", especially to be able to stream a response from a long-running cursor while representing a consistent snapshot of the data.

It's a bit tricky since if you hold open that old connection, the WAL could grow without bound and cannot be checkpointed back into the main database. What do we do when the WAL gets unreasonably large (e.g. bigger than the database)? Cancel old cursors so we can finally checkpoint? Will that be annoying for app developers to deal with, e.g. causing errors when traffic is high?

SQLite itself calls an open database a "connection" even though there's no actual network involved.

[emadda]

I did guess it might be harder to do than vanilla SQLite, as vanilla SQLite just has the WAL and main db on the same hard drive, so it has more space to grow the WAL and it is not an issue when the machine/instance reboots (as it just starts where it left off, even if the WAL is large and has not been check-pointed back to the main db).

To be honest this is an edge case. But I often start a read transaction on a SQLite connection just so I know multiple queries are reading from the same state (and to ensure state has not been changed between queries).

[kentonv]

Ugh didn't notice until too late to edit, but apparently HN interpreted my asterisk as an instruction to italicize everything between it and the footnote it referred to.