apply_schema_to_managed_db.js

Go to the documentation of this file.

#!/usr/bin/env node
// Apply schema and migrations to a PostgreSQL database using node-postgres (no psql required)
// Usage:
//   node apply_schema_to_managed_db.js [DATABASE_URL]
// If DATABASE_URL arg is omitted, tries:
//   1) devops/digitalocean/db_credentials.txt (DATABASE_URL=...)
//   2) process.env.DATABASE_URL
 
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
 
const ROOT = path.resolve(__dirname, '../..');
const DB_DIR = path.join(ROOT, 'database');
const CREDS_FILE = path.join(ROOT, 'devops', 'digitalocean', 'db_credentials.txt');
 
function loadDatabaseUrlFromCreds() {
  if (!fs.existsSync(CREDS_FILE)) return null;
  const content = fs.readFileSync(CREDS_FILE, 'utf8');
  const match = content.match(/^DATABASE_URL=(.*)$/m);
  return match ? match[1].trim() : null;
}
 
async function runSql(pool, filePath) {
  if (!fs.existsSync(filePath)) {
    console.log(`[SKIP] ${path.basename(filePath)} not found`);
    return;
  }
  const sql = fs.readFileSync(filePath, 'utf8');
  if (!sql.trim()) {
    console.log(`[SKIP] ${path.basename(filePath)} is empty`);
    return;
  }
  console.log(`[APPLY] ${path.basename(filePath)} ...`);
  try {
    await pool.query(sql);
    console.log(`[OK] ${path.basename(filePath)}`);
  } catch (err) {
    console.warn(`[WARN] Failed applying ${path.basename(filePath)}: ${err.message}`);
  }
}
 
(async () => {
  try {
    let databaseUrl = process.argv[2] || loadDatabaseUrlFromCreds() || process.env.DATABASE_URL;
    if (!databaseUrl) {
      console.error('[ERR] No DATABASE_URL provided or found in creds/env.');
      process.exit(1);
    }
 
    // Ensure we control SSL settings via client options, not URL flags
    databaseUrl = databaseUrl.replace(/([?&])sslmode=require(&|$)/, (m, p1, p2) => (p2 === '&' ? p1 : ''));
    const pool = new Pool({ connectionString: databaseUrl, ssl: { rejectUnauthorized: false } });
 
    console.log('[INFO] Testing connection ...');
    await pool.query('SELECT 1');
    console.log('[OK] Connected.');
 
    // Apply init.sql
    await runSql(pool, path.join(DB_DIR, 'init.sql'));
 
    // Apply migrations in order (base entities first, then enhancements, then RLS, then dependent features)
    const migrations = [
      '2025_11_04_create_base_entities.sql',
      '2025_10_31_enhance_contracts.sql',
      '2025_10_31_enhance_invoices_products.sql',
      '001_add_tenant_id.sql',
      '2025_11_03_add_purchase_orders.sql',
      '2025_11_03_add_multi_tenant_rls.sql',
      '20251103_add_contracts_sla_hours.sql',
      '20251103_tenant_settings.sql',
      '2025_11_04_add_missing_user_theme_products_integrations.sql',
    ];
 
    for (const m of migrations) {
      await runSql(pool, path.join(DB_DIR, 'migrations', m));
    }
 
    await pool.end();
    console.log('');
    console.log('==========================================');
    console.log('✅ Schema and migrations applied successfully');
    console.log('==========================================');
  } catch (err) {
    console.error('[ERR] Migration failed:', err.message);
    process.exit(2);
  }
})();