←back to thread

Zero-latency SQLite storage in every Durable Object

(simonwillison.net)
285 points ajhit406 | 3 comments | 13 Oct 24 23:07 UTC | HN request time: 0.431s | source
Show context
emadda ◴[14 Oct 24 00:40 UTC] No.41833065[source]▶
Some other interesting points:

- The write api is sync, but it has a hidden async await: when you do your next output with a response, if the write fails the runtime will replace the response with a http failure. This allows the runtime to auto-batch writes and optimistically assume they will succeed, without the user explicitly handling the errors or awaits.

- There are no read transactions, which would be useful to get a pointer to a snapshot at a point in time.

- Each runtime instance is limited to 128mb RAM.

- Websockets can hibernate and you do not have to pay for the time they are sleeping. This allows your clients to remain connected even when the DO is sleeping.

- They have a kind of auto RPC ability where you can talk to other DOs or workers as if they are normal JS calls, but they can actually be calling another data center. The runtime handles the serialisation and parsing.

replies(4): >>41833314 #>>41834000 #>>41834959 #>>41835462 #
matharmin ◴[14 Oct 24 07:03 UTC] No.41834959[source]▶
Just wondering, do you have a specific use case for read transactions implemented on the database level here?

In SQLite in general read transactions are useful since you can access the same database from multiple processes at a time. Here, only a single process can access the database. So you can get the same effect as read transactions either by doing all reads in one synchronous function, or implement your own process-level locking.

replies(1): >>41836156 #
emadda ◴[14 Oct 24 10:33 UTC] No.41836156[source]▶
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.

replies(1): >>41837576 #
1. kentonv ◴[14 Oct 24 13:55 UTC] No.41837576[source]▶
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.

replies(2): >>41840157 #>>41840282 #
2. kentonv ◴[14 Oct 24 18:07 UTC] No.41840157[source]▶
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.
3. emadda ◴[14 Oct 24 18:17 UTC] No.41840282[source]▶
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).