Litestream compatibility?

[Koeng101]

In the documentation:

To work around this limitation, SQLite is patched to always use EXCLUSIVE locking mode for WAL databases.

This seems to mean software like litestream, which uses taking over the WAL file for backups, wouldn't work. I just wanted to confirm I'm correct about that, so I don't use those two pieces of software together!

[ncruces]

Shared-memory WAL was merged with #71.

The driver should be fully compatible with Litestream on Linux and macOS.

---

Your intuition is correct: as things stand this driver is not compatible with Litestream.
Supporting WAL in a way that enables (an external process like) Litestream to work is particularly hard.

[gedw99]

https://github.com/maxpert/marmot is an alternative from Litestream that uses NATS for storage.

you can easily get it going using embedded nats too: https://github.com/maxpert/marmot/blob/master/stream/embedded_nats.go

Its using github.com/mattn/go-sqlite3:
https://github.com/maxpert/marmot/blob/master/db/sqlite.go#L15C3-L15C30

[ncruces]

This again depends on WAL mode, for which current support is less than great.

[ncruces]

As an update on this, I'm considering asynchronous replication based on a VFS and the LTX format instead.

If an ecosystem of tools forms around LTX (against which I can target compatibility) that seems a much better bet.

[ncruces]

Yeah, I already use that strategy for the GORM bindings and think that it helps cull what clients need to download.

Litestream needs to be its own process because of file-locking issues. It depends on mattn and multiple SQLite versions in one process is a big red flag.

[gedw99]

I see your point about litestreams dependencies.

today I got pockbase and marmot running . It uses NATS Jetstream to replicate SQLite.

https://github.com/maxpert/marmot has
Nats is embedded and does the replication of SQLite.

https://github.com/maxpert/marmot-pocketbase-flyio brings the 3 together.

It worked first time and I added a make file to automate it. https://github.com/maxpert/marmot-pocketbase-flyio/pull/4/files

[ncruces]

Not 100% sure I understand what this means.
Is it that this driver works with marmot doing leaderless replication out-of-process?
That's great if so!

[ncruces]

One thing I'd be interested in knowing, and that may make sense here (for read-only replicas), is if a OPEN_READONLY WAL connection works and gets updates as they come in. Just because it's not something I've tested, and read-only connections have subtly different locking.

[gedw99]

Yes it’s out of process.

I need to adjust the nats Jetstream code so it supports gateways and super clusters. This will allow replication between all you data centers globally without by data sync clashes.

bats supports this natively . So no risk.

https://docs.nats.io/running-a-nats-service/configuration/gateways

it’s just a matter of changing the config

https://docs.nats.io/running-a-nats-service/configuration/gateways/gateway

[gedw99]

See maxpert/marmot#110

will swing by and do thing one soon hopefully

[gedw99]

Hey @ncruces

Regarding OPEN_READONLY WAL, suggest checking the golang code in Marmot. It's pretty simple code.
You're way more familiar with that too. I am not that knowledgeable about lower level aspects of the WAL

[ncruces]

OK, that's not what I'm asking. I'd like to understand if an app using my driver that opens connections read-only, gets updates from other processes (like marmot). It should, but is definitely less tested.

[gedw99]

I don’t know - check the code .
It’s sound a lot like you are wanting to do CDC pattern ? Best to Google it if you don’t know it .