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