costco-grocery-list/scripts/db-migrate-common.js

"use strict";

const fs = require("fs");
const path = require("path");
const { spawnSync } = require("child_process");

const migrationsDir = path.resolve(
  __dirname,
  "..",
  "packages",
  "db",
  "migrations"
);
const staleConfigPath = path.join(migrationsDir, "stale-files.json");

function readStaleConfigFile() {
  if (!fs.existsSync(staleConfigPath)) {
    return new Set();
  }

  const raw = fs.readFileSync(staleConfigPath, "utf8");
  let parsed;
  try {
    parsed = JSON.parse(raw);
  } catch (error) {
    throw new Error(`Invalid JSON in ${staleConfigPath}`);
  }

  const values = Array.isArray(parsed?.stale_files) ? parsed.stale_files : [];
  return new Set(
    values
      .map((value) => String(value || "").trim())
      .filter(Boolean)
  );
}

function getSkippedMigrations() {
  const includeStale = String(process.env.DB_MIGRATE_INCLUDE_STALE || "")
    .trim()
    .toLowerCase();
  const skipFromConfig =
    includeStale === "1" || includeStale === "true" || includeStale === "yes"
      ? new Set()
      : readStaleConfigFile();

  const raw = process.env.DB_MIGRATE_SKIP_FILES || "";
  const skipFromEnv = new Set(
    raw
      .split(",")
      .map((value) => value.trim())
      .filter(Boolean)
  );

  return new Set([...skipFromConfig, ...skipFromEnv]);
}

function ensureDatabaseUrl() {
  const databaseUrl = process.env.DATABASE_URL;
  if (!databaseUrl) {
    throw new Error("DATABASE_URL is required.");
  }
  return databaseUrl;
}

function ensurePsql() {
  const result = spawnSync("psql", ["--version"], { stdio: "pipe" });
  if (result.error || result.status !== 0) {
    throw new Error("psql executable was not found in PATH.");
  }
}

function ensureMigrationsDir() {
  if (!fs.existsSync(migrationsDir)) {
    throw new Error(`Migrations directory not found: ${migrationsDir}`);
  }
}

function getMigrationFiles() {
  ensureMigrationsDir();
  const skipped = getSkippedMigrations();
  return fs
    .readdirSync(migrationsDir)
    .filter((file) => file.endsWith(".sql"))
    .filter((file) => !skipped.has(file))
    .sort((a, b) => a.localeCompare(b));
}

function runPsql(databaseUrl, args) {
  const result = spawnSync("psql", [databaseUrl, ...args], {
    stdio: "pipe",
    encoding: "utf8",
  });
  if (result.status !== 0) {
    const stderr = (result.stderr || "").trim();
    const stdout = (result.stdout || "").trim();
    const details = [stderr, stdout].filter(Boolean).join("\n");
    throw new Error(details || "psql command failed");
  }
  return result.stdout || "";
}

function escapeSqlLiteral(value) {
  return value.replace(/'/g, "''");
}

function ensureSchemaMigrationsTable(databaseUrl) {
  runPsql(databaseUrl, [
    "-v",
    "ON_ERROR_STOP=1",
    "-c",
    "CREATE TABLE IF NOT EXISTS schema_migrations (filename TEXT PRIMARY KEY, applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW());",
  ]);
}

function getAppliedMigrations(databaseUrl) {
  const output = runPsql(databaseUrl, [
    "-At",
    "-v",
    "ON_ERROR_STOP=1",
    "-c",
    "SELECT filename FROM schema_migrations ORDER BY filename ASC;",
  ]);
  return new Set(
    output
      .split(/\r?\n/)
      .map((line) => line.trim())
      .filter(Boolean)
  );
}

function applyMigration(databaseUrl, filename) {
  const fullPath = path.join(migrationsDir, filename);
  runPsql(databaseUrl, ["-v", "ON_ERROR_STOP=1", "-f", fullPath]);
  runPsql(databaseUrl, [
    "-v",
    "ON_ERROR_STOP=1",
    "-c",
    `INSERT INTO schema_migrations (filename) VALUES ('${escapeSqlLiteral(
      filename
    )}') ON CONFLICT DO NOTHING;`,
  ]);
}

module.exports = {
  applyMigration,
  ensureDatabaseUrl,
  ensurePsql,
  ensureSchemaMigrationsTable,
  getAppliedMigrations,
  getMigrationFiles,
  getSkippedMigrations,
  migrationsDir,
};