prolly-store-sqlite

SQLite storage adapter for prolly-map. It stores content-addressed nodes, traversal hints, and named roots in one portable SQLite database.

When to use it

Use this adapter for desktop software, CLIs, local services, durable tests, and single-host applications that value a self-contained database file and SQLite tooling. Choose RocksDB for an embedded key-value engine tuned for sustained write workloads. Choose a remote adapter when multiple hosts must share the same store.

Installation

[dependencies]
prolly-map = "0.4"
prolly-store-sqlite = "0.3.0"

The crate enables rusqlite's bundled SQLite build, so a system SQLite library is not required.

Quick start

use prolly::{Config, Prolly};
use prolly_store_sqlite::SqliteStore;

let store = SqliteStore::open_in_memory()?;
let prolly = Prolly::new(store, Config::default());

let tree = prolly.put(
    &prolly.create(),
    b"project/name".to_vec(),
    b"CrabDB".to_vec(),
)?;
prolly.publish_named_root(b"main", &tree)?;

let loaded = prolly.load_named_root(b"main")?.expect("main root");
assert_eq!(
    prolly.get(&loaded, b"project/name")?,
    Some(b"CrabDB".to_vec())
);
# Ok::<(), Box<dyn std::error::Error>>(())

Use SqliteStore::open("./data/app.prolly.sqlite") for persistence. Reopen the same file in a later process and load a published named root to recover the tree handle.

Schema and configuration

open, open_with_config, and open_in_memory create these tables if needed:

• prolly_nodes(cid, encoding, node) stores nodes by 32-byte CID, using raw or LZ4-encoded payloads.
• prolly_hints(namespace, key, value) stores optional traversal hints.
• prolly_roots(name, manifest) stores named root manifests.

SqliteStoreConfig controls the busy timeout, WAL journaling, SQLite's synchronous=NORMAL setting, database page size, page cache, WAL checkpoint interval, the decoded-node read cache, the minimum node size considered for compression, and the maximum memory-mapped read window. Defaults are a 5-second busy timeout, WAL for file-backed databases, synchronous=NORMAL, 64 KiB database pages, a 64 MiB page cache, a 32,768-page automatic checkpoint interval, a 128 MiB decoded-node cache, and a 256 MiB mapping. Nodes of at least 8 KiB are compressed when LZ4 makes them smaller; typical compact nodes remain raw to avoid spending more CPU than the write saves. Larger pages pack those node payloads efficiently, while the page cache avoids repeated dirty-page spills during bulk publication. The decoded-node cache retains shared immutable nodes across operations, and the checkpoint interval keeps checkpoint I/O outside typical publication commits while retaining bounded WAL growth. The mapping reduces page-read syscall overhead without changing transaction or durability behavior.

use prolly_store_sqlite::{SqliteStore, SqliteStoreConfig};

let store = SqliteStore::open_with_config(
    "./data/app.prolly.sqlite",
    SqliteStoreConfig {
        busy_timeout_ms: 10_000,
        enable_wal: true,
        synchronous_normal: false,
        node_compression_min_bytes: 8 * 1024,
        ..SqliteStoreConfig::default()
    },
)?;
# Ok::<(), Box<dyn std::error::Error>>(())

Use a separate database file for physical tenant or environment isolation. Named-root prefixes such as tenant/42/main organize roots logically, while content-addressed nodes may be shared by multiple roots in the file.

Opening existing databases safely

On Unix, SqliteStore::open_existing opens an existing database without creating the file or running schema DDL. The caller is responsible for validating that the required tables exist.

On Unix, open_existing_verified exposes the device, inode, and length of SQLite's actual open main-database file descriptor to a verifier before any pragma or SQL statement runs. It is useful when a caller must prove that SQLite opened an expected file. Both existing-only entry points fail closed on non-Unix platforms.

Transactions and named roots

The adapter implements Store, ManifestStore, scanning, and TransactionalStore. SQLite transactions atomically validate named-root preconditions and apply node and root writes. A stale precondition returns a conflict without committing staged changes.

SqliteStore owns one connection behind a mutex, so calls through one store are thread-safe and serialized. WAL and the busy timeout help when multiple SQLite connections contend for a file; application-level concurrency should still be measured under the intended workload.

Deleting a named root does not immediately delete unreachable nodes. Plan retention and garbage collection before pruning content-addressed data.

Durable semantic RAG example

examples/semantic_rag.rs builds a native ProximityMap over six support-documentation chunks, publishes its descriptor as the SQLite named root rag/corpus/main, and performs exact cosine retrieval. Run it twice against the same database to see initial construction followed by a process-independent reopen:

rm -f ./target/semantic-rag.sqlite*

cargo run --manifest-path stores/prolly-store-sqlite/Cargo.toml \
  --example semantic_rag -- \
  ./target/semantic-rag.sqlite password-reset

cargo run --manifest-path stores/prolly-store-sqlite/Cargo.toml \
  --example semantic_rag -- \
  ./target/semantic-rag.sqlite lost-2fa

The example prints three ranked chunks with stable keys and source citations, then renders an offline <context> block ready to pass to an LLM. It does not call an embedding or generation service.

The checked-in fixture uses synthetic, precomputed, normalized vectors with 1,536 dimensions. Replace embedding_for_query and corpus ingestion with the embedding provider used by your application; keep the model identifier and dimensions in named-root metadata so incompatible indexes fail closed on reopen.

Testing

The default suite is credential-free and exercises in-memory and file-backed storage, reopen behavior, transactions, scanning, and indexed maps:

cargo test --manifest-path stores/prolly-store-sqlite/Cargo.toml

See the prolly-map API documentation for map, transaction, diff, merge, and proximity-map operations available with this store.