db.test.js (7497B)
1 // test/db.test.js - sqlite driver and migration runner 2 // 3 // The mysql and postgres drivers share the query compiler and parameter 4 // handling that is exercised here. Their wire behaviour is covered by the 5 // integration suite, which needs a live server. 6 7 import test from 'node:test'; 8 import assert from 'node:assert/strict'; 9 import fs from 'node:fs/promises'; 10 import os from 'node:os'; 11 import path from 'node:path'; 12 import { openDatabase, runMigrations } from '../src/lib/db/index.js'; 13 import { splitStatements } from '../src/lib/db/migrate.js'; 14 import { dialectFromUrl } from '../src/lib/db/dialect.js'; 15 16 const quiet = () => {}; 17 18 async function tempDb() { 19 const dir = await fs.mkdtemp(path.join(os.tmpdir(), 'conductor-db-')); 20 const cfg = { database: { dialect: 'sqlite', path: path.join(dir, 'test.db') } }; 21 const db = await openDatabase(cfg); 22 return { db, dir, cleanup: async () => { await db.close(); await fs.rm(dir, { recursive: true, force: true }); } }; 23 } 24 25 test('dialect detection covers every supported scheme', () => { 26 assert.equal(dialectFromUrl(null), 'sqlite'); 27 assert.equal(dialectFromUrl('mysql://u:p@h/db'), 'mysql'); 28 assert.equal(dialectFromUrl('postgres://u:p@h/db'), 'postgres'); 29 assert.equal(dialectFromUrl('postgresql://u:p@h/db'), 'postgres'); 30 assert.equal(dialectFromUrl('mongodb://h/db'), null); 31 }); 32 33 test('statement splitter respects literals and comments', () => { 34 const sql = [ 35 "INSERT INTO t VALUES ('a;b');", 36 '-- a comment with ; in it', 37 'CREATE TABLE u (id TEXT);', 38 ].join('\n'); 39 const parts = splitStatements(sql, 'sqlite'); 40 assert.equal(parts.length, 2); 41 assert.ok(parts[0].includes("'a;b'")); 42 assert.ok(parts[1].startsWith('CREATE TABLE u')); 43 }); 44 45 test('statement splitter keeps postgres dollar quoted bodies whole', () => { 46 const sql = "CREATE FUNCTION f() RETURNS void AS $$ BEGIN a; b; END $$ LANGUAGE plpgsql;"; 47 assert.equal(splitStatements(sql, 'postgres').length, 1); 48 }); 49 50 test('migrations apply once and are idempotent', async () => { 51 const { db, cleanup } = await tempDb(); 52 try { 53 const first = await runMigrations(db, { logger: quiet }); 54 assert.ok(first.applied.length > 0, 'expected the schema to be created'); 55 assert.equal(first.applied.length, first.total, 'a fresh database applies every migration'); 56 57 const second = await runMigrations(db, { logger: quiet }); 58 assert.equal(second.applied.length, 0, 'a second job must do nothing'); 59 assert.equal(second.total, first.total); 60 } finally { 61 await cleanup(); 62 } 63 }); 64 65 test('an edited migration is refused rather than silently diverging', async () => { 66 const { db, dir, cleanup } = await tempDb(); 67 try { 68 const custom = path.join(dir, 'migrations'); 69 await fs.mkdir(custom); 70 await fs.writeFile(path.join(custom, '001_a.sql'), 'CREATE TABLE a (id TEXT);'); 71 await runMigrations(db, { dir: custom, logger: quiet }); 72 73 await fs.writeFile(path.join(custom, '001_a.sql'), 'CREATE TABLE a (id TEXT, extra TEXT);'); 74 await assert.rejects( 75 runMigrations(db, { dir: custom, logger: quiet }), 76 /was modified after it was applied/ 77 ); 78 } finally { 79 await cleanup(); 80 } 81 }); 82 83 test('a migration removed from disk is reported', async () => { 84 const { db, dir, cleanup } = await tempDb(); 85 try { 86 const custom = path.join(dir, 'migrations'); 87 await fs.mkdir(custom); 88 await fs.writeFile(path.join(custom, '001_a.sql'), 'CREATE TABLE a (id TEXT);'); 89 await runMigrations(db, { dir: custom, logger: quiet }); 90 await fs.rm(path.join(custom, '001_a.sql')); 91 92 await assert.rejects(runMigrations(db, { dir: custom, logger: quiet }), /missing from/); 93 } finally { 94 await cleanup(); 95 } 96 }); 97 98 test('rows come back as ordinary objects', async () => { 99 const { db, cleanup } = await tempDb(); 100 try { 101 await runMigrations(db, { logger: quiet }); 102 const now = Date.now(); 103 await db.run( 104 'INSERT INTO projects (id, name, repo_url, created_at, updated_at) VALUES ({id}, {n}, {u}, {t}, {t})', 105 { id: 'p1', n: 'P', u: 'https://example.invalid/p.git', t: now } 106 ); 107 const row = await db.get('SELECT * FROM projects WHERE id = {id}', { id: 'p1' }); 108 assert.equal(Object.getPrototypeOf(row), Object.prototype); 109 assert.equal(row.name, 'P'); 110 assert.equal(row.enabled, 1); 111 } finally { 112 await cleanup(); 113 } 114 }); 115 116 test('a conditional update claims exactly once', async () => { 117 const { db, cleanup } = await tempDb(); 118 try { 119 await runMigrations(db, { logger: quiet }); 120 const t = Date.now(); 121 await db.run('INSERT INTO projects (id,name,repo_url,created_at,updated_at) VALUES ({i},{n},{u},{t},{t})', 122 { i: 'p', n: 'p', u: 'u', t }); 123 await db.run('INSERT INTO jobs (id,project_id,number,head_sha,created_at) VALUES ({i},{p},{n},{s},{t})', 124 { i: 'r', p: 'p', n: 1, s: 'sha', t }); 125 await db.run( 126 'INSERT INTO tasks (id,job_id,name,base_name,image,requires,spec,timeout,created_at) ' + 127 'VALUES ({i},{r},{n},{n},{img},{req},{spec},{to},{t})', 128 { i: 'r:b', r: 'r', n: 'b', img: 'alpine', req: '[]', spec: '{}', to: 60, t } 129 ); 130 131 const claim = 'UPDATE tasks SET state = {to} WHERE id = {id} AND state = {from}'; 132 const first = await db.run(claim, { to: 'running', id: 'r:b', from: 'queued' }); 133 const second = await db.run(claim, { to: 'running', id: 'r:b', from: 'queued' }); 134 assert.equal(first.changes, 1); 135 assert.equal(second.changes, 0); 136 } finally { 137 await cleanup(); 138 } 139 }); 140 141 test('deleting a job cascades to its tasks', async () => { 142 const { db, cleanup } = await tempDb(); 143 try { 144 await runMigrations(db, { logger: quiet }); 145 const t = Date.now(); 146 await db.run('INSERT INTO projects (id,name,repo_url,created_at,updated_at) VALUES ({i},{n},{u},{t},{t})', 147 { i: 'p', n: 'p', u: 'u', t }); 148 await db.run('INSERT INTO jobs (id,project_id,number,head_sha,created_at) VALUES ({i},{p},{n},{s},{t})', 149 { i: 'r', p: 'p', n: 1, s: 'sha', t }); 150 await db.run( 151 'INSERT INTO tasks (id,job_id,name,base_name,image,requires,spec,timeout,created_at) ' + 152 'VALUES ({i},{r},{n},{n},{img},{req},{spec},{to},{t})', 153 { i: 'r:b', r: 'r', n: 'b', img: 'alpine', req: '[]', spec: '{}', to: 60, t } 154 ); 155 156 await db.run('DELETE FROM jobs WHERE id = {id}', { id: 'r' }); 157 assert.equal((await db.get('SELECT COUNT(*) AS c FROM tasks', {})).c, 0); 158 } finally { 159 await cleanup(); 160 } 161 }); 162 163 test('a failed transaction rolls back, and a failed savepoint keeps the outer work', async () => { 164 const { db, cleanup } = await tempDb(); 165 try { 166 await runMigrations(db, { logger: quiet }); 167 const t = Date.now(); 168 const insert = 'INSERT INTO projects (id,name,repo_url,created_at,updated_at) VALUES ({i},{n},{u},{t},{t})'; 169 170 await assert.rejects(db.transaction(async (tx) => { 171 await tx.run(insert, { i: 'rolled', n: 'x', u: 'u', t }); 172 throw new Error('boom'); 173 }), /boom/); 174 assert.equal((await db.get('SELECT COUNT(*) AS c FROM projects', {})).c, 0); 175 176 await db.transaction(async (tx) => { 177 await tx.run(insert, { i: 'kept', n: 'x', u: 'u', t }); 178 await assert.rejects(tx.transaction(async (inner) => { 179 await inner.run(insert, { i: 'inner', n: 'x', u: 'u', t }); 180 throw new Error('inner failed'); 181 }), /inner failed/); 182 }); 183 184 const ids = (await db.all('SELECT id FROM projects ORDER BY id', {})).map((r) => r.id); 185 assert.deepEqual(ids, ['kept']); 186 } finally { 187 await cleanup(); 188 } 189 });