property-management-network/scripts/migrate.mjs

// Production database migration runner.
//
// Applies the Drizzle SQL migrations in lib/db/migrations using drizzle-orm's
// built-in migrator. Runs without drizzle-kit (a devDependency), so it works
// inside the slim production image. Invoked by docker-entrypoint.sh on boot
// unless RUN_MIGRATIONS_ON_START=false.
import { drizzle } from "drizzle-orm/node-postgres"
import { migrate } from "drizzle-orm/node-postgres/migrator"
import pg from "pg"

const { Pool } = pg

const url = process.env.DATABASE_URL
if (!url) {
  console.error("[migrate] DATABASE_URL is not set — aborting.")
  process.exit(1)
}

// Mirror lib/db/index.ts TLS policy so migrations connect exactly like the app:
//   DATABASE_SSL = "disable"   -> no TLS (local dev / unix-socket Postgres)
//   DATABASE_SSL = "no-verify" -> encrypted, unverified (self-signed certs)
//   unset / "require" / other  -> encrypted + verified (optional DATABASE_CA)
function resolveSsl() {
  switch (process.env.DATABASE_SSL) {
    case "disable":
      return false
    case "no-verify":
      return { rejectUnauthorized: false }
    default: {
      const ca = process.env.DATABASE_CA
      return ca ? { rejectUnauthorized: true, ca } : { rejectUnauthorized: true }
    }
  }
}

const pool = new Pool({
  connectionString: url,
  ssl: resolveSsl(),
})

const db = drizzle(pool)

const MAX_ATTEMPTS = 10
const RETRY_DELAY_MS = 3000

for (let attempt = 1; attempt <= MAX_ATTEMPTS; attempt++) {
  try {
    await migrate(db, { migrationsFolder: "./lib/db/migrations" })
    console.log("[migrate] Migrations applied successfully.")
    await pool.end()
    process.exit(0)
  } catch (err) {
    const isLast = attempt === MAX_ATTEMPTS
    console.error(`[migrate] Attempt ${attempt}/${MAX_ATTEMPTS} failed: ${err?.message ?? err}`)
    if (isLast) {
      await pool.end().catch(() => {})
      process.exit(1)
    }
    // Postgres may still be starting up (common with the bundled compose DB).
    await new Promise((resolve) => setTimeout(resolve, RETRY_DELAY_MS))
  }
}