SQLite as an Application Cache: Patterns, TTLs, and Pitfalls
A cache does not need to be a network service. Sometimes the simplest useful cache is a local database file that survives a process restart, supports indexed lookups, and can be rebuilt when its contents become obsolete. SQLite can provide that foundation, but calling a database a cache does not automatically give it expiration, eviction, invalidation, or graceful failure behavior.
The architectural question is not whether SQLite is fast enough in the abstract. It is whether the application benefits from a local, persistent, disposable copy of data, and whether that copy can remain local. SQLite's own appropriate-use guidance emphasizes local application storage rather than a shared database reached directly over a network. That distinction should frame the design.
Choose a cache boundary before a database
Consider a service that repeatedly downloads public product metadata, transforms it into a normalized document, and serves it to local workers. Regenerating that document costs an upstream request and CPU time. It can be cached because its authoritative source exists elsewhere.
A different service computes a user's authorization decision. Caching that result can be dangerous if a key omits the user's identity, the tenant, the effective permissions, or the source policy version. SQLite will reliably retrieve the stored bytes; it cannot decide whether those bytes remain safe to serve.
A good cache candidate has a defined origin, a tolerable freshness window, a replacement strategy, and a bounded failure impact. A poor candidate is a value that must be globally current, can never be regenerated, or has no trustworthy invalidation rule. Persistence is a property of the cache, not permission to turn it into a second source of truth.
Local SQLite caches can be useful for command-line tools, offline-first applications, API response snapshots, intermediary computation, data enrichment, and per-host job workers. They become less compelling when many application replicas require identical cache contents or cross-machine atomic updates.
Design a small explicit schema
An application-specific cache can start with a single table:
CREATE TABLE IF NOT EXISTS cache_entries (
cache_key TEXT PRIMARY KEY,
value BLOB NOT NULL,
stored_at INTEGER NOT NULL,
expires_at INTEGER NOT NULL,
source_version TEXT
);
CREATE INDEX IF NOT EXISTS cache_expiry_idx
ON cache_entries(expires_at);
The timestamps are integer Unix seconds, chosen for straightforward comparisons. SQLite's date and time documentation explains its supported timestamp representations; it does not require a dedicated timestamp column type.
A key should identify not just a function name, but all inputs that affect its output: endpoint, normalized parameters, locale, tenant, authorization context, representation format, and schema or transformation version. A canonical serialized structure hashed into a key is often easier to maintain than loosely concatenated strings.
The stored value can be canonical JSON bytes, a compact binary representation, or an application-specific encoding. Do not deserialize arbitrary pickled data from untrusted or writable cache files. Establish a payload size limit, codec version, and a safe response to decode failure.
SQLite's UPSERT syntax supports a straightforward replacement when a key already exists:
INSERT INTO cache_entries
(cache_key, value, stored_at, expires_at, source_version)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT(cache_key) DO UPDATE SET
value = excluded.value,
stored_at = excluded.stored_at,
expires_at = excluded.expires_at,
source_version = excluded.source_version;
Parameter binding is important because key and value material may include user input. Treat the database file itself as a controlled application artifact, with filesystem permissions appropriate for any sensitive cache data.
Read-through flow: misses are expected
A cache-aside read follows a deliberately boring sequence:
- Calculate the canonical cache key.
- Read an entry whose expiration is still in the future.
- If there is a valid entry, decode and return it.
- Otherwise fetch or compute the authoritative value outside a write transaction.
- Validate and store the result with an explicit expiry.
- Return the result even if the cache write failed, provided the authoritative operation succeeded.
The lookup predicate should enforce expiration, not just test for a matching row:
SELECT value
FROM cache_entries
WHERE cache_key = ?
AND expires_at > ?;
Pass the current Unix time as a parameter. A stale record is a miss even if it remains on disk. This lets cleanup happen asynchronously without making freshness dependent on maintenance timing.
Keep cache reads and writes conceptually separate from the source of truth. A disk-full error, a malformed cached object, or a transient SQLite busy timeout should normally be treated as a cache failure, not as evidence that the requested application data is unavailable. Conversely, a failed upstream fetch should not automatically justify serving stale data: whether stale responses are permitted is a domain decision.
An implementation sketch in Python
The following simplified Python example demonstrates the central contract. It is intentionally small: it does not implement a production connection pool, distributed invalidation, or full storage accounting. It assumes that cache keys and values have already been canonically encoded by the caller.
import sqlite3
import time
from contextlib import closing
from pathlib import Path
DB_PATH = Path("/var/cache/myapp/data.sqlite3")
def connect():
db = sqlite3.connect(DB_PATH, timeout=2.0)
db.execute("PRAGMA busy_timeout = 2000")
return db
def get_cached(key: str) -> bytes | None:
now = int(time.time())
with closing(connect()) as db:
row = db.execute(
"SELECT value FROM cache_entries "
"WHERE cache_key = ? AND expires_at > ?",
(key, now),
).fetchone()
return row[0] if row else None
def put_cached(key: str, value: bytes, ttl_seconds: int):
if ttl_seconds <= 0:
return
now = int(time.time())
with closing(connect()) as db:
with db:
db.execute(
"INSERT INTO cache_entries "
"(cache_key, value, stored_at, expires_at) "
"VALUES (?, ?, ?, ?) "
"ON CONFLICT(cache_key) DO UPDATE SET "
"value = excluded.value, "
"stored_at = excluded.stored_at, "
"expires_at = excluded.expires_at",
(key, value, now, now + ttl_seconds),
)
The schema shown earlier includes an optional source_version field; this abbreviated write leaves it NULL. Initialize the schema before serving requests and select the journal and synchronous modes explicitly. The sample deliberately opens short-lived connections to avoid concealing transaction lifetime; production code may use a pool if that pool has clear ownership and locking rules.
In the calling layer, wrap cache-specific read/write errors separately from errors raised by the authoritative fetch. A failed cache lookup should normally trigger a source fetch, but do not turn every exception into a miss: application permission errors, invalid query construction, and corrupted deployment configuration still merit direct attention. The policy boundary belongs in application code, not a blanket catch-all.
The Python sqlite3 documentation provides details about transactions, connection use, and parameter binding. Validate the SQL and driver behavior with integration tests on the SQLite version actually shipped.
Expiration is not eviction
Time-to-live (TTL) describes how long an entry is eligible to be served. Eviction limits how much storage the cache is allowed to consume. These are not interchangeable.
An expired row can remain stored indefinitely until an explicit deletion or replacement. A periodic cleaner can delete expired entries in manageable batches rather than holding a write transaction across a large table sweep. Capacity enforcement might remove the oldest stored entries, maintain a byte budget, or use a library with an established eviction policy.
The DiskCache tutorial documents a practical trade-off: least-recently-used and least-frequently-used policies update access metadata on reads. Those additional writes may be counterproductive for a read-heavy SQLite cache. An oldest-stored-first policy is less sophisticated, but can avoid turning cache hits into write traffic.
Do not assume that deleting a row immediately returns the same number of bytes to the operating system. SQLite page reuse, checkpointing, auto-vacuum configuration, and file compaction affect physical storage. Decide whether the goal is a bounded number of entries, bounded logical payload bytes, or a strict filesystem quota; they require different enforcement.
Bound storage without turning housekeeping into an outage
A simple cleaner can delete expired entries in short transactions, for example by selecting a limited batch of expired keys and deleting those keys. Repeat until an iteration finds no expired entries, then sleep or wait for the next maintenance interval. A single unbounded DELETE may monopolize the writer lock just when the application needs it.
DELETE FROM cache_entries
WHERE cache_key IN (
SELECT cache_key
FROM cache_entries
WHERE expires_at <= ?
ORDER BY expires_at
LIMIT 200
);
This is an illustrative batch size, not a universal tuning recommendation. Test it with real payload sizes and concurrent traffic. If the same SQLite file supports other application tables, cache cleanup must not compromise those higher-value workloads. A separate dedicated file makes the cache easier to discard and makes storage policy less likely to affect authoritative tables.
A hard capacity bound needs a definition. Counting rows ignores payload sizes. Summing payload byte lengths ignores SQLite page overhead and index pages. A filesystem quota can constrain consumption but may cause writes and checkpoints to fail abruptly. At minimum, record the size of the main database, WAL, and adjacent cache objects and trigger cleanup before the storage allocation is exhausted.
Updating an access counter for every hit may appear to improve eviction quality, but it changes the concurrency profile: a workload that was almost entirely reads now queues many writes behind the single SQLite writer. If the cache hit rate is high and objects are similarly valuable, an oldest-stored policy may be the better engineering trade, not because it predicts future hits more accurately but because it causes less bookkeeping.
WAL improves concurrency but does not make SQLite a cache server
Write-ahead logging (WAL) allows readers to continue while another connection commits changes. SQLite's WAL documentation is explicit that only one writer can proceed at a time. The benefit is concurrent reading and writing, not unlimited write throughput.
A plausible local configuration for a truly rebuildable cache is:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 2000;
These are design choices, not magic performance switches. The PRAGMA reference states that WAL with synchronous=NORMAL remains consistent but may lose recently committed transactions after a power failure. That trade may be acceptable for reproducible cache entries, not for authoritative state. FULL provides stronger power-loss durability.
A busy timeout gives a contending connection some opportunity to wait instead of failing immediately. It cannot fix transactions that hold locks too long, a saturated write workload, or unsafe filesystem locking. Network requests and expensive serialization must stay outside SQLite write transactions.
WAL also introduces checkpointing. A long-lived reader can hold a snapshot that prevents a checkpoint from advancing completely. If writers are active, the WAL file can grow beyond what an operator expects. Track transaction duration, WAL file footprint, and repeated busy errors, not just average SELECT latency.
The 2026 version check matters
In March 2026, SQLite's maintainers documented a rare race involving the WAL reset path under multiple connections writing or checkpointing concurrently. The project's WAL documentation describes the affected versions and the narrow triggering conditions. The fix arrived in 3.51.3, with selected backports for older release lines; the SQLite release news records subsequent releases, including 3.54.1 on October 16, 2026.
The practical action is to inspect the version of the SQLite library linked into the actual application runtime. A command-line sqlite3 binary can have a different version from the SQLite library embedded in Python, a mobile application, or another executable. For a Python service, inspect sqlite3.sqlite_version at runtime and verify that the deployed build includes the relevant fix.
This is a maintenance and risk-management point, not evidence that ordinary WAL caches routinely corrupt. The SQLite project characterizes the original race as unusual and difficult to trigger without targeted testing.
Prevent a miss storm and define stale behavior
Imagine a local cache whose popular entry expires while many requests arrive together. Each request sees a miss and independently fetches the origin. This is a cache stampede: the cache can increase downstream load at precisely the moment it is least useful.
A common application-level mitigation is single-flight refresh. For a given key, one caller performs the authoritative fetch while the others await the result. This can be an in-memory dictionary of in-flight futures within one process. It does not coordinate independent processes or machines; if that matters, use a separate coordination service or accept a bounded number of duplicate refreshes.
A further option is to distinguish a fresh-until deadline from an optional stale-until deadline. After normal expiry, an entry might be served temporarily while a refresh runs, but only for use cases where stale output is explicitly safe. Public catalog data is different from permission decisions, inventory commitments, or security revocations. A stale-while-revalidate mode must have an upper bound and an observable flag, rather than silently serving arbitrarily old content.
Negative caching also needs deliberate rules. Caching a true "not found" response can avoid repetitive lookups, but transient network errors and rate-limit responses are not the same as a stable absence. Choose different, usually shorter expiration windows for negative results. Cache keys must distinguish a missing resource in one tenant from another tenant's view of it.
Jittering expiration across entries can spread refresh work when large batches are inserted at once. It does not solve all stampedes because individually popular keys can still attract simultaneous misses. Combine expiration policy with concurrency limits and origin protection.
Failure recovery should be ordinary
The cache should be replaceable without an incident-response exercise. Define what happens after a missing file, database busy error, truncated payload, unexpected schema version, permission change, disk exhaustion, or integrity failure.
An application can maintain a separate cache schema version and create a replacement database in a safe application-owned path when migration is impractical. It should avoid deleting arbitrary paths or silently swallowing every database exception. A failed cache and a failed authoritative data source are different operational states and deserve different metrics.
A startup cache warm-up is an optimization. It is not a correctness requirement. After an empty cache, systems should continue operating through ordinary misses while rate limiting or coalescing origin requests to avoid a burst.
Security and correctness at the cache edge
Every cached value inherits the security classification of the original data. A local file can outlive a session or process restart and may be included accidentally in support bundles, container layers, crash diagnostics, or unrestricted backups. If it contains customer information, consider filesystem encryption, application-level encryption, access-control permissions, retention obligations, and safe deletion practices.
The key design must prevent cross-user and cross-tenant collisions. A hash of just the request URL can leak a response when authorization or request headers affect the result. Include relevant identity and policy inputs, or refuse to cache the response. A cache layer should not retain authentication headers or secrets merely because a convenient HTTP caching library can serialize complete requests.
Do not trust cached bytes as valid structured data forever. A code deployment can change response schemas while older entries remain. Serialize a format version or namespace the cache key by deployment schema. On decode error, invalidate the entry and fetch again if the origin is available; avoid silently coercing unknown or privileged fields into new representations.
Finally, separate the cache's file path from source repositories, application secrets, and durable database volumes. A cache that is easy to delete must also be hard to mistake for essential customer data.
Multi-instance behavior is where the boundary becomes visible
SQLite does not make per-instance caches consistent. If replicas A and B maintain local files, their contents expire and refresh independently. That can be acceptable for public datasets or expensive deterministic transformations, but not for data whose freshness and invalidation must be globally coordinated.
The SQLite project cautions against directly sharing database files across machines on network filesystems; see SQLite Over a Network. File locking behavior and network latency undermine an architecture that expects a host-local embedded database.
For multiple hosts requiring coherent shared cache state, a networked service, database-backed coordination mechanism, or explicit invalidation bus may be simpler than engineering distributed semantics around local SQLite files. A separate Redis-type cache may suit short-lived shared data, although it adds its own availability and eviction concerns.
Learn from existing SQLite-backed cache libraries
Before writing a cache layer, examine tools that already expose the missing semantics.
Python's DiskCache combines disk-backed entries with expiration, culling, size limits, and several eviction policies. The documentation also makes the trade-offs observable: eviction can be lazy, and updating recency/frequency has a write cost.
Requests-Cache's SQLite backend demonstrates another concrete use: HTTP response caching with an embedded store and options for WAL, lock waiting, and expiration. Neither library's existence proves that SQLite will be the fastest option in a particular workload. They show that the cache-specific policy layer is meaningful work, not a schema footnote.
A deployment and test checklist
A single-service host, a developer laptop, and a replicated container fleet make different assumptions about storage lifetime. A developer tool can use the platform-specific application cache directory and survive restarts. A container may have an ephemeral writable layer that disappears after replacement. Persisting the cache with a mounted volume helps warm starts, but introduces lifecycle, ownership, and locking considerations. Do not share the same database file across hosts by mounting a network drive.
Run tests that cover the failure boundary, not merely a successful SELECT. Delete the cache file before startup; revoke write permission; simulate a busy writer; fill or constrain the disk; insert corrupted payloads; change the codec version; make the authoritative origin unavailable; run two processes against the same local file; and observe what happens when the cache is cold after deployment.
Use a representative load generator to compare at least three cases: no cache, a warm SQLite cache, and a cold or heavily expiring SQLite cache. Report end-to-end request latency distributions, upstream request rate, bytes retained, write errors, checkpoint behavior, and CPU overhead from serialization. Cache hit percentage alone is not a business outcome, and microbenchmarks of SELECT latency ignore the cost of restoring an unusable cache.
For operations, define signals for lookup hits and misses, stale-entry observations, refresh success and failure, busy-timeout exceptions, eviction volume, total database-plus-WAL size, and the latency of the authoritative request. Record which failures are safe cache misses and which indicate a correctness or security problem.
Decide with a workload, not an ideology
Measure first the number of avoided upstream requests and recomputations; then measure hit rate, p95 and p99 end-to-end latency, write contention, database and WAL size, cleanup duration, origin load on cold starts, and the cost of losing the cache during recovery. Include authorization correctness and stale-data tolerance in acceptance criteria.
Use SQLite when local persistence and indexed queries are valuable, writes are manageable, and every stored value is safely recoverable. Use an in-memory map when process-lifetime caching is sufficient. Use a shared cache when replicas need shared invalidation, centralized admission, or cross-host coordination.
The successful design is one where an empty SQLite file is a routine operating condition. If losing the file would lose the business, the application has probably built a database of record and called it a cache.