Keeping Third-Party API Responses in SQLite Gets You a Cache, an Offline Mode and a History
The first version of an API cache is a dictionary with a timeout. The second is Redis holding a JSON string under a key built from the URL. The third gets written after an incident, when somebody needs to know what the weather provider returned on Tuesday and the cache has already replaced it with Wednesday’s answer. Every app that depends on an outside API walks the same path: a cache, then a retry layer, then a debugging log, then a wish that it had kept the old responses.
One SQLite file can be all four. The version worth building is an embeddable library that sits between the application and its HTTP client. It stores every response as a row keyed by a normalized request, stores each distinct body once under its hash, and exposes chosen JSON fields as columns. The cache becomes one query: the newest row for this key that is still fresh. Offline mode is the same query with the freshness check removed. The debugging log is the table itself, and the history is every older row you haven’t pruned. Outside APIs carry hidden costs, and a local record of what the provider actually said is cheap insurance against some of them.
What Already Exists
Python’s requests-cache stores responses in SQLite by default, and it has shown for years that a file beats a server for this job. Hishel adds HTTP caching to httpx. Under both sit the HTTP specs (RFC 9111 for caching, RFC 9110 for conditional requests), which supply the vocabulary: Cache-Control, ETag, Vary, revalidation. Varnish and Squid do similar work as shared caches on the network, which is a different deployment. VCR-style cassettes (VCR for Ruby, Polly.js, Betamax, go-vcr) record real interactions to files so tests can replay them. Git’s object store supplies the content-addressing model: name a blob by its hash and identical content is stored once. The same caching idea at the edge, on one platform, is in caching a third-party response in a Cloudflare Pages Function.
What’s missing is the combination. A cache keeps the latest answer per key and discards the rest by design. Cassettes are written for tests and read back by a test runner, not by a person with a question. Neither gives you SQL over the bodies, and neither treats the pile of old responses as data. The gap is narrow: keep every distinct response, make the file queryable, and make offline a mode you choose instead of an accident of an expired TTL.
How the File Is Laid Out
Two tables carry most of the design. Bodies are stored by hash, and each request row points at one.
CREATE TABLE bodies (
sha256 BLOB PRIMARY KEY,
kind TEXT NOT NULL, -- 'jsonb' or 'zstd'
body BLOB NOT NULL
) WITHOUT ROWID;
CREATE TABLE requests (
key TEXT NOT NULL, -- method + normalized URL + Vary headers + identity hash
fetched_at INTEGER NOT NULL,
status INTEGER NOT NULL,
etag TEXT,
headers TEXT, -- JSON, response headers worth keeping
body_sha256 BLOB NOT NULL REFERENCES bodies(sha256),
PRIMARY KEY (key, fetched_at)
);
Key normalization decides whether the cache ever hits. Lowercase the host, drop default ports, and sort query parameters by name (keeping repeated ones in order), so ?b=2&a=1 and ?a=1&b=2 land on one key. Add the values of whatever request headers the response’s Vary names, plus a hash of the caller’s identity. The credential itself never goes in the file, only the hash.
Freshness follows RFC 9111 when the provider sends Cache-Control, with a per-host override for the many APIs that send nothing useful. stale-while-revalidate and stale-if-error (both from RFC 5861) become library settings: serve the stale row at once and refresh in the background, or serve it when the upstream answers with a 5xx. Revalidation sends If-None-Match with the stored ETag. A 304 inserts a new requests row pointing at the same body, so the table records every check and the check costs almost nothing.
Coalescing matters more than it sounds. Say fifty requests arrive in the same second for a key that just expired. Without coalescing all fifty go upstream, which is how a cache turns a rate limit into an outage. With it, the first call goes out and the other forty-nine wait on its result. In one process that’s a map from key to an in-flight call. Across processes it needs a lease row in the file, and that’s where 0.1 stops.
Offline mode is one flag: never touch the network, answer from the newest row for the key, mark the response stale, and raise a typed error when no row exists. The same file then works as a test fixture. Record a session against the real API once, replay it in CI with the flag on, and you get what a cassette gives you plus a file you can query. Chosen JSON fields then query like plain columns.
-- "responses" is a view joining requests to their decoded bodies
SELECT fetched_at, body ->> '$.current.temp_c' AS temp_c
FROM responses
WHERE key LIKE 'GET api.example.com/weather?id=123%'
AND body ->> '$.current.temp_c' > 30
ORDER BY fetched_at DESC;
SQL needs a decoded body, and that forces a choice. SQLite’s binary JSON format (JSONB, available since 3.45) makes field queries cheaper than re-parsing text, and an index on an expression, or a generated column (since 3.31), keeps a hot field fast. A compressed body is smaller but can’t be queried until it’s decoded. So the choice is per host: JSONB where you want SQL, compression where you only want the history.
The Hard Part Is Whose Response It Is
A cache that serves user A’s response to user B is a data breach with a hit rate. Anything fetched with credentials needs the caller’s identity in the key, Vary has to be honored, and the safe default is to refuse to cache a request that carries an Authorization header until the caller names an identity explicitly. Fail closed. The first test to write is two users hitting one URL and getting two rows.
Provider terms come second. Some mapping and data APIs limit how long you may keep responses, or whether you may store them at all. A library that quietly keeps everything forever is a liability, so retention is a per-host setting with a visible default, and the documentation should tell readers to check their provider’s terms before turning history on.
Invalidation is mostly TTL, because that’s all most providers offer. After your own write to a resource you can drop related reads by key prefix, but you have to know which ones. Writes are never cached; only GET and HEAD by default. Some read-only APIs take their query as a POST body, so those become an explicit per-route opt-in, with the body hashed into the key.
History grows. Content addressing saves space only when bodies are byte-identical, and many APIs put a timestamp inside the body, so nothing dedupes until the noise is stripped out before hashing (giving any API a history goes into that part). Retention then becomes a pruning job, such as the newest 20 rows per key plus one per day beyond that. Deleting rows doesn’t shrink the file, because SQLite reuses freed pages instead of shrinking the file; space comes back only after a VACUUM, or with auto-vacuum turned on. Large binary responses (images, PDFs, archives) shouldn’t be rows at all above a size you pick; write them to a directory named by hash and keep only the hash.
Clocks cause quiet bugs. TTL math uses wall-clock time, so a laptop that wakes with the wrong date sees entries as fresh for a year or stale forever. Compute freshness the way RFC 9111 does, counting the Age an upstream cache already added, and treat any row stamped in the future as stale.
What the First Version Leaves Out
Version 0.1 is a library for one language, whichever your own code runs on, plus a small CLI that runs SQL against the file. It does exact-match caching, content-addressed bodies, TTL with stale-while-revalidate, in-process coalescing and the offline flag. It refuses to become a shared network proxy: the file sits next to one application. It refuses semantic matching, where “similar” requests share an answer, because a wrong hit on API data is silent corruption.
MCP servers have the same problem with different headers. The July 2026 MCP revision added cache hints to tool lists and resource reads but not to tool calls, which is the gap a caching proxy for MCP fills, and the key and identity rules above carry over unchanged. The history half is a small version of something bigger, an embedded database built around change history.
Keep the answers. Delete them on purpose.