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)
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.
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.
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