budget-app/db/migrations/001_initial_schema.js

const fs = require('fs');
const path = require('path');

async function runMigrations(pool) {
  // Create migrations tracking table if it doesn't exist
  await pool.query(`
    CREATE TABLE IF NOT EXISTS migrations (
      id         SERIAL PRIMARY KEY,
      name       TEXT NOT NULL UNIQUE,
      applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);

  // Read all .sql files in this directory, sorted by name
  const migrationsDir = __dirname;
  const sqlFiles = fs
    .readdirSync(migrationsDir)
    .filter((f) => f.endsWith('.sql'))
    .sort();

  for (const file of sqlFiles) {
    // Check if this migration has already been applied
    const { rows } = await pool.query(
      'SELECT id FROM migrations WHERE name = $1',
      [file]
    );

    if (rows.length > 0) {
      continue; // already applied
    }

    const sql = fs.readFileSync(path.join(migrationsDir, file), 'utf8');

    // Run the migration in a transaction
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      await client.query(sql);
      await client.query('INSERT INTO migrations (name) VALUES ($1)', [file]);
      await client.query('COMMIT');
      console.log(`Migration applied: ${file}`);
    } catch (err) {
      await client.query('ROLLBACK');
      throw new Error(`Migration failed for ${file}: ${err.message}`);
    } finally {
      client.release();
    }
  }
}

module.exports = { runMigrations };