cache.storage.sqlite

SQLiteStorage

class SQLiteStorage(BaseStorage)

Overview

Durable, single-file storage backed by sqlite3, using Write-Ahead Logging for better read/write concurrency. This is the default backend (CacheConfig.storage_backend == StorageBackend.SQLITE) and also serves as RedisStorage's automatic fallback when Redis is unreachable.

Schema

CREATE TABLE IF NOT EXISTS cache_entries (
    cache_key   TEXT PRIMARY KEY,
    tenant_id   TEXT NOT NULL,
    payload     TEXT NOT NULL,   -- JSON-serialized IntelligentCacheEntry
    expires_at  REAL             -- absolute Unix timestamp, or NULL
);
CREATE INDEX IF NOT EXISTS idx_tenant ON cache_entries(tenant_id);
CREATE INDEX IF NOT EXISTS idx_expires ON cache_entries(expires_at);

Constructor

SQLiteStorage(db_path: str, wal: bool = True, cache_size_kb: int = 65536) -> None

On construction, immediately opens (or creates) a connection for the calling thread and executes the DDL above, so the file and schema exist before the constructor returns.

Connection model

Each thread gets its own sqlite3.Connection, stored in threading.local() and created lazily by the private _conn() helper the first time that thread calls any public method. Every new connection:

1. Is opened with check_same_thread=False and a 10-second busy timeout.
2. Uses sqlite3.Row as its row factory (so results are accessed by column name, e.g. row["payload"]).
3. Applies PRAGMA journal_mode=WAL (if wal=True), PRAGMA cache_size, PRAGMA synchronous=NORMAL, and PRAGMA temp_store=MEMORY.
4. Re-runs the DDL (idempotent, IF NOT EXISTS).

Methods

get(key: str) -> Optional[IntelligentCacheEntry]

Selects payload, expires_at for cache_key = ?. Returns None if no row exists. If expires_at is set and in the past, deletes the row (lazy expiry, same pattern as MemoryStorage) and returns None. Otherwise JSON-decodes payload and reconstructs it via IntelligentCacheEntry.from_storage_dict(). If deserialization raises any exception, the error is logged (logger.error) and None is returned rather than propagating the exception.

• Raises: Does not raise on deserialization failure — errors are swallowed and logged.
• Side effects: may delete an expired row; issues one SELECT, and conditionally one DELETE + commit().

set(key: str, entry: IntelligentCacheEntry, ttl_s: Optional[float] = None) -> None

Serializes entry via IntelligentCacheEntry.to_storage_dict() and json.dumps(..., ensure_ascii=False), then performs an upsert:

INSERT INTO cache_entries(cache_key, tenant_id, payload, expires_at)
VALUES (?, ?, ?, ?)
ON CONFLICT(cache_key) DO UPDATE SET
    tenant_id=excluded.tenant_id,
    payload=excluded.payload,
    expires_at=excluded.expires_at

Note the same if ttl_s: truthiness check as MemoryStorage — a ttl_s of 0 is treated as "no TTL" (expires_at = None).

delete(key: str) -> bool

DELETE from zerda.cache_entries WHERE cache_key=?; returns cur.rowcount > 0.

exists(key: str) -> bool

SELECT 1 ... WHERE cache_key=? AND (expires_at IS NULL OR expires_at > ?) — unlike MemoryStorage.exists(), this does not delete expired rows as a side effect; it simply filters them out of the result.

keys_by_tenant(tenant_id: str) -> List[str]

SELECT cache_key ... WHERE tenant_id=? AND (expires_at IS NULL OR expires_at > ?) — already filters expired rows at the SQL level (unlike MemoryStorage).

all_keys() -> List[str]

Same expiry filter, without the tenant predicate.

total_size_bytes() -> int

SELECT SUM(LENGTH(payload)) from zerda.cache_entries — measures the serialized JSON payload size, not Python object size; returns 0 if the table is empty (row[0] or 0 guards against NULL).

flush_tenant(tenant_id: str) -> int

DELETE from zerda.cache_entries WHERE tenant_id=?; returns cur.rowcount.

purge_expired() -> int

[!INFO] Not part of the BaseStorage contract purge_expired() is a SQLiteStorage-specific extension, not declared on BaseStorage and not called anywhere else in the analyzed source. It is available for callers that want to explicitly sweep expired rows (DELETE ... WHERE expires_at IS NOT NULL AND expires_at < ?) rather than relying on lazy per-key expiry. Returns the number of rows removed.

close() -> None

Closes the current thread's connection, if one exists, and clears the thread-local reference. Because connections are per-thread, close() called from a different thread than the one that created a connection will not close that other thread's connection.

Thread Safety

Thread-safe in the sense that each thread gets its own SQLite connection (check_same_thread=False is used defensively, but the thread-local pattern means connections are not actually shared across threads in practice). SQLite's own WAL mode provides safe concurrent readers with a single writer at a time; concurrent writers from different threads may see SQLITE_BUSY retried internally up to the 10-second timeout.

Side Effects

• File operations: creates/opens db_path, appends WAL/SHM files if wal=True.
• Logging: logger.info on initialization; logger.error on deserialization failure.

Best Practices

• Prefer this backend when you need durability without operating a separate database server.
• Call close() (or use CacheManager.close()/the context-manager form) during graceful shutdown to release the connection, especially before deleting or moving the database file.

Common Mistakes

• Sharing a SQLiteStorage instance across processes (not threads) expecting them to see each other's writes immediately — SQLite handles this at the file level, but WAL mode has its own visibility semantics; don't assume the same real-time consistency as an in-memory store.
• Relying on total_size_bytes() as an exact estimate of on-disk file size — it reflects serialized payload length only, not SQLite's page overhead, indexes, or WAL file size.

Related APIs

• BaseStorage — interface implemented
• RedisStorage — uses SQLiteStorage as its fallback
• build_storage() — constructs this class for StorageBackend.SQLITE