`scripts/migrate.js`

52 lines
#!/usr/bin/env node
// migrate.js — applies any unapplied SQL files from db/migrations/ in lexical order.
// Idempotent: tracks applied filenames in schema_migrations.

require('dotenv').config();
const fs = require('fs');
const path = require('path');
const { pool } = require('../lib/db');

const MIGRATIONS_DIR = path.join(__dirname, '..', 'db', 'migrations');

async function main() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      filename text PRIMARY KEY,
      applied_at timestamptz NOT NULL DEFAULT now()
    );
  `);

  const files = fs.readdirSync(MIGRATIONS_DIR)
    .filter((f) => f.endsWith('.sql'))
    .sort();

  const { rows } = await pool.query('SELECT filename FROM schema_migrations');
  const applied = new Set(rows.map((r) => r.filename));

  let count = 0;
  for (const f of files) {
    if (applied.has(f)) {
      console.log(`[migrate] skip ${f} (already applied)`);
      continue;
    }
    const sql = fs.readFileSync(path.join(MIGRATIONS_DIR, f), 'utf8');
    console.log(`[migrate] apply ${f}`);
    await pool.query('BEGIN');
    try {
      await pool.query(sql);
      await pool.query('INSERT INTO schema_migrations(filename) VALUES ($1) ON CONFLICT DO NOTHING', [f]);
      await pool.query('COMMIT');
      count++;
    } catch (err) {
      await pool.query('ROLLBACK');
      console.error(`[migrate] FAILED ${f}:`, err.message);
      process.exit(1);
    }
  }
  console.log(`[migrate] done — applied ${count} migration(s)`);
  await pool.end();
}

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