hermes-control-panel/lib/db.cjs

"use strict"

const { Pool } = require("pg")
const fs = require("fs")
const path = require("path")

const MIGRATIONS_DIR = path.join(__dirname, "..", "migrations")
const ADVISORY_LOCK_KEY = 12345678

function createPool(databaseUrl) {
  return new Pool({
    connectionString: databaseUrl,
    connectionTimeoutMillis: 5000,
    query_timeout: 10000,
    idleTimeoutMillis: 30000,
  })
}

async function withTransaction(pool, fn) {
  const client = await pool.connect()
  try {
    await client.query("BEGIN")
    const result = await fn(client)
    await client.query("COMMIT")
    return result
  } catch (err) {
    await client.query("ROLLBACK")
    throw err
  } finally {
    client.release()
  }
}

async function runMigrations(pool) {
  const client = await pool.connect()
  try {
    // Acquire advisory lock to prevent concurrent migrations
    await client.query(`SELECT pg_advisory_lock(${ADVISORY_LOCK_KEY})`)

    try {
      // Create schema_migrations table if it doesn't exist
      await client.query(`
        CREATE TABLE IF NOT EXISTS schema_migrations (
          name text PRIMARY KEY,
          applied_at timestamptz DEFAULT now()
        )
      `)

      // Read all .sql files from migrations directory, sorted alphabetically
      const files = fs
        .readdirSync(MIGRATIONS_DIR)
        .filter((f) => f.endsWith(".sql"))
        .sort()

      for (const file of files) {
        // Check if migration has already been applied
        const { rows } = await client.query(
          "SELECT name FROM schema_migrations WHERE name = $1",
          [file]
        )

        if (rows.length === 0) {
          const sql = fs.readFileSync(path.join(MIGRATIONS_DIR, file), "utf8")
          await client.query(sql)
          await client.query(
            "INSERT INTO schema_migrations (name) VALUES ($1)",
            [file]
          )
        }
      }
    } finally {
      // Always release the advisory lock
      await client.query(`SELECT pg_advisory_unlock(${ADVISORY_LOCK_KEY})`)
    }
  } finally {
    client.release()
  }
}

module.exports = { createPool, withTransaction, runMigrations }