Our great sponsors
-
-
InfluxDB
Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.
-
-
-
WorkOS
The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.
-
pocketbase-framework-litestream
A small example using PocketBase as a Go Framework with replication/restoration of SQLite DB with Litestream
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.
[1]: https://github.com/benbjohnson/postlite
[2]: https://github.com/litements/litexplore
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.
[1]: https://github.com/benbjohnson/postlite
[2]: https://github.com/litements/litexplore
Have you compared LZ4 to other compression algorithms, zstd for example? ( https://github.com/phiresky/sqlite-zstd )
Given that LiteFS operates at the filesystem layer via FUSE, have you considered it against designs that use built-in features native to some filesystems? For example, I've considered a similar system design based on a single ZFS primary node that streams ZFS snapshots to reader nodes. With some coordination service (e.g. consul) it could still allow for the whole node promotion process.
> If a LiteFS node is way behind on replication, can it let the app know when it's caught up?
LiteFS exposes the replication position via a file[1] suffixed with "-pos" for each database. That has a monotonically increasing TXID that you could compare to the primary.
We track latency via a Prometheus metric but we don't currently expose that easily to the application. I added an issue for that[2].
> have one Fly instance running and another in cold standby. On deploy, the cold instance boots, fails the health check until replication catches up, then takes over while the first instance goes cold.
When LiteFS starts up, it waits for the node to either connect to the primary and catch up or become the primary itself before it starts the user's application (if running in a supervisor mode). That would probably get you most of the way there to what you're trying to do.
[1]: https://fly.io/docs/litefs/position/
[2]: https://github.com/superfly/litefs/issues/253
Hi, author of litexplore here!
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
For posterity: https://github.com/TylerSustare/pocketbase-framework-litestr...
For posterity: https://github.com/TylerSustare/pocketbase-framework-litestr...