SPB Git

Repository Public

db.ts 4.0 KB 145 lines typescript

1import { DatabaseSync } from "node:sqlite";2import fs from "node:fs";3import path from "node:path";45/**6 * Accès SQLite (module intégré node:sqlite, aucune dépendance native).7 * Les données vivent HORS du dépôt : DATA_DIR (défaut ./data, ignoré par git).8 */9export type Db = DatabaseSync;1011const g = globalThis as unknown as { __phiDb?: { db: Db; file: string } };1213export function dataDir(): string {14  const dir = path.resolve(/*turbopackIgnore: true*/ process.env.DATA_DIR || path.join(/*turbopackIgnore: true*/ process.cwd(), "data"));15  fs.mkdirSync(dir, { recursive: true, mode: 0o700 });16  return dir;17}1819export function migrationsDir(): string {20  return path.join(/*turbopackIgnore: true*/ process.cwd(), "db", "migrations");21}2223function listMigrations(suffix: "up" | "down") {24  const dir = migrationsDir();25  return fs26    .readdirSync(dir)27    .filter((f) => f.endsWith(`.${suffix}.sql`))28    .sort()29    .map((f) => ({ id: f.replace(`.${suffix}.sql`, ""), file: path.join(dir, f) }));30}3132export function migrate(db: Db): string[] {33  db.exec(34    "CREATE TABLE IF NOT EXISTS schema_migrations (id TEXT PRIMARY KEY, applied_at INTEGER NOT NULL)",35  );36  const done = new Set(37    (db.prepare("SELECT id FROM schema_migrations").all() as { id: string }[]).map((r) => r.id),38  );39  const applied: string[] = [];40  for (const m of listMigrations("up")) {41    if (done.has(m.id)) continue;42    db.exec("BEGIN IMMEDIATE");43    try {44      db.exec(fs.readFileSync(m.file, "utf8"));45      db.prepare("INSERT INTO schema_migrations (id, applied_at) VALUES (?, ?)").run(46        m.id,47        Date.now(),48      );49      db.exec("COMMIT");50      applied.push(m.id);51    } catch (e) {52      db.exec("ROLLBACK");53      throw e;54    }55  }56  return applied;57}5859/** Annule la dernière migration appliquée (script `pnpm db:rollback`). */60export function rollbackLast(db: Db): string | null {61  const last = db.prepare("SELECT id FROM schema_migrations ORDER BY id DESC LIMIT 1").get() as62    | { id: string }63    | undefined;64  if (!last) return null;65  const down = listMigrations("down").find((m) => m.id === last.id);66  if (!down) throw new Error(`Migration ${last.id} : fichier .down.sql introuvable`);67  db.exec("BEGIN IMMEDIATE");68  try {69    db.exec(fs.readFileSync(down.file, "utf8"));70    db.prepare("DELETE FROM schema_migrations WHERE id = ?").run(last.id);71    db.exec("COMMIT");72  } catch (e) {73    db.exec("ROLLBACK");74    throw e;75  }76  return last.id;77}7879export function openDb(file: string): Db {80  const db = new DatabaseSync(file);81  db.exec("PRAGMA journal_mode = WAL");82  db.exec("PRAGMA foreign_keys = ON");83  db.exec("PRAGMA busy_timeout = 5000");84  db.exec("PRAGMA synchronous = NORMAL");85  return db;86}8788export function dbFile(): string {89  return process.env.DB_FILE || path.join(dataDir(), "phi.db");90}9192export function getDb(): Db {93  const file = dbFile();94  if (g.__phiDb && g.__phiDb.file === file) return g.__phiDb.db;95  const db = openDb(file);96  migrate(db);97  g.__phiDb = { db, file };98  return db;99}100101/** Pour les tests : ferme et oublie la connexion courante. */102export function closeDb() {103  if (g.__phiDb) {104    try {105      g.__phiDb.db.close();106    } catch {}107    g.__phiDb = undefined;108  }109}110111/** Transaction d'écriture (BEGIN IMMEDIATE : verrou d'écriture pris dès le début). */112export function tx<T>(db: Db, fn: () => T): T {113  db.exec("BEGIN IMMEDIATE");114  try {115    const out = fn();116    db.exec("COMMIT");117    return out;118  } catch (e) {119    try {120      db.exec("ROLLBACK");121    } catch {}122    throw e;123  }124}125126export function getSetting<T>(key: string, fallback: T): T {127  const row = getDb().prepare("SELECT value FROM settings WHERE key = ?").get(key) as128    | { value: string }129    | undefined;130  if (!row) return fallback;131  try {132    return JSON.parse(row.value) as T;133  } catch {134    return fallback;135  }136}137138export function setSetting(key: string, value: unknown) {139  getDb()140    .prepare(141      "INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",142    )143    .run(key, JSON.stringify(value));144}145
Files main
Menu

Theme