spb/search-box Public

1/**2 * Search-box.ai3 * Author: Simon-Pierre Boucher4 * Contact: contact@spboucher.ai5 * File: packages/db/src/migrate.ts6 * Description: Idempotent migration runner (applies packages/db/migrations in order).7 */89import { readdirSync, readFileSync } from "node:fs";10import { dirname, join } from "node:path";11import { fileURLToPath } from "node:url";12import { getPool } from "./pool.js";1314const here = dirname(fileURLToPath(import.meta.url));15const migrationsDir = join(here, "..", "migrations");1617export async function migrate(): Promise<void> {18  const pool = getPool();19  await pool.query(20    `CREATE TABLE IF NOT EXISTS schema_migrations (21       name text PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT now()22     )`23  );24  const files = readdirSync(migrationsDir).filter((f) => f.endsWith(".sql")).sort();25  for (const file of files) {26    const { rows } = await pool.query("SELECT 1 FROM schema_migrations WHERE name = $1", [file]);27    if (rows.length > 0) continue;28    const sql = readFileSync(join(migrationsDir, file), "utf8");29    const client = await pool.connect();30    try {31      await client.query("BEGIN");32      await client.query(sql);33      await client.query("INSERT INTO schema_migrations (name) VALUES ($1)", [file]);34      await client.query("COMMIT");35      console.log(`applied ${file}`);36    } catch (err) {37      await client.query("ROLLBACK");38      throw err;39    } finally {40      client.release();41    }42  }43}4445// Run directly: `pnpm migrate`46if (process.argv[1] && process.argv[1].endsWith("migrate.ts")) {47  migrate()48    .then(() => process.exit(0))49    .catch((err) => {50      console.error(err);51      process.exit(1);52    });53}54