scripts/db-migrate.ts

Linked repository file from this build.

#!/usr/bin/env bun
import "dotenv/config";
import { mkdir, writeFile } from "node:fs/promises";
import { Client } from "pg";
import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { resolveDatabaseTarget, errorDetails, redact } from "./db/target";
import { databaseStatus, migrationFiles, migrationsFolder } from "./db/migrations";

export async function main(args = process.argv.slice(2)) {
  let connection: Client | undefined;
  let url = "";
  let step: { migration: string; statement: number; sql: string } | undefined;
  let applying = false;
  const report: Record<string, unknown> = { startedAt: new Date().toISOString() };
  const stamp = new Date().toISOString().replace(/[:.]/g, "-");
  const reportPath = `test-results/database/${stamp}-${process.pid}.json`;
  try {
    if (args.includes("--help")) {
      console.log(
        "Usage: bun run scripts/db-migrate.ts [--status] [--target development|production]\nProduction reads PROD_DATABASE_URL; development reads DATABASE_URL. Store both in .env.",
      );
      return 0;
    }
    let target = process.env.QC_DB_TARGET ?? "development";
    let statusOnly = false;
    for (let i = 0; i < args.length; i++) {
      if (args[i] === "--status") statusOnly = true;
      else if (args[i] === "--target" && args[i + 1]) target = args[++i]!;
      else throw new Error("Unknown or incomplete argument. Use --help.");
    }
    const selected = resolveDatabaseTarget(target);
    url = selected.url;
    Object.assign(report, {
      target,
      database: selected.label,
      mode: statusOnly ? "status" : "migrate",
    });
    console.log(`Database: ${target} — ${selected.label} (${selected.variable})`);
    const files = migrationFiles();
    connection = new Client({
      connectionString: url,
      connectionTimeoutMillis: 10_000,
      application_name: "iris-migrations",
    });
    // Handle idle socket errors without allowing pg's EventEmitter to print credentials.
    connection.on("error", (error) => {
      report.connectionError = errorDetails(error, [url]);
    });
    await connection.connect();
    await connection.query("set lock_timeout = '10s'; set statement_timeout = '5min'");
    // Serialize this runner, then re-read history under the lock before applying anything.
    if (!statusOnly) await connection.query("select pg_advisory_lock(741903, 1)");
    const before = await databaseStatus(connection, files);
    report.before = before;
    console.log(`Applied: ${before.applied}; pending: ${before.pending.length}`);
    for (const tag of before.pending) console.log(`  pending ${tag}.sql`);
    for (const warning of before.warnings) console.warn(`History warning: ${warning}`);
    for (const issue of before.issues) console.error(`History issue: ${issue}`);
    if (before.missingColumns.length)
      console.log(`Missing columns: ${before.missingColumns.join(", ")}`);
    if (before.issues.length)
      throw new Error(
        "Migration history needs reconciliation. See the issues above; nothing was applied.",
      );
    if (statusOnly) {
      report.ok = before.pending.length === 0 && before.missingColumns.length === 0;
      return report.ok ? 0 : 1;
    }
    if (!before.pending.length && before.missingColumns.length)
      throw new Error(
        "History is current but the application schema is missing columns. This is schema drift; migrations cannot repair it by rerunning recorded files.",
      );
    if (!before.pending.length) {
      report.ok = true;
      report.after = before;
      console.log("Database migrations are already current; nothing to apply.");
      return 0;
    }
    const lookup = new Map(
      files.flatMap((file) =>
        file.sql.map(
          (sql, index) =>
            [
              sql.trim(),
              { migration: `${file.tag}.sql`, statement: index + 1, sql: sql.trim() },
            ] as const,
        ),
      ),
    );
    const db = drizzle(connection, {
      logger: {
        logQuery(query) {
          // Do not replace the last real statement when Drizzle logs ROLLBACK.
          if (/^\s*rollback(?:\s|$)/i.test(query)) return;
          const current = lookup.get(query.trim());
          step = current ?? {
            migration: "Drizzle migration bookkeeping",
            statement: 0,
            sql: query.trim(),
          };
          if (current) {
            console.log(`Applying ${current.migration} — statement ${current.statement}`);
          }
        },
      },
    });
    applying = true;
    await migrate(db, { migrationsFolder });
    applying = false;
    step = undefined;
    const after = await databaseStatus(connection, files);
    report.after = after;
    if (after.pending.length || after.issues.length || after.missingColumns.length)
      throw new Error(
        "Migration finished but schema verification failed. See the diagnostic report.",
      );
    report.ok = true;
    console.log("Database migrations are current; all application columns are present.");
    return 0;
  } catch (error) {
    report.ok = false;
    report.step = step;
    report.errors = errorDetails(error, [url]);
    console.error(
      applying
        ? "Migration failed. Drizzle attempts to roll back the pending migration transaction; check status before retrying."
        : "Database command failed.",
    );
    if (step)
      console.error(
        `Failed at ${step.migration}, statement ${step.statement}:\n${redact(step.sql, [url])}`,
      );
    console.error(JSON.stringify(report.errors, null, 2));
    return 1;
  } finally {
    if (connection) await connection.end().catch(() => {});
    if (!args.includes("--help")) {
      await mkdir("test-results/database", { recursive: true });
      await writeFile(reportPath, redact(JSON.stringify(report, null, 2), [url]), { mode: 0o600 });
      console.log(`Diagnostic report: ${reportPath}`);
    }
  }
}
if (import.meta.main) process.exitCode = await main();