Building a Cache Layer with SQLite Instead of Redis — WAL, TTL, and Connection Separation in a Single File with bun:sqlite

When you first add Redis to a project, at some point it becomes second nature. The concept of caching itself starts to feel synonymous with Redis. I was the same way. Then I discovered that the cost of maintaining a Redis instance on a solo project was higher than the server cost itself, and I started to question whether it really made sense for an API handling hundreds of requests per second on a single server to query Redis over the network every single time.

Starting with Bun 1.2, bun:sqlite is now built directly into the runtime, which opened up another option for this dilemma. In this article, I'll build a single-file SQLite cache from scratch without any external infrastructure, and honestly document the pitfalls I encountered along the way.

Why SQLite Has Re-emerged as a Cache Candidate Now

Runtimes Have Started Bundling Drivers Directly

Honestly, just a few years ago, saying "SQLite for cache?" would get a lukewarm response. If you've ever spent an hour trying to install better-sqlite3 only to have node-gyp fail to build, you'd naturally give up and move on.

But as of 2026, the situation has changed. Bun 1.2 ships bun:sqlite as a first-class citizen, and Node.js has experimentally introduced node:sqlite since version 22.5.0. Runtime-bundled SQLite has become a common choice in server-side JavaScript.

Bun's bun:sqlite is implemented in Zig, and its API is inspired by better-sqlite3 in a synchronous style, so anyone familiar with that library can use it with almost no learning curve. For accurate performance numbers, I recommend checking the Bun official SQLite documentation in your own environment. Benchmarks vary by workload, so measuring in your own app is far more useful than reading someone else's numbers.

Why Redis-free Architectures Are Growing

There are clear reasons why more projects are choosing SQLite-based local caching without an external Redis.

This doesn't mean replacing Redis entirely. Redis is still the right choice when you need shared caching across multiple servers or workloads with extreme write contention. This article assumes "single server or single-machine multi-process" scenarios.

Architecture Design — Three Core Decisions

Before diving into code, there are three things you must understand from a design perspective.

Decision 1: Why You Must Enable WAL Mode

In SQLite's default journal mode (DELETE), reads are blocked while writes are in progress. For a workload like caching, where reads vastly outnumber writes, this is fatal.

WAL (Write-Ahead Logging) mode writes changes to a separate .db-wal file first, then merges them into the main DB file later. The key point is that readers do not block writers, and writers do not block readers. It supports multiple concurrent read transactions and a single write transaction.

The checkpoint interval can be adjusted with the wal_autocheckpoint PRAGMA, and the default is 1000 pages (not a fixed behavior — you can change it anytime). Enabling it is just one line.

db.run("PRAGMA journal_mode = WAL");

Enabling WAL isn't the end of it. When multiple processes or connections try to write simultaneously, SQLite returns SQLITE_BUSY immediately by default. To absorb this with retries, you need to set PRAGMA busy_timeout as well. We'll include this in createCacheDB below.

Decision 2: Separate Write and Read Connections

This was something I found confusing at first too. SQLite only allows one concurrent write transaction. When multiple connections all try to write, SQLITE_BUSY errors become frequent.

As Evan Schwartz's article explains well, the optimal pattern is a single Write connection + separate Read connection. Writes are serialized at the application level, and reads are handled on a separate connection.

One thing worth clarifying: this is why I removed "connection pool" from the subtitle of this article. What's commonly called "connection pooling" involves maintaining multiple connections of the same role and lending/returning them — applying that directly to the write side in SQLite actually hurts performance. So this article approaches it as connection separation, not "pooling." A true pool with multiple read connections is possible, but given that we're using a synchronous API in a single-process event loop, the practical gain is minimal.

Decision 3: Handle TTL Expiration Through Queries, Not a Separate Timer

Rather than deleting expired entries with a background worker or setInterval, embedding the expiration condition in the query itself is much cleaner. SQLite's unixepoch() function makes this easy.

-- Auto-filter expired entries on lookup
SELECT value FROM cache 
WHERE key = ? AND (expires_at IS NULL OR expires_at > unixepoch());
 
-- Separate cleanup query (timing discussed later)
DELETE FROM cache WHERE expires_at <= unixepoch();

Implementation — Real Code Step by Step

Foundation: Database Initialization

// cache-db.ts
import { Database } from "bun:sqlite";
 
function createCacheDB(path: string = "./cache.db") {
  const db = new Database(path);
 
  db.run("PRAGMA journal_mode = WAL");
  db.run("PRAGMA synchronous = NORMAL");
  db.run("PRAGMA busy_timeout = 5000");
  db.run("PRAGMA cache_size = 10000");
  db.run("PRAGMA temp_store = MEMORY");
 
  db.run(`
    CREATE TABLE IF NOT EXISTS cache (
      key        TEXT PRIMARY KEY,
      value      TEXT NOT NULL,
      expires_at INTEGER,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    )
  `);
 
  db.run(`
    CREATE INDEX IF NOT EXISTS idx_cache_expires_at 
    ON cache(expires_at) WHERE expires_at IS NOT NULL
  `);
 
  return db;
}
 
export { createCacheDB };

PRAGMA synchronous = NORMAL is a cache-specific tradeoff. The last transaction may be lost on an OS crash or power failure, but for a cache that can simply be repopulated, this is acceptable. However, copying this setting to a general DB that stores source data could cause real data loss, so be careful.

busy_timeout = 5000 makes SQLite retry for up to 5 seconds instead of failing immediately when it can't acquire a lock. This significantly reduces SQLITE_BUSY exceptions in single-machine multi-process scenarios or brief write contention situations.

Separating Write/Read Connections and the Cache Class

The read connection is opened with readonly: true, which requires the file and schema to already exist. So inside the constructor, you must call createCacheDB() (the write connection) first to create the schema, then open the read connection.

// cache.ts
import { Database, Statement } from "bun:sqlite";
import { createCacheDB } from "./cache-db";
 
interface CacheOptions {
  ttl?: number;
}
 
class SQLiteCache {
  private writeDB: Database;
  private readDB: Database;
 
  private stmtGet: Statement;
  private stmtSet: Statement;
  private stmtDelete: Statement;
  private stmtCleanup: Statement;
  private stmtCheckpoint: Statement;
 
  private writesSinceCleanup = 0;
  private readonly cleanupEvery: number;
 
  constructor(path: string = "./cache.db", cleanupEvery = 1000) {
    this.writeDB = createCacheDB(path);
    this.readDB = new Database(path, { readonly: true });
    this.cleanupEvery = cleanupEvery;
 
    this.stmtGet = this.readDB.prepare(`
      SELECT value FROM cache
      WHERE key = ? AND (expires_at IS NULL OR expires_at > unixepoch())
    `);
 
    this.stmtSet = this.writeDB.prepare(`
      INSERT OR REPLACE INTO cache (key, value, expires_at)
      VALUES (?, ?, ?)
    `);
 
    this.stmtDelete = this.writeDB.prepare(`
      DELETE FROM cache WHERE key = ?
    `);
 
    this.stmtCleanup = this.writeDB.prepare(`
      DELETE FROM cache 
      WHERE expires_at IS NOT NULL AND expires_at <= unixepoch()
    `);
 
    this.stmtCheckpoint = this.writeDB.prepare("PRAGMA wal_checkpoint(TRUNCATE)");
  }
 
  get<T = unknown>(key: string): T | null {
    const row = this.stmtGet.get(key) as { value: string } | null;
    if (!row) return null;
    return JSON.parse(row.value) as T;
  }
 
  set(key: string, value: unknown, options: CacheOptions = {}): void {
    const expiresAt = options.ttl
      ? Math.floor(Date.now() / 1000) + options.ttl
      : null;
    this.stmtSet.run(key, JSON.stringify(value), expiresAt);
 
    this.writesSinceCleanup++;
    if (this.writesSinceCleanup >= this.cleanupEvery) {
      this.cleanup();
      this.writesSinceCleanup = 0;
    }
  }
 
  delete(key: string): void {
    this.stmtDelete.run(key);
  }
 
  cleanup(): void {
    this.stmtCleanup.run();
  }
 
  checkpoint(): void {
    this.stmtCheckpoint.run();
  }
 
  close(): void {
    this.writeDB.close();
    this.readDB.close();
  }
}
 
export { SQLiteCache };

For prepared statement field types, I use Statement exported directly from bun:sqlite. It gives a clearer picture of the API structure than workarounds like ReturnType<Database["prepare"]>.

I also changed where cleanup() is called from the initial draft. The original draft called cleanup() after every set(), which means DELETE FROM cache WHERE expires_at <= unixepoch() would run on every cache miss. As the table grows, this DELETE shows up directly in response latency. Here I added a counter to run cleanup only once every N writes, and if you prefer, you can fully separate it with a time-based approach as shown below.

// For complete separation using a dedicated interval
setInterval(() => cache.cleanup(), 5 * 60 * 1000);

The Get-or-Set Pattern: Caching API Responses

This is the most commonly used pattern in practice. On a cache hit, return immediately; on a miss, perform the actual work and store the result.

import { SQLiteCache } from "./cache";
 
const cache = new SQLiteCache();
 
async function getCachedUserProfile(userId: string) {
  const cacheKey = `user:profile:${userId}`;
 
  const cached = cache.get<UserProfile>(cacheKey);
  if (cached) return cached;
 
  const profile = await fetchUserFromDB(userId);
  cache.set(cacheKey, profile, { ttl: 300 });
  return profile;
}

Caching HTTP Responses with Hono Middleware

// middleware/cache.ts
import { Context, Next } from "hono";
import type { StatusCode } from "hono/utils/http-status";
import { SQLiteCache } from "../cache";
 
const cache = new SQLiteCache();
 
export function httpCache(ttlSeconds: number) {
  return async (c: Context, next: Next) => {
    if (c.req.method !== "GET") {
      return next();
    }
 
    const cacheKey = `http:${c.req.method}:${c.req.url}`;
 
    const cached = cache.get<{ body: string; contentType: string }>(cacheKey);
    if (cached) {
      return c.body(cached.body, 200, {
        "Content-Type": cached.contentType,
        "X-Cache": "HIT",
      });
    }
 
    await next();
 
    if (c.res.status >= 200 && c.res.status < 300) {
      const body = await c.res.clone().text();
      const contentType = c.res.headers.get("Content-Type") ?? "text/plain";
 
      cache.set(cacheKey, { body, contentType }, { ttl: ttlSeconds });
 
      return c.body(body, c.res.status as StatusCode, {
        "Content-Type": contentType,
        "X-Cache": "MISS",
      });
    }
  };
}

One thing to note here is c.res.status as StatusCode. The original draft had it force-cast as as 200, which would distort actual response status codes like 201, 204, or 206 to all appear as 200. Using Hono's StatusCode type preserves the actual status code.

// app.ts
import { Hono } from "hono";
import { httpCache } from "./middleware/cache";
 
const app = new Hono();
 
app.get("/api/products", httpCache(60), async (c) => {
  const products = await fetchProductsFromDB();
  return c.json(products);
});

Full Request Flow Visualization

Managing WAL Checkpoints

On long-running servers, the WAL file can keep growing. Since we already exposed a public checkpoint() method on the class, you can call it from outside. The approach in the original draft of accessing the private field via bracket notation (cache["writeDB"]) breaks encapsulation and should be avoided.

setInterval(() => {
  try {
    cache.checkpoint();
  } catch (err) {
    console.error("WAL checkpoint failed:", err);
  }
}, 30 * 60 * 1000);

Tradeoffs — An Honest Summary

Pros and Cons Comparison

Two Common Mistakes in Practice

Mistake 1: Grouping Read/Write Connections in the Same Pool

If you create multiple connections with new Database(path) and manage them as a pool, SQLITE_BUSY will fire when all those connections try to write. Writes must be serialized through a single connection, with a separate connection dedicated to reads.

Mistake 2: Running Heavy Queries on the Main Thread with a Synchronous API

bun:sqlite is a synchronous API. This is fine for lightweight, fast cache lookups, but running complex aggregation queries or large cleanup operations frequently will block the event loop. Heavy cleanup work should be decoupled from the request handling flow.

These Situations Call for Redis Instead

Signals and Checklist for Switching to Redis

Even if you start with a SQLite cache, the time will come to move to Redis. It's better to make that call based on the following signals rather than gut feeling.

• When you need 2 or more app instances and the cache hit rate starts dropping below half per instance
• When SQLITE_BUSY retries frequently fail even within busy_timeout, and the write queue starts visibly backing up
• When cache invalidation needs to be broadcast simultaneously across multiple processes (pub/sub required)
• When the size of cached data starts straining local disk capacity or backup policies

The migration itself isn't that hard if you design your interface well. Wrapping the cache in an interface with three methods — get / set / delete — means you can swap the implementation from SQLiteCache to RedisCache with minimal effort. The harder part is deciding how to maintain data consistency during the switch (whether to start with an empty cache or migrate data), and that depends on your service's characteristics.

Closing

The three decisions — WAL mode, connection separation, query-based TTL — don't work in isolation; each one depends on the others. Without WAL mode, separating Read/Write connections doesn't translate into a performance gain, and handling TTL in a separate worker reintroduces write contention, blurring the benefit of connection separation. Only when all three decisions are made together do you get a cache layer solid enough to run in production without external dependencies.

Redis is always there when you need it. The key insight is that you don't need it from the start — and there's a real, meaningful window where things run just fine without it. That's what I confirmed by building this myself.

References

• Bun Official SQLite Documentation
• Bun Official Blog
• PSA: Your SQLite Connection Pool Might Be Ruining Your Write Performance — Evan Schwartz
• SQLite Official WAL Documentation
• SQLite PRAGMA Reference
• Node.js node:sqlite Documentation
• Hono Official Documentation