`db/migrate.ts`

61 lines
/**
 * Runs every .sql file in db/migrations/ in lexical order, idempotent.
 * Records applied filenames in schema_migrations.
 * Constraint: migration files must NOT contain their own BEGIN/COMMIT —
 * the runner wraps each file in a transaction.
 */
import 'dotenv/config';
import { readFileSync, readdirSync } from 'node:fs';
import { join, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';
import { pool } from './pool.ts';

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

async function ensureLedger() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      filename TEXT PRIMARY KEY,
      applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);
}

async function applied(): Promise<Set<string>> {
  const r = await pool.query<{ filename: string }>(`SELECT filename FROM schema_migrations`);
  return new Set(r.rows.map((x) => x.filename));
}

async function main() {
  await ensureLedger();
  const seen = await applied();
  const files = readdirSync(migrationsDir).filter((f) => f.endsWith('.sql')).sort();
  for (const f of files) {
    if (seen.has(f)) {
      console.log(`[skip]   ${f}`);
      continue;
    }
    const sql = readFileSync(join(migrationsDir, f), 'utf8');
    console.log(`[apply]  ${f}`);
    try {
      await pool.query('BEGIN');
      await pool.query(sql);
      await pool.query(`INSERT INTO schema_migrations (filename) VALUES ($1) ON CONFLICT DO NOTHING`, [f]);
      await pool.query('COMMIT');
      console.log(`[done]   ${f}`);
    } catch (e: any) {
      try { await pool.query('ROLLBACK'); } catch {}
      console.error(`[fail]   ${f}: ${e.message}`);
      process.exit(1);
    }
  }
  await pool.end();
  console.log('migrations complete');
}

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