MCP Server Template

/** * SQLite database connection using sql.js (WebAssembly) * * Provides cross-platform SQLite without native dependencies. * Initializes schema on first connection and provides singleton access. */ import { mkdirSync, existsSync, readFileSync, writeFileSync } from 'node:fs'; import { dirname } from 'node:path'; import initSqlJs, { type Database, type SqlJsStatic } from 'sql.js'; import { getConfig } from '../config/index.js'; import { createLogger } from '../shared/logger.js'; import { CacheError } from '../shared/errors.js'; const logger = createLogger('db'); let db: Database | null = null; let sqlPromise: Promise<SqlJsStatic> | null = null; /** * Schema definition for the cache table */ const SCHEMA = ` CREATE TABLE IF NOT EXISTS cache ( key TEXT PRIMARY KEY, value TEXT NOT NULL, expires_at INTEGER NOT NULL, created_at INTEGER NOT NULL, hit_count INTEGER DEFAULT 0 ); CREATE INDEX IF NOT EXISTS idx_cache_expires ON cache(expires_at); `; /** * Initialize sql.js WebAssembly module (cached) */ async function getSql(): Promise<SqlJsStatic> { if (!sqlPromise) { sqlPromise = initSqlJs(); } return sqlPromise; } /** * Ensure the database directory exists */ function ensureDbDir(dbPath: string): void { const dir = dirname(dbPath); if (!existsSync(dir)) { mkdirSync(dir, { recursive: true }); logger.debug('Created database directory', { dir }); } } /** * Initialize database connection and schema */ export async function initDatabase(): Promise<Database> { if (db) { return db; } const config = getConfig(); const dbPath = config.dbPath; try { const SQL = await getSql(); ensureDbDir(dbPath); // Load existing database or create new one if (existsSync(dbPath)) { const buffer = readFileSync(dbPath); db = new SQL.Database(buffer); logger.info('Loaded existing database', { path: dbPath }); } else { db = new SQL.Database(); logger.info('Created new database', { path: dbPath }); } // Initialize schema db.run(SCHEMA); // Cleanup expired entries on startup const now = Date.now(); db.run('DELETE FROM cache WHERE expires_at < ?', [now]); logger.debug('Database initialized'); return db; } catch (error) { throw new CacheError('Failed to initialize database', error as Error); } } /** * Get the database instance (must call initDatabase first) */ export function getDatabase(): Database { if (!db) { throw new CacheError('Database not initialized. Call initDatabase() first.'); } return db; } /** * Persist database to disk */ export function saveDatabase(): void { if (!db) { return; } const config = getConfig(); try { const data = db.export(); const buffer = Buffer.from(data); writeFileSync(config.dbPath, buffer); logger.debug('Database saved to disk'); } catch (error) { logger.error('Failed to save database', { error: String(error) }); } } /** * Close database connection and save to disk */ export function closeDatabase(): void { if (db) { saveDatabase(); db.close(); db = null; logger.info('Database closed'); } } /** * Reset database (for testing) */ export async function resetDatabase(): Promise<void> { closeDatabase(); db = null; await initDatabase(); }