Redis 없이 SQLite로 캐시 레이어 만들기 — bun:sqlite로 WAL·TTL·커넥션 분리를 한 파일에

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

-- 조회 시 만료 자동 필터링
SELECT value FROM cache 
WHERE key = ? AND (expires_at IS NULL OR expires_at > unixepoch());
 
-- 별도 정리 쿼리 (호출 시점은 뒤에서 논의)
DELETE FROM cache WHERE expires_at <= unixepoch();

// 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 };

// 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 };

// 별도 인터벌로 완전 분리하고 싶다면
setInterval(() => cache.cleanup(), 5 * 60 * 1000);

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;
}

// 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",
      });
    }
  };
}

// 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);
});

setInterval(() => {
  try {
    cache.checkpoint();
  } catch (err) {
    console.error("WAL 체크포인트 실패:", err);
  }
}, 30 * 60 * 1000);