Repository Public
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