// src/db.mjs import { fileURLToPath } from 'node:url'; import pg from 'pg'; import { bootWalk, createFailoverPool, migrationsFrom } from '@turnzero/database'; const MIGRATIONS = fileURLToPath(new URL('../migrations/', import.meta.url)); const HEALTH_READ_MS = 2_000; // The live pool, which only the process entry opens: the package's failover // pool over a pg pool. APP_DATABASE_CONNECTION_LIMIT sets its maximum. // The platform supplies it; the turnzero command's provision writes it for // a local run. The pool uses one connection where the setting is absent. export function openLivePool() { const url = process.env.APP_DATABASE_URL; if (!url) throw new Error('APP_DATABASE_URL is not set'); const limit = process.env.APP_DATABASE_CONNECTION_LIMIT ?? ''; const max = /^[1-9][0-9]*$/.test(limit) ? Number(limit) : 1; return createFailoverPool({ // settings is { max, connectionTimeoutMillis }: the maximum and the five-second connect limit. connect: (settings) => new pg.Pool({ connectionString: url, ...settings }), max, onConnectionLoss: (error) => console.warn('database session ended:', error.message), }); } // The migrations through the package's boot walk: a lost connection retried // every second for up to 120 seconds, and any other failure ends the start. export function migrate(pool) { return bootWalk(pool, migrationsFrom(MIGRATIONS), { onRetry: (error, attempt) => { if (attempt === 1) console.warn('database unreachable at start; retrying:', error.message); }, }); } // The data layer over any pool with the pg.Pool shape: the live one, or a test double's. // Each row belongs to the signed-in user who added it, its owner, and is listed to that user // alone, newest first; only its owner can delete it. export function createDataLayer(pool) { return { // One read over the connection, bounded so a probe never waits on a lost database. async reachable() { let timer; const late = new Promise((resolve) => { timer = setTimeout(resolve, HEALTH_READ_MS, 'late'); }); try { return (await Promise.race([pool.query('SELECT 1'), late])) !== 'late'; } catch { return false; } finally { clearTimeout(timer); } }, async addNote(owner, body) { const { rows } = await pool.query('INSERT INTO "notes" (owner, body) VALUES ($1, $2) RETURNING id, body, created_at', [owner, body]); return rows[0]; }, async listNotes(owner) { const { rows } = await pool.query('SELECT id, body, created_at FROM "notes" WHERE owner = $1 ORDER BY created_at DESC, id DESC', [owner]); return rows; }, // True where the row was the owner's and is gone; false where no row of the owner's has the id. async deleteNote(owner, id) { const { rowCount } = await pool.query('DELETE FROM "notes" WHERE owner = $1 AND id = $2', [owner, id]); return rowCount === 1; }, }; }