Speisekarte/db/migrate.mjs

#!/usr/bin/env node
// Wendet alle noch nicht angewendeten Dateien aus db/migrations/*.sql auf
// die per DATABASE_URL erreichbare Postgres-Datenbank an. Idempotent –
// kann bei jedem App-Start gefahrlos erneut laufen (Docker-Entrypoint,
// npm predev/prestart).

import { readdirSync, readFileSync } from "node:fs";
import { dirname, join } from "node:path";
import { fileURLToPath } from "node:url";
import pg from "pg";

const migrationsDir = join(dirname(fileURLToPath(import.meta.url)), "migrations");

async function main() {
  const client = new pg.Client({ connectionString: process.env.DATABASE_URL });
  await client.connect();

  try {
    await client.query(`
      create table if not exists schema_migrations (
        filename   text primary key,
        applied_at timestamptz not null default now()
      )
    `);

    const { rows } = await client.query("select filename from schema_migrations");
    const applied = new Set(rows.map((r) => r.filename));

    const files = readdirSync(migrationsDir)
      .filter((f) => f.endsWith(".sql"))
      .sort();

    for (const file of files) {
      if (applied.has(file)) continue;
      const sql = readFileSync(join(migrationsDir, file), "utf8");
      console.log(`db:migrate – wende ${file} an…`);
      await client.query("begin");
      try {
        await client.query(sql);
        await client.query("insert into schema_migrations (filename) values ($1)", [file]);
        await client.query("commit");
      } catch (err) {
        await client.query("rollback");
        throw new Error(`Migration ${file} fehlgeschlagen: ${err.message}`, { cause: err });
      }
    }

    console.log("db:migrate – Datenbank ist aktuell.");
  } finally {
    await client.end();
  }
}

main().catch((err) => {
  console.error(err);
  process.exit(1);
});