sqlite-remote-vfs

A SQLite VFS that stores the database file on a remote server instead of the local disk. SQLite runs unchanged in the client process. A commit returns only after the server has stored it. With encryption above the VFS, such as SQLite3 Multiple Ciphers or SQLCipher, the server stores only encrypted pages.

Runs natively and in the browser (wasm32-unknown-unknown). In the browser it can also keep databases only in IndexedDB, without a server.

Status: works and is tested, not yet in production use. Versions are 0.x: the protocol and the API can still change.

Features

• Durable commits. At SQLITE_FCNTL_SYNC the VFS sends all blocks changed by the transaction to the server and waits for the acknowledgement. The server applies a commit atomically. After a connection loss the VFS reconnects and checks whether the last commit was applied before it sends the commit again.
• Encryption above the VFS. The VFS itself does not encrypt and depends on no encryption library. Encryption that runs above it keeps plaintext and the database key away from the server. Tested with SQLite3 Multiple Ciphers (open through RemoteVfs::encrypted_name()) and SQLCipher (open through RemoteVfs::name()), in both cases with PRAGMA key. With plain SQLite the server stores plaintext.
• Login by signature. The application passes a Signer: an algorithm, a public key and a sign function. The server sends a challenge, verifies the signature and derives the client's subject from the public key. A client can only open databases under its own subject. The VFS holds no key material. Supported algorithm: Ed25519. The signature is not bound to the connection, so a server the client connects to could relay another server's challenge. Use a separate key for each server.
• Access tokens (optional). For a server that admits only clients with a token from a token service, set Server::token to a TokenSource. The VFS asks it for a token before every login and sends it in the Hello; the token is opaque to the VFS. If the server rejects the token, the VFS asks once more. It reconnects with a new token before the old one expires, between two requests, and resumes its databases. A rejected token fails the registration with Error::is_access_denied(), and a later open or commit with SQLITE_AUTH.
• One writer per database. Opening a database acquires a lease. A newer lease has a higher epoch and fences off all older ones, so an outdated client cannot overwrite newer data.
• Deletion. RemoteVfs::delete_database deletes a database on the server, and its cache. Opened again with SQLITE_OPEN_CREATE, it comes back empty and may use another page size and another key. A client that held a lease on the deleted database can no longer commit, also not after the database was created anew. The server also deletes databases that have not been used for a configurable time (180 days by default). A cache of a deleted database is never used again, also on other devices.
• Slots (with access tokens). A slot ties the token's owner (sub) to one key, for applications where a user has exactly one set of databases at a time. RemoteVfs::claim_slot() passes the owner's slot to this key; the server deletes the databases of the key that held it before, and that key's open databases fail with TakenOver. The result names the label of the replaced slot. RemoteVfs::set_slot_label() sets the label, e.g. a device ID; only the key that holds the slot can. RemoteVfs::delete_slot() releases the slot and deletes all databases of this key. While another key holds the owner's slot, opening fails with SQLITE_PERM. The server tells the slot's label without a key, see sqlite-remote-server.
• Open errors. Opening without SQLITE_OPEN_CREATE fails with SQLITE_CANTOPEN only if the database does not exist, with SQLITE_BUSY if another instance has it open, and with SQLITE_PERM if another key holds the owner's slot. Other failures, such as an unreachable server, give SQLITE_IOERR. An application can therefore tell a database that is not stored from one it cannot reach.
• Why a database stopped. After a failed commit or read, RemoteVfs::failure() tells why the open database can no longer be used: TakenOver (another instance took it over), Unreachable, Denied (access token), Rejected or Full. Closing and opening the database again resets it.
• Takeover without a request. The server tells the holder of a database when another instance takes it over. failure() then returns TakenOver, and the function in Server::on_takeover is called with the database's name, never during a call from SQLite: in a browser at once, natively within one ping interval. While idle, the client pings the server, so the connection and the leases stay alive; in a browser the connection worker does that.
• Healing after an outage. A database that failed as Unreachable heals by itself on the same SQLite connection. While the server is gone, every access fails at once; in the background the VFS tries to connect. The first access after the server accepted a connection resumes the lease and reloads the database as of the server's version. With a journal on the VFS (journal mode DELETE, SQLite's default, TRUNCATE or PERSIST), SQLite then rolls a commit that failed back, also if it had reached the server. With journal mode MEMORY the database keeps the server's version, with or without that commit. A takeover during the outage is reported as TakenOver.
• Server errors during a commit. If the server fails a commit for now (ERROR_CODE_INTERNAL, e.g. while its database is unreachable), the VFS sends the same commit again after a short pause; the server acknowledges a repeated commit without applying it twice. If that does not succeed within Server::reconnect_timeout, the database fails as Unreachable and heals as above, instead of failing for good.
• Cache (optional). A file natively. In the browser, an IndexedDB database per database, so that several databases of one key keep their own caches. Reads are served from it without a round trip. It contains only data the server has acknowledged and may be incomplete; missing blocks are fetched from the server. A stale cache is brought up to date from the server's change log: only the blocks changed since its version are discarded.
• Memory limit (optional). Memory::Blocks(n) keeps at most n blocks in memory and evicts the least recently used. Blocks modified since the last commit are never evicted.
• Loading. Load::Preload (default) reads the whole database when it is opened. Load::OnDemand fetches blocks when SQLite first reads them.
• Local databases in the browser. Config::local keeps the databases only in IndexedDB, with the same API and without a server or signer. A commit returns when its IndexedDB transaction is complete. One instance per database at a time, also across tabs; see Local databases.
• TLS. wss:// natively via rustls, trusting the operating system's certificate store and the CAs in Server::extra_roots. In the browser, the browser handles TLS. ws:// is supported for local servers and servers behind a TLS-terminating proxy.

Example

use std::sync::Arc;

use rusqlite::{Connection, OpenFlags};
use sqlite_remote_vfs::{Algorithm, Config, RemoteVfs, Signer};

struct Key(ed25519_dalek::SigningKey);

impl Signer for Key {
    fn algorithm(&self) -> Algorithm {
        Algorithm::Ed25519
    }

    fn public_key(&self) -> Vec<u8> {
        self.0.verifying_key().to_bytes().to_vec()
    }

    fn sign(&self, message: &[u8]) -> Vec<u8> {
        use ed25519_dalek::Signer as _;
        self.0.sign(message).to_bytes().to_vec()
    }
}

let signer: Arc<dyn Signer> = Arc::new(Key(signing_key));
let vfs = RemoteVfs::register("remote", Config::server("wss://server.example/v1/ws", signer))?;

let flags = OpenFlags::SQLITE_OPEN_READ_WRITE | OpenFlags::SQLITE_OPEN_CREATE;
let conn = Connection::open_with_flags_and_vfs("app.db", flags, vfs.encrypted_name().as_str())?;
conn.pragma_update(None, "key", database_key)?;
// From here on, use SQLite as usual.

The example uses SQLite3 Multiple Ciphers. With SQLCipher, open through vfs.name() instead.

Config also sets the page size (default 4096), the memory limit, the loading mode, the timeout and lease takeover. Server holds what only applies to a server: the cache, the reconnect timeout and additional CA certificates; build it with Server::new and pass Config::new(Store::Server(server)). vfs.stats() returns counters for commits, fetches, the cache and evictions.

In the browser

• Register with RemoteVfs::register_async instead of register.
• SQLite and the VFS must run in a dedicated worker. The VFS blocks with Atomics.wait, which browsers do not allow on the main thread.
• The page must be cross-origin isolated (Cross-Origin-Opener-Policy: same-origin, Cross-Origin-Embedder-Policy: require-corp), because the VFS uses SharedArrayBuffer.
• The VFS starts a second worker that holds the WebSocket connection.
• Cache::Browser keeps the cache in IndexedDB.

browser/ builds SQLite with SQLite3 Multiple Ciphers for the browser and contains the browser tests.

Local databases

In the browser, a VFS can keep its databases only in IndexedDB, for development, demos, tests and deployments without a server. Opening, encryption and SQL are the same as with a server:

let vfs = RemoteVfs::register_async("local", Config::local("my-app")).await?;
let conn = Connection::open_with_flags_and_vfs("app.db", flags, vfs.encrypted_name().as_str())?;

• Each database is an IndexedDB database named sqlite-remote-vfs-local/<namespace>/<database>. The namespace keeps applications or users apart and must not be empty or contain /.
• Durability. A commit returns when its IndexedDB transaction is complete. It is requested with durability strict, which asks the browser to write it to disk before completing; browsers without that option use their default. A completed commit survives a crash of the tab or the browser. Whether it survives a crash of the operating system or a power loss depends on that write to disk.
• Failures. A failed commit leaves the stored database unchanged. SQLite gets SQLITE_FULL if the storage quota is exhausted, otherwise SQLITE_IOERR_FSYNC, and rolls back. The database then accepts no more writes until it is opened again.
• One instance per database. Opening takes a Web Lock named after the database, which also covers other tabs and workers of the origin. A second instance gets SQLITE_BUSY. With Config::takeover it takes the database over; the first instance then can no longer commit. Opening also increments an epoch in the stored database, and every commit checks it in its transaction, so this holds even before the first instance learns that it lost the lock. Locks need a secure context (HTTPS or localhost).
• RemoteVfs::delete_database takes the lock too, so it fails while another instance has the database open, unless Config::takeover is set.
• The data is lost when the user clears the site data, and the browser may evict it under storage pressure. The application can ask for persistent storage with navigator.storage.persist().
• Load and Memory work as with a server. vfs.stats() counts commits to IndexedDB as commits and reads from it as local_reads; the server counters stay zero.

To move a local database to a server, open it and an empty database on a server VFS, both with the same key, and copy it with SQLite's online backup API (rusqlite::backup::Backup), in steps. The source can be used between the steps. The copy reaches the server as one commit, so it must fit the server's commit limit (256 MiB by default).

Other languages

sqlite-remote-vfs-ext builds the VFS as a loadable SQLite extension with a C interface. It contains no SQLite: it uses the SQLite that loads it, whether plain SQLite, SQLite3 Multiple Ciphers, SQLCipher or another build (3.14.0 or later, with extension loading enabled). sqlite-remote-vfs-ffi builds the same interface as a static library for programs that link SQLite themselves.

cargo build --release -p sqlite-remote-vfs-ext   # target/release/libsqlite_remote_vfs_ext.so or .dylib
cargo build --release -p sqlite-remote-vfs-ffi   # target/release/libsqlite_remote_vfs_ffi.a

The interface is declared in crates/sqlite-remote-vfs-ffi/include/sqlite_remote_vfs.h:

1. Load the extension with sqlite3_load_extension() or load_extension(). With the static library, skip this step.
2. Call sqlite_remote_vfs_register() with a name and a configuration: URL, public key and a sign function. With the extension, call it from the loaded library: link against it or look it up with dlsym. The private key stays in the calling program; the sign function is called at every login.
3. Open databases through the VFS name, or through multipleciphers-<name> with SQLite3 Multiple Ciphers.
4. To delete a database, call sqlite_remote_vfs_delete_database() with the VFS name and the database name.

A program that links the static library also links SQLite and the system libraries that cargo rustc -p sqlite-remote-vfs-ffi --crate-type staticlib -- --print native-static-libs lists.

sqlite_remote_vfs_config config = {
    .struct_size = sizeof config,
    .url = "wss://vfs.example/v1/ws",
    .algorithm = SQLITE_REMOTE_VFS_ED25519,
    .public_key = public_key,
    .public_key_len = 32,
    .sign = sign,              /* signs with the application's Ed25519 key */
    .sign_context = key,
};
char *error = NULL;
if (sqlite_remote_vfs_register("remote", &config, &error) != SQLITE_OK) {
    fprintf(stderr, "%s\n", error);
    sqlite_remote_vfs_free(error);
}
sqlite3_open_v2("app.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, "remote");

examples/python/remote_vfs.py does the same from Python with the sqlite3 and ctypes modules.

How it works

The server stores the main database file as numbered blocks of the page size, plus a version number per database that increases with every commit. Journals and temporary files stay in the client's memory. WAL mode is not available, because the VFS provides no shared memory.

Client and server exchange Protocol Buffers messages over a WebSocket, one binary frame per message. The protocol is defined in proto/sqlite_remote/v1/sqlite_remote.proto. proto/testdata/v1/ contains one encoded sample of every message; a server implementation can check that it decodes and encodes them identically.

The server is sqlite-remote-server, written in Go, with an in-memory store and a PostgreSQL store.

Not there yet

• One database per registered VFS, one connection per database.
• The C interface does not pass access tokens yet.
• Recovery after the server was restored from a backup: a client that has seen commits the restored server no longer has is not handled yet. The protocol reserves a field number for it.

Layout

Path	Content
proto/sqlite_remote/v1/sqlite_remote.proto	protocol definition
proto/testdata/v1/	one encoded sample of every message
crates/sqlite-remote-protocol	Rust types for the protocol, generated with prost and checked in, and the tests that check them against the .proto file and check the samples
crates/sqlite-remote-vfs	the VFS. tests/remote.rs runs against a server, tests/tls.rs over wss:// through a TLS terminator started by the test, tests/spike.rs checks SQLite's VFS behaviour with an in-memory VFS
crates/sqlite-remote-vfs-ffi	C interface and header, built as a static library
crates/sqlite-remote-vfs-ext	the loadable extension: the C interface plus the entry point, using the SQLite that loads it
crates/sqlite-remote-harness	test tool: crash test, fault injection, measurements, latency proxy, load test
browser/	separate workspace for wasm32-unknown-unknown with the browser tests, see browser/README.md
extension/	separate workspace that tests the extension and the static library with plain SQLite
examples/python/	the extension used from Python
sqlcipher/	separate workspace that tests the VFS with SQLCipher instead of SQLite3 Multiple Ciphers
vendor/libsqlite3-sys	libsqlite3-sys 0.36.0 with SQLite3 Multiple Ciphers for native builds, see vendor/libsqlite3-sys/sqlite3mc/README.md

Build and test

Requires rustup. rust-toolchain.toml pins Rust 1.97.1.

cargo test
cargo test --test spike -- --nocapture    # also prints the recorded VFS calls
SQLITE_REMOTE_TEST_URL=ws://localhost:8080/v1/ws cargo test -p sqlite-remote-vfs --test remote --test tls

The Rust code for the protocol is generated with prost and checked in as crates/sqlite-remote-protocol/src/sqlite_remote.v1.rs, so a build runs no code generator. After a change to the .proto file, rewrite that code, then the samples in proto/testdata. protox compiles the .proto file in Rust, so protoc and buf are not needed:

SQLITE_REMOTE_UPDATE_GENERATED=1 cargo test -p sqlite-remote-protocol --test generated
SQLITE_REMOTE_UPDATE_GOLDEN=1 cargo test -p sqlite-remote-protocol --test golden

The native tests use SQLite3 Multiple Ciphers. The tests with SQLCipher are a separate workspace, because one build links exactly one SQLite. They build SQLCipher and OpenSSL from source:

cd sqlcipher && cargo test          # with SQLITE_REMOTE_TEST_URL also against a server

The tests of the extension and the static library are a separate workspace too, with plain SQLite. They load the extension from target/debug:

cargo build -p sqlite-remote-vfs-ext && (cd extension && cargo test)

The tests in remote.rs and tls.rs need a running sqlite-remote-server and are skipped without SQLITE_REMOTE_TEST_URL. The server's SQLITE_REMOTE_SERVER_ID must equal that URL. The tests log in with random keys. The TLS tests create their own CA and certificates and start a TLS terminator in front of the server, so no system configuration is needed.

CI runs all tests against the server. The native tests and those of the extension and the static library run on Linux, macOS and Windows, the tests against PostgreSQL and with SQLCipher on Linux, and the browser tests in Firefox, Chrome, Edge and Safari.

Harness

Runs against a server that is already running.

sqlite-remote-harness crash   --url ws://… [--iterations 30]        # kills a writer at random, checks that no acknowledged commit is lost
sqlite-remote-harness faults  --url ws://… [--transactions 1500]    # breaks the connection during commits, via a proxy
sqlite-remote-harness load    --url ws://… [--clients 10] [--seconds 20] [--rate 0] [--kind keystore|message]
sqlite-remote-harness measure --url ws://… [--delays-ms 0,25,50]    # prints the measurements as Markdown tables
sqlite-remote-harness proxy   --url ws://… [--port 18190] [--delay-ms 25] [--mbit 20]

load runs many clients in parallel. Afterwards each client reopens its database from the server and checks that it contains exactly the acknowledged commits. --rate is commits per second per client; 0 means as fast as possible. With a rate, latency is measured from the time a commit was due, so a server that falls behind shows up as latency.

License

Apache License 2.0, see LICENSE.

vendor/libsqlite3-sys contains third-party code under its own licenses: libsqlite3-sys under the MIT license (vendor/libsqlite3-sys/LICENSE), SQLite3 Multiple Ciphers under the MIT license (vendor/libsqlite3-sys/sqlite3mc/LICENSE), and SQLite in the public domain.