go-sqlite3

A read-only VFS doing this can be really simple, with the right API… This is my VFS: https://github.com/ncruces/go-sqlite3/blob/main/vfs/readervf... And using it with range requests: https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/readerv... And having it work with a Zstandard compressed SQLite database, is one library away: https://pkg.go.dev/github.com/SaveTheRbtz/zstd-seekable-form...

I also have this implemented and ready to go in my Go SQLite driver: https://github.com/ncruces/go-sqlite3/blob/main/litestream/e... Slightly different API (programmatic, no env variables, works with as many databases as you may want), but otherwise, everything should work. Note that PRAGMA litestream_time is per connection, so some care is necessary when using a connection pool.

Started as fun "what if" side project, but kept me interested for 3 years now. Turns out it's actually useful. It's pretty portable: with some caveats, it works pretty much everywhere Go does. Performance is bad outside amd64/arm64, but for most popular OS/platforms it's fine. See this for an overall picture (these are the platforms I test): https://github.com/ncruces/go-sqlite3/wiki/Support-matrix I bet you could do the similar with pglite, but this (and helping out with wazero) already consumes all my spare time.

I knew I remember your name working with something of sqlite and golang https://github.com/ncruces/go-sqlite3 Man I really enjoy golang and cross portability and wazero + sqlite could still be cross portable which is super fascinating What are your thoughts on https://github.com/electric-sql/pglite (postgres in wasm)? Also what were the goal behind a pure golang solution via wazero + wasm sqlite as you had made? Was it cross platform support, if so, what are your thoughts on zig, I have seen a project use zig + golang to create cross platform C language support but I think that adding zig into the picture complicates the build process so there are tradeoffs and I am interested to hear your opinions about it!

Yes I want to know too. Where can I run this test matrix for less than $100/mo unbilled (where half the “cost” is from macOS alone): https://github.com/ncruces/go-sqlite3/wiki/Support-matrix I built a similar matrix to test wazero (of which I'm a maintainer).

> One good reason is that people have written golang adapters, so that you can use sqlite databases without cgo. There's also the Go-wrapped WASM build of the C sqlite[0] which is handy. [0] https://github.com/ncruces/go-sqlite3

SQLite directly against S3 is workable if you mean querying a read-only database. For example, from Go, you could use my driver, and point it to a database file stored in S3 using this: https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/readerv... For read-write it's a terrible idea. Object storage assumes objects are immutable. There may be some support for appends, but modifying the middle of an object in place involves copying the entire thing. What is on the verge of becoming viable is to use Litestream to do asynchronous replication to S3, and have read replicas that stream the data directly from S3. But what's stored in S3 isn't a database file, but a format created for the purpose called LTX.

I've been trying to get a headstart on the lightweight read replicas for my Go SQLite driver and, yes, there are issues. https://fly.io/blog/litestream-revamped/#lightweight-read-re... But the concept is really cool, and it's workable. I already have a version that mostly works. https://github.com/ncruces/go-sqlite3/tree/litestream/litest... With Litestream v0.5.0 doing the replication, you'll get some transient read failures that really shouldn't happen. If you build Litestream from head (or wait for v0.5.1), that'll be fixed. You'll still have similar issues around the time of a snapshot, if you keep only one snapshot. Other than that, I'm pretty confident it just works. But the performance probably sucks. I need to add a layer of caching. But that's it.

The Litestream read replicas they announced, just for my Go SQLite driver. They mostly work already, would appreciate testing from anyone who already has a larger, real-world Litestream v0.5.0 setup running. https://fly.io/blog/litestream-revamped/#lightweight-read-re... https://github.com/ncruces/go-sqlite3/tree/litestream/litest...

My version of the VFS should be ready for testing. After some testing, I expect it to be working correctly in the vast majority of cases, but the performance may disappoint. The original still needs work. https://github.com/ncruces/go-sqlite3/tree/litestream

I mean, that's literally their "What’s next?" from the OP: https://fly.io/blog/litestream-v050-is-here/#whats-next They already have a prototype, and... it's pretty rough on the edges. I'm porting it to my Go SQLite driver and already ran into a bunch of issues. But it seems at least feasible to get it into a working shape. https://github.com/benbjohnson/litestream/issues/772 https://github.com/ncruces/go-sqlite3/compare/main...litestr...

Does this use an external binary or CGO or Wazero (Wasm) or is it rewritten in Go? With SQLite, although all approaches are available, my fav is to use https://github.com/ncruces/go-sqlite3 which uses Wazero. I try to avoid CGO if I can because it adds compile-time complexity, making it unfriendly for a user to compile.

The way they got multi-writer working is a bit too adventurous for me. It involves fiddling the database header to ensure you can keep track of the read-set from the VFS layer, by convincing SQLite to drop its page cache at the start of every transaction. Other than that, a MVCC VFS is relatively straightforward. Here's an in-memory experiment if a few hundred lines of Go: https://github.com/ncruces/go-sqlite3/blob/v0.29.0/vfs/mvcc/... I haven't settled on what's a good storage layer for something like this.

My driver (Wasm based) does many of these things by default. Foreign keys are default enabled, default synchronous mode is FULL for rollback and NORMAL for WAL, shared cache is disabled, the default busy timeout when using database/sql is 60 seconds. About the default huge busy timeout: my driver can easily afford this because context cancellation actually works and interrupt both long running queries and filesystem locks. I don't think the mattn driver can use a (e.g.) WithDeadline(100ms) context to interrupt early a connection blocked on a busy timeout of 5 seconds: your goroutine will be stuck in a Cgo loop without anything to interrupt it. Doing this properly requires a custom busy handler, the overhead of which might be to big for Cgo (not sure). Having done this, I also implemented the Ruby approach to improve tail latencies: https: