Art-gallery/scripts/backup-db-data.js

const fs = require('fs');
const path = require('path');
const { execFileSync } = require('child_process');
const pg = require('pg');
const {
  assertProdDatabase,
  assertDevDatabase,
  loadDevPgConfig,
  loadProdPgConfig,
  rootDir,
} = require('./db-env');
const { printCliResult } = require('./lib/cli-result');

const { Client } = pg;

const backupDir = path.join(rootDir, 'db', 'DataBackup');
const fromProd = process.argv.includes('--prod');
const config = fromProd ? loadProdPgConfig() : loadDevPgConfig();

if (fromProd) {
  assertProdDatabase(config.database);
} else {
  assertDevDatabase(config.database);
  if (config.database.endsWith('_prod')) {
    console.error('Refusing backup of production database without --prod. Use: npm run prod:db:backup');
    process.exit(1);
  }
}

function sqlLiteral(value) {
  if (value === null || value === undefined) return 'NULL';
  if (typeof value === 'boolean') return value ? 'TRUE' : 'FALSE';
  if (value instanceof Date) return `'${value.toISOString()}'`;
  if (typeof value === 'number' || typeof value === 'bigint') return String(value);
  // node-pg returns JS arrays for Postgres array columns (e.g. users.permissions TEXT[])
  if (Array.isArray(value)) {
    if (value.length === 0) return `ARRAY[]::text[]`;
    return `ARRAY[${value.map((item) => sqlLiteral(item)).join(', ')}]`;
  }
  if (typeof value === 'object') {
    return `'${JSON.stringify(value).replace(/'/g, "''")}'`;
  }
  return `'${String(value).replace(/'/g, "''")}'`;
}

function timestampSlug(date = new Date()) {
  const pad = (n) => String(n).padStart(2, '0');
  return [
    date.getFullYear(),
    pad(date.getMonth() + 1),
    pad(date.getDate()),
    '_',
    pad(date.getHours()),
    pad(date.getMinutes()),
    pad(date.getSeconds()),
  ].join('');
}

async function main() {
  fs.mkdirSync(backupDir, { recursive: true });

  const client = new Client(config);
  await client.connect();

  const dbNameRes = await client.query('SELECT current_database() AS name');
  const dbName = dbNameRes.rows[0].name;

  const tablesRes = await client.query(
    `SELECT table_name
     FROM information_schema.tables
     WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
     ORDER BY table_name`,
  );

  const lines = [
    '-- Gallery PostgreSQL data backup',
    `-- Generated: ${new Date().toISOString()}`,
    `-- Database: ${dbName}`,
    '-- Format: INSERT statements (data only)',
    '',
  ];

  let totalRows = 0;

  for (const { table_name: tableName } of tablesRes.rows) {
    const colsRes = await client.query(
      `SELECT column_name
       FROM information_schema.columns
       WHERE table_schema = 'public' AND table_name = $1
       ORDER BY ordinal_position`,
      [tableName],
    );
    const columns = colsRes.rows.map((row) => row.column_name);
    const dataRes = await client.query(`SELECT * FROM "${tableName}"`);
    const rowCount = dataRes.rowCount ?? 0;
    totalRows += rowCount;

    lines.push(`-- TABLE ${tableName} (${rowCount} rows)`);

    if (rowCount === 0) {
      lines.push('');
      continue;
    }

    const quotedCols = columns.map((c) => `"${c}"`).join(', ');
    for (const row of dataRes.rows) {
      const values = columns.map((col) => sqlLiteral(row[col])).join(', ');
      lines.push(`INSERT INTO "${tableName}" (${quotedCols}) VALUES (${values});`);
    }
    lines.push('');
  }

  await client.end();

  const slug = timestampSlug();
  const txtName = `${dbName}_data_${slug}.txt`;
  const zipName = `${dbName}_data_${slug}.zip`;
  const txtPath = path.join(backupDir, txtName);
  const zipPath = path.join(backupDir, zipName);

  fs.writeFileSync(txtPath, lines.join('\n'), 'utf8');

  execFileSync(
    'powershell',
    [
      '-NoProfile',
      '-Command',
      `Compress-Archive -LiteralPath '${txtPath.replace(/'/g, "''")}' -DestinationPath '${zipPath.replace(/'/g, "''")}' -Force`,
    ],
    { stdio: 'inherit' },
  );

  const relTxtPath = path.relative(rootDir, txtPath).replace(/\\/g, '/');
  console.log(`Backup written: ${txtPath}`);
  console.log(`Archive written: ${zipPath}`);
  console.log(`Tables: ${tablesRes.rowCount}, rows: ${totalRows}`);

  printCliResult({
    script: 'backup-db-data',
    ok: true,
    summary: `Backup complete for "${dbName}".`,
    details: [
      `File: ${relTxtPath}`,
      `Tables: ${tablesRes.rowCount}, rows: ${totalRows}`,
    ],
  });
}

main().catch((error) => {
  console.error(error);
  printCliResult({
    script: 'backup-db-data',
    ok: false,
    summary: error.message || String(error),
  });
});