Each server would have one copy of the SQLite database. Only one of the server would support writes — and those write will be replicated to the other server. Reads in the other server will be transactionally safe, but might be slightly out of date.

I don't think you understand what transactionally safe means. SQLite used in this manner is not a database, it's a cache. Thinking otherwise will give you a bad time when the value you're writing is based on the stale value you read.

This is my main q: are the writes replicated in real-time? Do the apps that just need read access have to repeatedly call 'restore'?

This is what I understand as well. On top of that, you cannot call restore on the same sqlite file, so you have to create a new db each restore and make your reader app aware of the new db file.

Edit: As the blog is saying, it's mainly for single-node application so you should treat the project as a way to constantly backup your sqlite db and being able to recover from disaster.

It says "continuous" but I don't really see how it is. Or, at least, I get that the backin-up is continuous, since litestream is watching the WAL. But in the example there, isn't `restore` called manually to pick up the change?

Is the idea you just kind of "poll" restore? That seems like a lot of extra work, if I'm reading that example correctly. It pulls down the whole database every time? Even a "small" SQLite DB (for the use cases I'm thinking of) can easily be a hundred megabytes. I don't think I'd want to poll that every few seconds.

You have to call a "restore" function, according to their docs. I'm not sure I understand the use case, but perhaps it's just for structured user-only data.

I also investigated SQLite and it's not clear how we can use it with multiple servers.

The WAL documentation [1] says "The wal-index greatly improves the performance of readers, but the use of shared memory means that all readers must exist on the same machine. This is why the write-ahead log implementation will not work on a network filesystem."

So it seems that we can't have 2 Node.js servers accessing the same SQLite file on a shared volume.

I'm not sure how to do zero downtime deployment (like starting server 2, checking it works, and shutting down server 1, seems risky since we'll have 2 servers accessing the same SQLite file temporarily)

[1] https://sqlite.org/wal.html

The point of Litestream is that you don't have multiple servers accessing the same SQLite file. They all have their own SQLite databases. Of course, you only write to one of them, but that's a common constraint for database clusters.

> I'm not sure how to do zero downtime deployment

AFAIK, you either:

1) Don't, and eat a few seconds of downtime (f.ex if the clients re-try in the background, or..)

2) Start two processes on the same machine (believe that's always safe)

3) Share the database over the network in a way that's safe with sqlite3. Think it's possible, but at this point things are getting too complicated to be worth it IMO.

Also how does the WAL page based replication maintain consistency / handle concurrent updates?

It doesn't, this gives you a read-only replica only.