db.test.js
1 /** 2 * Tests for src/utils/db.js 3 * 4 * Covers: query, getOne, getAll, run, withTransaction, closePool 5 * 6 * Uses pg-mock to exercise the PG-compatible interface backed by SQLite in-memory. 7 */ 8 9 import { test, describe } from 'node:test'; 10 import assert from 'node:assert/strict'; 11 import Database from 'better-sqlite3'; 12 import { createPgMock, pgToSqlite } from '../helpers/pg-mock.js'; 13 14 // ─── pgToSqlite translation ──────────────────────────────────────────────────── 15 16 describe('pgToSqlite', () => { 17 test('converts $1, $2 params to ?', () => { 18 assert.equal(pgToSqlite('SELECT * FROM foo WHERE id = $1'), 'SELECT * FROM foo WHERE id = ?'); 19 assert.equal( 20 pgToSqlite('INSERT INTO t (a,b) VALUES ($1,$2)'), 21 'INSERT INTO t (a,b) VALUES (?,?)' 22 ); 23 }); 24 25 test('converts NOW() to datetime(now)', () => { 26 const result = pgToSqlite('SELECT NOW()'); 27 assert.ok(result.includes("datetime('now')")); 28 }); 29 30 test('converts CURRENT_DATE to date(now)', () => { 31 const result = pgToSqlite('SELECT CURRENT_DATE'); 32 assert.ok(result.includes("date('now')")); 33 }); 34 35 test('strips RETURNING clause', () => { 36 const result = pgToSqlite('INSERT INTO t (a) VALUES (?) RETURNING id'); 37 assert.ok(!result.includes('RETURNING')); 38 }); 39 40 test('converts ILIKE to LIKE', () => { 41 const result = pgToSqlite('SELECT * FROM t WHERE name ILIKE $1'); 42 assert.ok(result.includes('LIKE')); 43 assert.ok(!result.includes('ILIKE')); 44 }); 45 46 test('strips schema prefixes (tel., ops.)', () => { 47 assert.ok(!pgToSqlite('SELECT * FROM tel.agent_tasks').includes('tel.')); 48 assert.ok(!pgToSqlite('SELECT * FROM ops.metrics').includes('ops.')); 49 }); 50 }); 51 52 // ─── createPgMock ───────────────────────────────────────────────────────────── 53 54 describe('createPgMock — query', () => { 55 test('SELECT returns rows array', async () => { 56 const db = new Database(':memory:'); 57 db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)'); 58 db.exec("INSERT INTO t VALUES (1, 'hello')"); 59 const { query } = createPgMock(db); 60 const result = await query('SELECT * FROM t WHERE id = $1', [1]); 61 assert.equal(result.rows.length, 1); 62 assert.equal(result.rows[0].val, 'hello'); 63 db.close(); 64 }); 65 66 test('INSERT returns rowCount', async () => { 67 const db = new Database(':memory:'); 68 db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)'); 69 const { query } = createPgMock(db); 70 const result = await query('INSERT INTO t (val) VALUES ($1)', ['world']); 71 assert.equal(result.rowCount, 1); 72 db.close(); 73 }); 74 75 test('UPDATE returns rowCount', async () => { 76 const db = new Database(':memory:'); 77 db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)'); 78 db.exec("INSERT INTO t VALUES (1, 'old')"); 79 const { query } = createPgMock(db); 80 const result = await query('UPDATE t SET val = $1 WHERE id = $2', ['new', 1]); 81 assert.equal(result.rowCount, 1); 82 db.close(); 83 }); 84 }); 85 86 describe('createPgMock — getOne', () => { 87 test('returns single row for existing record', async () => { 88 const db = new Database(':memory:'); 89 db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)'); 90 db.exec("INSERT INTO t VALUES (42, 'answer')"); 91 const { getOne } = createPgMock(db); 92 const row = await getOne('SELECT * FROM t WHERE id = $1', [42]); 93 assert.ok(row !== null); 94 assert.equal(row.val, 'answer'); 95 db.close(); 96 }); 97 98 test('returns null for missing record', async () => { 99 const db = new Database(':memory:'); 100 db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)'); 101 const { getOne } = createPgMock(db); 102 const row = await getOne('SELECT * FROM t WHERE id = $1', [999]); 103 assert.equal(row, null); 104 db.close(); 105 }); 106 }); 107 108 describe('createPgMock — getAll', () => { 109 test('returns all matching rows', async () => { 110 const db = new Database(':memory:'); 111 db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)'); 112 db.exec("INSERT INTO t VALUES (1, 'a'), (2, 'b'), (3, 'c')"); 113 const { getAll } = createPgMock(db); 114 const rows = await getAll('SELECT * FROM t ORDER BY id'); 115 assert.equal(rows.length, 3); 116 assert.equal(rows[0].val, 'a'); 117 assert.equal(rows[2].val, 'c'); 118 db.close(); 119 }); 120 121 test('returns empty array when no rows', async () => { 122 const db = new Database(':memory:'); 123 db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)'); 124 const { getAll } = createPgMock(db); 125 const rows = await getAll('SELECT * FROM t'); 126 assert.deepEqual(rows, []); 127 db.close(); 128 }); 129 }); 130 131 describe('createPgMock — run', () => { 132 test('returns changes count', async () => { 133 const db = new Database(':memory:'); 134 db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)'); 135 db.exec("INSERT INTO t VALUES (1, 'a'), (2, 'b')"); 136 const { run } = createPgMock(db); 137 const result = await run("DELETE FROM t WHERE val = 'a'"); 138 assert.equal(result.changes, 1); 139 db.close(); 140 }); 141 142 test('returns lastInsertRowid for inserts', async () => { 143 const db = new Database(':memory:'); 144 db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)'); 145 const { run } = createPgMock(db); 146 const result = await run('INSERT INTO t (val) VALUES ($1)', ['test']); 147 assert.ok(result.lastInsertRowid > 0); 148 db.close(); 149 }); 150 }); 151 152 describe('createPgMock — withTransaction', () => { 153 test('executes function within transaction', async () => { 154 const db = new Database(':memory:'); 155 db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)'); 156 const { withTransaction } = createPgMock(db); 157 await withTransaction(async (client) => { 158 await client.query('INSERT INTO t (val) VALUES ($1)', ['txn']); 159 }); 160 const row = db.prepare('SELECT * FROM t').get(); 161 assert.equal(row.val, 'txn'); 162 db.close(); 163 }); 164 }); 165 166 describe('createPgMock — closePool / getPool / createDatabaseConnection', () => { 167 test('closePool resolves without error', async () => { 168 const db = new Database(':memory:'); 169 const { closePool } = createPgMock(db); 170 await assert.doesNotReject(() => closePool()); 171 db.close(); 172 }); 173 174 test('getPool returns an object', () => { 175 const db = new Database(':memory:'); 176 const { getPool } = createPgMock(db); 177 assert.ok(typeof getPool() === 'object'); 178 db.close(); 179 }); 180 181 test('createDatabaseConnection returns an object', () => { 182 const db = new Database(':memory:'); 183 const { createDatabaseConnection } = createPgMock(db); 184 assert.ok(typeof createDatabaseConnection() === 'object'); 185 db.close(); 186 }); 187 });