Bun 1.2 내장 SQLite로 테넌트별 DB 파일을 분리하고 WAL과 커넥션 풀을 설계하기

data/
├── central.db            # 테넌트 목록, 스키마 버전 관리
└── tenants/
    ├── org-abc.db        # 테넌트별 독립 파일
    ├── org-abc.db-wal    # WAL 파일 (자동 생성·관리됨)
    ├── org-def.db
    └── org-def.db-wal

PRAGMA journal_mode = WAL;        -- WAL 모드 활성화
PRAGMA synchronous = NORMAL;      -- 성능과 내구성의 균형
PRAGMA busy_timeout = 5000;       -- 잠금 대기 최대 5초 (ms)
PRAGMA wal_autocheckpoint = 1000; -- 1000 페이지마다 자동 체크포인트 (기본값)
PRAGMA cache_size = -64000;       -- 연결당 64MB 캐시

// tenant-db-pool.ts
import { Database } from "bun:sqlite";
import { mkdirSync } from "fs";
 
const PRAGMA_WRITER = `
  PRAGMA journal_mode = WAL;
  PRAGMA synchronous = NORMAL;
  PRAGMA busy_timeout = 5000;
  PRAGMA wal_autocheckpoint = 1000;
  PRAGMA cache_size = -64000;
`;
 
const PRAGMA_READER = `
  PRAGMA busy_timeout = 5000;
  PRAGMA cache_size = -64000;
`;
 
function applySchema(db: Database): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      email TEXT NOT NULL UNIQUE,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );
    CREATE TABLE IF NOT EXISTS events (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      type TEXT NOT NULL,
      data TEXT,
      created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );
    CREATE INDEX IF NOT EXISTS idx_events_type ON events (type);
  `);
}
 
interface TenantConnections {
  writer: Database;
  readers: Database[];
  nextReader: number;
}
 
export class TenantDBPool {
  private connections = new Map<string, TenantConnections>();
  private readonly dbDir: string;
  private readonly maxTenants: number;
  private readonly readerCount: number;
 
  constructor(options: {
    dbDir: string;
    maxTenants?: number;
    readerCount?: number;
  }) {
    this.dbDir = options.dbDir;
    this.maxTenants = options.maxTenants ?? 100;
    this.readerCount = options.readerCount ?? 3;
    mkdirSync(this.dbDir, { recursive: true });
  }
 
  private openTenant(tenantId: string): TenantConnections {
    const path = `${this.dbDir}/${tenantId}.db`;
 
    const writer = new Database(path, { create: true });
    writer.exec(PRAGMA_WRITER);
    applySchema(writer);
 
    const readers = Array.from({ length: this.readerCount }, () => {
      const r = new Database(path, { readonly: true });
      r.exec(PRAGMA_READER);
      return r;
    });
 
    return { writer, readers, nextReader: 0 };
  }
 
  private getConnections(tenantId: string): TenantConnections {
    if (this.connections.has(tenantId)) {
      const conns = this.connections.get(tenantId)!;
      this.connections.delete(tenantId);
      this.connections.set(tenantId, conns);
      return conns;
    }
 
    if (this.connections.size >= this.maxTenants) {
      const oldestKey = this.connections.keys().next().value!;
      this.closeTenant(oldestKey);
    }
 
    const conns = this.openTenant(tenantId);
    this.connections.set(tenantId, conns);
    return conns;
  }
 
  getWriter(tenantId: string): Database {
    return this.getConnections(tenantId).writer;
  }
 
  getReader(tenantId: string): Database {
    const conns = this.getConnections(tenantId);
    const reader = conns.readers[conns.nextReader % conns.readers.length];
    conns.nextReader++;
    return reader;
  }
 
  closeTenant(tenantId: string): void {
    const conns = this.connections.get(tenantId);
    if (!conns) return;
    conns.writer.close();
    conns.readers.forEach((r) => r.close());
    this.connections.delete(tenantId);
  }
 
  closeAll(): void {
    for (const tenantId of [...this.connections.keys()]) {
      this.closeTenant(tenantId);
    }
  }
}

// app.ts
import { TenantDBPool } from "./tenant-db-pool";
 
const pool = new TenantDBPool({
  dbDir: "./data/tenants",
  maxTenants: 200,
  readerCount: 3,
});
 
// 읽기 — reader 인스턴스 사용
const events = pool
  .getReader("org-abc")
  .query("SELECT * FROM events WHERE type = ? ORDER BY created_at DESC LIMIT 50")
  .all("login");
 
// 쓰기 — transaction 래퍼 필수
const writer = pool.getWriter("org-abc");
writer.transaction(() => {
  writer
    .query("INSERT INTO events (type, data) VALUES (?, ?)")
    .run("login", JSON.stringify({ userId: 42 }));
  writer
    .query("UPDATE users SET last_seen = ? WHERE id = ?")
    .run(Date.now(), 42);
})();

// migrate-all.ts
import { Database } from "bun:sqlite";
import { readdirSync } from "fs";
 
interface Migration {
  version: number;
  sql: string;
}
 
const MIGRATIONS: Migration[] = [
  { version: 1, sql: `ALTER TABLE events ADD COLUMN metadata TEXT;` },
];
 
function ensureMigrationTable(db: Database): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      version INTEGER PRIMARY KEY,
      applied_at INTEGER NOT NULL DEFAULT (unixepoch())
    );
  `);
}
 
function currentVersion(db: Database): number {
  const row = db
    .query("SELECT COALESCE(MAX(version), 0) AS v FROM schema_migrations")
    .get() as { v: number };
  return row.v;
}
 
function applyMigrations(db: Database): number[] {
  ensureMigrationTable(db);
  const from = currentVersion(db);
  const pending = MIGRATIONS.filter((m) => m.version > from);
  const applied: number[] = [];
 
  for (const m of pending) {
    db.transaction(() => {
      db.exec(m.sql);
      db.query("INSERT INTO schema_migrations (version) VALUES (?)").run(m.version);
    })();
    applied.push(m.version);
  }
  return applied;
}
 
const files = readdirSync("./data/tenants").filter((f) => f.endsWith(".db"));
 
for (const file of files) {
  const db = new Database(`./data/tenants/${file}`);
  db.exec("PRAGMA busy_timeout = 5000;");
  try {
    const applied = applyMigrations(db);
    console.log(`${file}: 적용 ${applied.length}건 (${applied.join(", ") || "없음"})`);
  } catch (err) {
    console.error(`실패: ${file}`, err);
  } finally {
    db.close();
  }
}

// 위험: 중간 실패 시 부분 적용 상태가 됩니다
writer.query("UPDATE accounts SET balance = ? WHERE id = ?").run(newBalance, id);
writer.query("INSERT INTO ledger ...").run(...);
 
// 안전: 원자성 보장됩니다
writer.transaction(() => {
  writer.query("UPDATE accounts SET balance = ? WHERE id = ?").run(newBalance, id);
  writer.query("INSERT INTO ledger ...").run(...);
})();

const TENANT_ID_PATTERN = /^[a-zA-Z0-9-]{1,64}$/;
 
function validateTenantId(tenantId: string): void {
  if (!TENANT_ID_PATTERN.test(tenantId)) {
    throw new Error("유효하지 않은 테넌트 ID");
  }
}
 
// 미들웨어 진입 지점에서 항상 먼저 실행
validateTenantId(req.tenantId);
const db = pool.getReader(req.tenantId);