Hi folks

I use sqlite as an embedded database for my application. I am happy with its speed, ease of setup, and simplicity of golang API.

I want to add crash resistance to my application using replication. i.e. the master instance of my application runs at host H1. Any update to the database at H1 is also copied to hosts H2,H3,...Hn. The transaction is considered committed at H1 if it is also copied to 3 other hosts (the number is configurable).

If host H1 crashes then the system picks a new master among hosts that got the latest transaction data.

I am looking for such a transaction replication solution. It must be stable, effective (low overhead), and have a low replication latency.

I checked sqlite documentation/sources but see nothing related to such stream replication.

Googled and found a few projects that try to fill this gap. Found litestream project. It tracks WAL file changes and copies them to the slave hosts. Unfortunately, litestream dropped the replication functionality for some reason. https://github.com/benbjohnson/litestream/commit/868d5649885ac35111c93346f40fc0f0dd061128

So I am looking for some other solution. Ideally, it should be some low-overhead replication API extension for sqlite3. Are there any recommendations here?

LiteFS appears appealing.

You might want to checkout dqlite -- https://github.com/canonical/dqlite.

Raft-based replication (in C) and it is a very active project.

strangely there is also rqlite that sounds very similar

And we can round the list out with Bedrock.

rqlite is Go based (love Go but it changes things) and also cannot do transactions or non-deterministic functions.

So I looked at some proposed replication options.

• Litestream (now removed code) uses WAL files. It tracks changes to those files and if any then it sends updates to the slave node.

• LiteFS (a replacement for LiteStream?) - uses fuse filesystem. If there is any change at the block level then the FS copies the block to the slave.

The problem with the first solution is that it builds applications on top of abstractions that were not created with data replication in mind.

The LiteFS is even further away as it adds more layers of abstraction and complicates the solution even more. LiteFS works on the OS block layers, which itself adds latency and complexity. It requires extra kernel modules and a daemon.

I am surprised that SQLite itself does not have any simple API for data update tracking (basic functionality for needed for replication).

What I really want is an API like this:

master:

while(true) {
   evt = sqlite3_wait_for_data_change(db)
   // evt contains serialized data change and its length

   // here we have app-specific logic to copy evt{} to other hosts
}

slave:

while(true) {
  // app-specific code to receive data and length from the master

  evt = {
    .data = data,
    .len = len
  }

  sqlite_apply_data_change(db, evt)
}

what would be the closed existing API to make such functionality possible?

I am surprised that SQLite itself does not have any simple API for data update tracking (basic functionality for needed for replication).

I'm not surprised at all. Core SQLite itself is intended to be light.

Remember the goal is to replace fopen(), not MySQL or IDMS or DB2 or Oracle. A distributed database system is out of scope.

But anyone is free to add that on top of the base product.

In my opinion the huge success of SQLite is mostly caused by discipline in avoiding bloat and featuritis.

I agree, But they could offer some function/callback to extend sqlite if we want too .

They have done it with virtual table by example.

It's not quite what you describe, but the sessions extension is one way to capture changes so that they can be applied to a follower database:

https://www.sqlite.org/sessionintro.html

It uses the preupdate hook:

https://sqlite.org/c3ref/preupdate_count.html

Dan.

I just looked at https://www.sqlite.org/sessionintro.html , and the first sentence of the Introduction starts "The session extension provide a mechanism for recording changes to some or all of the rowid tables in an SQLite database...."

The stated restriction to rowid tables no longer seems necessary.

Gerry

The stated restriction to rowid tables no longer seems necessary.

If you look at the signature of the callback used in the preupdate API, you will find that it is tailored for capturing changes to rowid tables. Further, that callback is not allowed to do the querying that would generally be needed to identify what row was being modified for a without-rowid table.

However, with enough work to capture primary key info, before and after a change, and record that info without using the connection passed to the callback, one could use it for without-rowid tables too.

And, lo: The sentence you quoted is not exactly a restriction. The API itself works as described, which description mentions operation with without-rowid tables.

It looked to me as if the quoted sentence was left over in error from pre-3.17.0 days.

Gerry

The Session Extension write-up has been clarified to avoid suggesting an overly restrictive criterion for the extension's applicability.

Larry, thank you for the clarification. I'm curious about this part (as I'm working on a session-extension-based sync):

extra steps are needed to record primary keys for WITHOUT ROWID table changes

What are these extra steps that are needed in this case?

I did not mean to be mysterious. The API passes before/after rowids directly to the callback. If those are not the PK, (as must be the case for WITHOUT ROWID tables), then sqlite3_preupdate_{new,old}() will need to be called within the preupdate callback to get the PK column(s). One or two extra steps.

I will reconsider that language.