`db/migrate.js`

65 lines
'use strict';
/**
 * Migration runner. Applies db/schema.sql (idempotent) plus any numbered
 * files in db/migrations/*.sql not yet recorded in schema_migrations.
 * Usage:  node db/migrate.js [--reset]
 *   --reset drops and recreates the public schema first (DESTRUCTIVE, local dev only).
 */
const fs = require('fs');
const path = require('path');
const { pool } = require('./index');

async function main() {
  const reset = process.argv.includes('--reset');
  const client = await pool.connect();
  try {
    if (reset) {
      // eslint-disable-next-line no-console
      console.log('[migrate] --reset: dropping public schema');
      await client.query('DROP SCHEMA public CASCADE; CREATE SCHEMA public;');
    }

    const baseSql = fs.readFileSync(path.join(__dirname, 'schema.sql'), 'utf8');
    await client.query(baseSql);
    console.log('[migrate] base schema applied');

    // ensure bookkeeping table exists (schema.sql creates it too)
    await client.query(
      'CREATE TABLE IF NOT EXISTS schema_migrations (version text PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT now())'
    );

    const migDir = path.join(__dirname, 'migrations');
    const files = fs.existsSync(migDir)
      ? fs.readdirSync(migDir).filter((f) => f.endsWith('.sql')).sort()
      : [];
    for (const f of files) {
      const version = f.replace(/\.sql$/, '');
      const { rowCount } = await client.query(
        'SELECT 1 FROM schema_migrations WHERE version=$1',
        [version]
      );
      if (rowCount) continue;
      const sql = fs.readFileSync(path.join(migDir, f), 'utf8');
      await client.query('BEGIN');
      try {
        await client.query(sql);
        await client.query('INSERT INTO schema_migrations(version) VALUES ($1)', [version]);
        await client.query('COMMIT');
        console.log(`[migrate] applied ${f}`);
      } catch (e) {
        await client.query('ROLLBACK');
        throw e;
      }
    }
    console.log('[migrate] done');
  } finally {
    client.release();
    await pool.end();
  }
}

main().catch((e) => {
  console.error('[migrate] FAILED', e.message);
  process.exit(1);
});