migrate.ts

import 'dotenv/config';
import { readFileSync, readdirSync } from 'fs';
import { resolve, join } from 'path';
import { Client } from 'pg';

/**
 * A deliberately small migration runner: plain numbered .sql files in
 * migrations/, tracked in a schema_migrations table. No ORM — FreClean's
 * schema is stable and explicit SQL is easier to review line by line than a
 * generated one, consistent with the "avoid unnecessary complexity"
 * principle used throughout this ecosystem (see freclean-payment's Celo
 * client for the same reasoning applied elsewhere).
 */

const MIGRATIONS_DIR = resolve(__dirname, '../migrations');

async function main() {
  const connectionString = process.env.DATABASE_URL;
  if (!connectionString) {
    throw new Error('DATABASE_URL is not set. Copy .env.example to .env and configure it.');
  }

  const client = new Client({ connectionString });
  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 applied = new Set(
      (await client.query('SELECT filename FROM schema_migrations')).rows.map((r) => r.filename),
    );

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

    let ranAny = false;

    for (const file of files) {
      if (applied.has(file)) continue;
      ranAny = true;
      console.log(`Applying ${file}...`);
      const sql = readFileSync(join(MIGRATIONS_DIR, file), 'utf8');

      await client.query('BEGIN');
      try {
        await client.query(sql);
        await client.query('INSERT INTO schema_migrations (filename) VALUES ($1)', [file]);
        await client.query('COMMIT');
        console.log(`  ✅ ${file}`);
      } catch (err) {
        await client.query('ROLLBACK');
        throw new Error(`Migration ${file} failed and was rolled back: ${(err as Error).message}`);
      }
    }

    if (!ranAny) {
      console.log('No pending migrations — database is up to date.');
    }
  } finally {
    await client.end();
  }
}

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