1  /**
  2   * Test Database Utility
  3   * Provides consistent SQLite database initialization for tests using the production schema.
  4   *
  5   * Architecture note: tests use SQLite (not PostgreSQL) so they can run without a live
  6   * database instance in CI and development.  This is intentional — the production db.js
  7   * is mocked at the module level by tests that import source files which call db.js.
  8   * Tests that need a real relational database use initTestDb() / createInMemoryDb() to
  9   * get a SQLite instance pre-loaded with the production schema (db/schema.sql).
 10   *
 11   * Two patterns in the test suite:
 12   *   A. Mock db.js entirely (mock.module('../../src/utils/db.js', {...})) — used by
 13   *      tests that just need to stub DB calls; no SQLite needed.
 14   *   B. Real SQLite via test-db.js — used by integration-style tests that INSERT rows
 15   *      and verify query results without going through the production PG pool.
 16   *
 17   * The createPgMock() export below supports pattern A by providing ready-made pg-style
 18   * async mock functions (run/getOne/getAll/query/withTransaction) for use with mock.module.
 19   */
 20  
 21  import Database from 'better-sqlite3';
 22  import { readFileSync, existsSync, unlinkSync } from 'fs';
 23  import { join, dirname } from 'path';
 24  import { fileURLToPath } from 'url';
 25  
 26  const __filename = fileURLToPath(import.meta.url);
 27  const __dirname = dirname(__filename);
 28  const projectRoot = join(__dirname, '../..');
 29  const schemaPath = join(projectRoot, 'db/schema.sql');
 30  
 31  // Use /tmp for shared test databases (avoids writing to project dir)
 32  const SHM_DIR = '/tmp';
 33  
 34  /**
 35   * Get the path for a shared in-memory test database
 36   * @param {string} dbName - Name for the test database
 37   * @returns {string} Path to the database file in /dev/shm
 38   */
 39  export function getTestDbPath(dbName = 'test') {
 40    return join(SHM_DIR, `333m-test-${dbName}.db`);
 41  }
 42  
 43  /**
 44   * Create an in-memory test database with production schema.
 45   * Uses SQLite's true :memory: — each call returns an isolated instance
 46   * with no on-disk artifact, suitable for parallel test execution.
 47   * @returns {Database} Database instance with schema applied
 48   */
 49  export function createInMemoryDb() {
 50    const db = new Database(':memory:');
 51    const schema = readFileSync(schemaPath, 'utf-8');
 52    db.exec(schema);
 53    return db;
 54  }
 55  
 56  /**
 57   * Initialize a test database with production schema
 58   * @param {string} dbPath - Path to the test database file (use ':memory:' for in-memory)
 59   * @returns {Database} Initialized database instance
 60   */
 61  export function initTestDb(dbPath = ':memory:') {
 62    // Delete stale file so CREATE TABLE IF NOT EXISTS always applies the current schema
 63    if (dbPath !== ':memory:' && existsSync(dbPath)) {
 64      unlinkSync(dbPath);
 65      try { unlinkSync(`${dbPath}-journal`); } catch { /* ignore */ }
 66      try { unlinkSync(`${dbPath}-wal`); } catch { /* ignore */ }
 67      try { unlinkSync(`${dbPath}-shm`); } catch { /* ignore */ }
 68    }
 69  
 70    const db = new Database(dbPath);
 71  
 72    // Read and apply production schema.
 73    // Wrap in an explicit transaction for file-based databases to avoid ~135 individual
 74    // fsync calls (one per DDL statement), which takes 20+ seconds on Docker overlay
 75    // filesystems vs <100ms in a single transaction.
 76    const schema = readFileSync(schemaPath, 'utf-8');
 77    if (dbPath !== ':memory:') {
 78      db.exec('BEGIN;');
 79      db.exec(schema);
 80      db.exec('COMMIT;');
 81    } else {
 82      db.exec(schema);
 83    }
 84  
 85    return db;
 86  }
 87  
 88  /**
 89   * Close database and clean up
 90   * @param {Database} db - Database instance to close
 91   */
 92  export function closeDb(db) {
 93    if (db) {
 94      db.close();
 95    }
 96  }
 97  
 98  /**
 99   * Apply specific migration to test database
100   * @param {Database} db - Database instance
101   * @param {string} migrationName - Migration file name (e.g., '013-fix-keywords-schema.sql')
102   */
103  export function applyMigration(db, migrationName) {
104    const migrationPath = join(projectRoot, 'db/migrations', migrationName);
105    const migration = readFileSync(migrationPath, 'utf-8');
106    db.exec(migration);
107  }
108  
109  /**
110   * Create a minimal test database with only required tables
111   * Useful for unit tests that don't need the full schema
112   * @param {Database} db - Database instance
113   * @param {string[]} tables - Table names to create (e.g., ['sites', 'keywords'])
114   */
115  export function createMinimalSchema(db, tables) {
116    const schema = readFileSync(schemaPath, 'utf-8');
117  
118    // Extract CREATE TABLE and related indexes for specific tables
119    for (const table of tables) {
120      // Match CREATE TABLE statement (including CHECK constraints)
121      const tableRegex = new RegExp(`CREATE TABLE[^;]*\\b${table}\\b[^;]*(?:\\([^)]*\\))*;`, 'gis');
122      const tableMatch = schema.match(tableRegex);
123  
124      if (tableMatch) {
125        db.exec(tableMatch[0]);
126      }
127  
128      // Match indexes for this table
129      const indexRegex = new RegExp(`CREATE[^;]*INDEX[^;]*\\b${table}\\b[^;]*;`, 'gis');
130      const indexMatches = schema.match(indexRegex);
131  
132      if (indexMatches) {
133        for (const index of indexMatches) {
134          db.exec(index);
135        }
136      }
137  
138      // Match triggers for this table
139      const triggerRegex = new RegExp(`CREATE[^;]*TRIGGER[^;]*\\b${table}\\b[^;]*END;`, 'gis');
140      const triggerMatches = schema.match(triggerRegex);
141  
142      if (triggerMatches) {
143        for (const trigger of triggerMatches) {
144          db.exec(trigger);
145        }
146      }
147    }
148  }
149  
150  /**
151   * Create an in-memory database with minimal schema.
152   * Uses SQLite's true :memory: for test isolation and speed.
153   * @param {string[]} tables - Table names to create (e.g., ['sites', 'keywords'])
154   * @returns {Database} Database instance with minimal schema
155   */
156  export function createMinimalInMemoryDb(tables) {
157    const db = new Database(':memory:');
158    createMinimalSchema(db, tables);
159    return db;
160  }
161  
162  /**
163   * Clean up a test database file from /dev/shm
164   * @param {string} dbName - Name of the test database to clean up
165   */
166  export function cleanupTestDb(dbName = 'test') {
167    const dbPath = getTestDbPath(dbName);
168    try {
169      unlinkSync(dbPath);
170    } catch (e) {
171      // File doesn't exist, that's fine
172    }
173    try {
174      unlinkSync(`${dbPath}-journal`);
175    } catch (e) {
176      // Journal doesn't exist, that's fine
177    }
178    try {
179      unlinkSync(`${dbPath}-wal`);
180    } catch (e) {
181      // WAL doesn't exist, that's fine
182    }
183    try {
184      unlinkSync(`${dbPath}-shm`);
185    } catch (e) {
186      // SHM doesn't exist, that's fine
187    }
188  }
189  
190  /**
191   * Create a pg-style mock object suitable for use with mock.module('../../src/utils/db.js').
192   *
193   * Returns an object with async functions matching the db.js API.  Each function stores
194   * calls in its .calls array and returns the value configured via .mockResolvedValue().
195   *
196   * Usage in tests:
197   *   import { mock } from 'node:test';
198   *   import { createPgMock } from '../../src/utils/test-db.js';
199   *
200   *   const dbMock = createPgMock();
201   *   mock.module('../../src/utils/db.js', { namedExports: dbMock });
202   *
203   *   // Configure return values:
204   *   dbMock.getOne.mockResolvedValue({ id: 1, domain: 'example.com' });
205   *   dbMock.getAll.mockResolvedValue([{ id: 1 }, { id: 2 }]);
206   *   dbMock.run.mockResolvedValue({ changes: 1, lastInsertRowid: 42 });
207   *
208   * @returns {object} Mock exports matching db.js named exports
209   */
210  export function createPgMock() {
211    function makeAsyncMock(defaultReturn) {
212      let _impl = async () => defaultReturn;
213      const fn = async (...args) => {
214        fn.calls.push(args);
215        return _impl(...args);
216      };
217      fn.calls = [];
218      fn.mockResolvedValue = val => { _impl = async () => val; };
219      fn.mockImplementation = impl => { _impl = impl; };
220      fn.mockReset = () => { fn.calls = []; _impl = async () => defaultReturn; };
221      return fn;
222    }
223  
224    return {
225      getPool: () => ({ query: async () => ({ rows: [], rowCount: 0 }) }),
226      query: makeAsyncMock({ rows: [], rowCount: 0 }),
227      getOne: makeAsyncMock(null),
228      getAll: makeAsyncMock([]),
229      run: makeAsyncMock({ changes: 0, lastInsertRowid: undefined }),
230      withTransaction: makeAsyncMock(null),
231      closePool: makeAsyncMock(undefined),
232      createDatabaseConnection: () => ({ query: async () => ({ rows: [], rowCount: 0 }) }),
233      closeDatabaseConnection: makeAsyncMock(undefined),
234    };
235  }
236  
237  export default {
238    getTestDbPath,
239    createInMemoryDb,
240    initTestDb,
241    closeDb,
242    applyMigration,
243    createMinimalSchema,
244    createMinimalInMemoryDb,
245    cleanupTestDb,
246    createPgMock,
247  };