query.test.js (4007B)
1 // test/query.test.js - named bind marker compilation 2 3 import test from 'node:test'; 4 import assert from 'node:assert/strict'; 5 import { compileQuery, bindParams } from '../src/lib/db/query.js'; 6 7 test('compiles markers to the placeholder each dialect expects', () => { 8 const sql = 'SELECT * FROM tasks WHERE job_id = {job} AND state = {state}'; 9 10 assert.equal(compileQuery(sql, 'sqlite').sql, 'SELECT * FROM tasks WHERE job_id = ? AND state = ?'); 11 assert.equal(compileQuery(sql, 'mysql').sql, 'SELECT * FROM tasks WHERE job_id = ? AND state = ?'); 12 assert.equal(compileQuery(sql, 'postgres').sql, 'SELECT * FROM tasks WHERE job_id = $1 AND state = $2'); 13 14 assert.deepEqual(compileQuery(sql, 'postgres').keys, ['job', 'state']); 15 }); 16 17 test('numbers postgres placeholders per occurrence, including repeats', () => { 18 const { sql, keys } = compileQuery('SELECT {a}, {b}, {a}', 'postgres'); 19 assert.equal(sql, 'SELECT $1, $2, $3'); 20 assert.deepEqual(keys, ['a', 'b', 'a']); 21 }); 22 23 test('ignores markers inside string literals and comments', () => { 24 const sql = "SELECT '{notabind}' AS a, {real} -- {alsonot}\n, \"{quoted}\""; 25 const { keys } = compileQuery(sql, 'sqlite'); 26 assert.deepEqual(keys, ['real']); 27 assert.ok(compileQuery(sql, 'sqlite').sql.includes("'{notabind}'")); 28 }); 29 30 test('a json literal in default text survives untouched', () => { 31 const { sql, keys } = compileQuery("UPDATE t SET spec = '{}' WHERE id = {id}", 'postgres'); 32 assert.equal(sql, "UPDATE t SET spec = '{}' WHERE id = $1"); 33 assert.deepEqual(keys, ['id']); 34 }); 35 36 test('doubling a brace escapes it', () => { 37 const { sql, keys } = compileQuery('SELECT {{name}} , {name}', 'sqlite'); 38 assert.equal(sql, 'SELECT {name} , ?'); 39 assert.deepEqual(keys, ['name']); 40 }); 41 42 test('block comments are skipped, and nest only for postgres', () => { 43 assert.deepEqual(compileQuery('SELECT /* {no} */ {yes}', 'mysql').keys, ['yes']); 44 assert.deepEqual(compileQuery('SELECT /* a /* {no} */ b */ {yes}', 'postgres').keys, ['yes']); 45 }); 46 47 test('postgres dollar quoted bodies are skipped', () => { 48 const { keys } = compileQuery('SELECT $tag$ {no} $tag$, {yes}', 'postgres'); 49 assert.deepEqual(keys, ['yes']); 50 }); 51 52 test('mysql backtick identifiers and hash comments are skipped', () => { 53 assert.deepEqual(compileQuery('SELECT `col{no}`, {yes} # {alsono}\n', 'mysql').keys, ['yes']); 54 }); 55 56 test('a malformed marker is rejected with a useful message', () => { 57 assert.throws(() => compileQuery('SELECT {not a name}', 'sqlite'), /malformed bind marker/); 58 }); 59 60 test('binds by name, resolving dotted paths', () => { 61 const { keys, sql } = compileQuery('SELECT {a}, {nested.deep.value}', 'sqlite'); 62 assert.deepEqual(bindParams(keys, { a: 1, nested: { deep: { value: 'x' } } }, sql), [1, 'x']); 63 }); 64 65 test('an exact key beats path traversal', () => { 66 const { keys, sql } = compileQuery('SELECT {a.b}', 'sqlite'); 67 assert.deepEqual(bindParams(keys, { 'a.b': 'flat', a: { b: 'nested' } }, sql), ['flat']); 68 }); 69 70 test('reports every missing key at once', () => { 71 const { keys, sql } = compileQuery('SELECT {a}, {b}, {c}', 'sqlite'); 72 assert.throws( 73 () => bindParams(keys, { b: 1 }, sql), 74 (e) => /missing bind parameter\(s\): a, c/.test(e.message) 75 ); 76 }); 77 78 test('rejects an array of parameters, which would silently bind by position', () => { 79 const { keys, sql } = compileQuery('SELECT {a}', 'sqlite'); 80 assert.throws(() => bindParams(keys, ['x'], sql), /must be an object/); 81 }); 82 83 test('normalizes booleans, dates and null', () => { 84 const { keys, sql } = compileQuery('SELECT {t}, {f}, {d}, {n}', 'sqlite'); 85 const at = new Date('2026-01-02T03:04:05.678Z'); 86 assert.deepEqual(bindParams(keys, { t: true, f: false, d: at, n: null }, sql), [1, 0, at.getTime(), null]); 87 }); 88 89 test('rejects an object value, which is nearly always a forgotten stringify', () => { 90 const { keys, sql } = compileQuery('SELECT {spec}', 'sqlite'); 91 assert.throws(() => bindParams(keys, { spec: { a: 1 } }, sql), /unsupported type object/); 92 });