6 ms·

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

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.

benbjohnson 4y ago
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 https://github.com/benbjohnson/postlite [2]: https://github.com/litements/litexplore https://github.com/litements/litexplore
- polyrand 4y ago
  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 https://github.com/dbeaver/cloudbeaver [1]: https://datasette.io/ https://datasette.io/ [2]: https://github.com/coleifer/sqlite-web https://github.com/coleifer/sqlite-web
bob1029 4y ago
IMO, the entire point of using something embedded like SQLite is to avoid the network stack. Using SQLite, you can reliably satisfy queries in tens-to-hundreds of microseconds. I cannot imagine any scenario wherein a single-node hosted DB (across the LAN) can outperform a properly-configured SQLite instance (in same process). DB administration for us is included in an admin web app that is part of the product stack.
- gunnarmorling 4y ago
  > I cannot imagine any scenario wherein a single-node hosted DB (across the LAN) can outperform a properly-configured SQLite instance (in same process). SQLite only supports one writer at a time, so any use case with more than a handful of concurrent writes will likely be better off with a remote DB which typically can handle thousands of active connections issuing transactions.
  - bob1029 4y ago
    How fast a network are we talking about? SQLite can max out my NVMe drives, despite its internal serialization constraint. Thousands of connections at milliseconds is similar to a few connections at microseconds. Latency is everything.
    - tptacek 4y ago
      Another thing people are sleeping on is that you can't have concurrent writes to a single SQLite database, but you can have lots of SQLite databases --- SQLite will even do joins across them.
      - geenat 4y ago
        Had no idea about joining across multiple databases! Hot tip! Yeah, you can drastically increase write throughput by just having extra SQLite databases.. Example pattern: * main.db * sessions.db * analytics.db * cache.db Will 4x your writes. Combined with a modern nvme drive, you begin reaching crazy fast write throughput which you'd require far more traditional database nodes to achieve, but on a single machine.

benbjohnson 4y ago

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 https://github.com/benbjohnson/postlite [2]: https://github.com/litements/litexplore https://github.com/litements/litexplore

polyrand 4y ago
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 https://github.com/dbeaver/cloudbeaver [1]: https://datasette.io/ https://datasette.io/ [2]: https://github.com/coleifer/sqlite-web https://github.com/coleifer/sqlite-web

bob1029 4y ago

IMO, the entire point of using something embedded like SQLite is to avoid the network stack. Using SQLite, you can reliably satisfy queries in tens-to-hundreds of microseconds. I cannot imagine any scenario wherein a single-node hosted DB (across the LAN) can outperform a properly-configured SQLite instance (in same process). DB administration for us is included in an admin web app that is part of the product stack.

gunnarmorling 4y ago
> I cannot imagine any scenario wherein a single-node hosted DB (across the LAN) can outperform a properly-configured SQLite instance (in same process). SQLite only supports one writer at a time, so any use case with more than a handful of concurrent writes will likely be better off with a remote DB which typically can handle thousands of active connections issuing transactions.
- bob1029 4y ago
  How fast a network are we talking about? SQLite can max out my NVMe drives, despite its internal serialization constraint. Thousands of connections at milliseconds is similar to a few connections at microseconds. Latency is everything.
  - tptacek 4y ago
    Another thing people are sleeping on is that you can't have concurrent writes to a single SQLite database, but you can have lots of SQLite databases --- SQLite will even do joins across them.
    - geenat 4y ago
      Had no idea about joining across multiple databases! Hot tip! Yeah, you can drastically increase write throughput by just having extra SQLite databases.. Example pattern: * main.db * sessions.db * analytics.db * cache.db Will 4x your writes. Combined with a modern nvme drive, you begin reaching crazy fast write throughput which you'd require far more traditional database nodes to achieve, but on a single machine.