Having an interest in Sqlite and LiteFS, but not having explored either very much, I wonder what the DB administration aspect looks like?
Say I want to connect a SQL client to my production DB do I expose a port on my production app that speaks the protocol? Or maybe I have a separate container that is basically just the Sqlite replica that I can connect to? Or maybe there is some other approach that I'm just not seeing?
Fly, Sqlite, and LiteFS is definitely an interesting combination but I'm still sort of mentally stuck in the managed (and standalone) DB model.
Currently, you need to SSH in and use the sqlite3 CLI on the server. There has been some work in this area but it's all still rough around the edges. I wrote a server called Postlite[1] that exposes remote SQLite databases over the Postgres wire protocol but it's very alpha. :)
I'd love to see more work in this area. Ricardo Ander-Egg wrote a remote management tool called litexplore[2] that connects over SSH to the SQLite CLI behind the scenes. I haven't used it but I think there's a lot of potential with that approach.
I agree remote management is the "least convenient" aspect of SQLite. If you have control over the remote VM where the DB is hosted, you can also run something like ClouDBeaver[0], Datasette[1] or sqlite-web[2] as a Docker container, then connect to it from your laptop.
The reason I wrote litexplore was that those approaches require exposing extra ports, managing authentication, etc. With litexplore, you just need the sqlite3 CLI (compiled with the JSON1 extension) installed in the VM and a way to access it over SSH.
litexplore is still in early stages and I have some updates planned in the following 2 months, but the basics are working.
[0]: https://github.com/dbeaver/cloudbeaver [1]: https://datasette.io/ [2]: https://github.com/coleifer/sqlite-web