SQLite Cache Schema

This schema provides a ready-to-use structure for caching different data types.

It efficiently stores and retrieves immutable binary and textual data, accessed by a textual key. Use the cache_blob and cache_text tables for this purpose.

Versioned:

For slowly changing data, use the cache_rev table. It is an append-only table that preserves every revision but is optimized to look up only the most recent one for a given key.

Versioned and paged:

HTTP APIs often returned chunked data in pages. When this data must be preserved exactly as-is—where pre-processing is undesirable or even forbidden—the cache needs to maintain its paged nature.

The cache_rev_paged table is designed for this. Like the cache_rev table, it appends each new revision of a record. It is optimized to quickly retrieve the most recent set of paged objects for a key. This requires all pages for a given key to share the same inserted_at timestamp.

SQLite is a serverless, file-based relational database that runs within your application's process. It is fast and requires zero configuration, unlike traditional client-server RDBMS. This makes it an ideal solution for a durable, on-disk application cache.

Key-value stores like Redis are the standard for most caching solutions. They are optimized for the key-based access patterns that caches typically require.

However, for (web) applications with low to medium concurrent users, SQLite is also a well-suited key-value store. These applications rarely hit SQLite's performance limits. The benefits are even greater if you already use SQLite, prefer simple infrastructure, and write concurrency is not your bottleneck.

-- stores immutable text values by key (e.g. HTML, JSON from HTTP APIs) CREATE TABLE IF NOT EXISTS cache_text ( id INTEGER PRIMARY KEY AUTOINCREMENT, key TEXT NOT NULL UNIQUE, inserted_at DATETIME NOT NULL, -- ISO 8601 in UTC data TEXT NOT NULL ); -- stores immutable binary data by key. (e.g. images, audio) CREATE TABLE IF NOT EXISTS cache_blob ( id INTEGER PRIMARY KEY AUTOINCREMENT, key TEXT NOT NULL UNIQUE, inserted_at DATETIME NOT NULL, -- ISO 8601 in UTC data BLOB NOT NULL ); -- stores versioned text values, preserving every revision while optimizing access for the latest one CREATE TABLE IF NOT EXISTS cache_rev ( key TEXT NOT NULL, inserted_at DATETIME NOT NULL, -- ISO 8601 in UTC data TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_cache_rev_key_inserted_at ON cache_rev (key, inserted_at DESC); -- stores versioned, paginated text data, preserves every revision of each page and is optimized for retrieving the latest complete set -- ideal for caching raw API responses that are returned page-by-page CREATE TABLE IF NOT EXISTS cache_rev_paged ( key TEXT NOT NULL, page INTEGER NOT NULL, -- used to group pages from a single fetch run -- requirement: -- different pages that have been fetched in one run must have the same timestamp -- despite fetching them one after the other and at slightly different times -- good enough approximation when minutes of difference do not matter inserted_at DATETIME NOT NULL, -- ISO 8601 in UTC data TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_cache_rev_paged_key_inserted_at_page ON cache_rev_paged (key, inserted_at DESC, page ASC); PRAGMA journal_mode = WAL; PRAGMA user_version = 1;