get-db-schema-with-cert.js

#!/usr/bin/env node

# This script is for getting the DB schema from a database that requires SSL verification by providing a certificate
# For example, AWS RDS instances will require this
# This script takes a database url and a path to the certificate, then runs the pg_dump command
# 
# This is very useful for tools like dbdiagram.io

const { exec } = require("child_process");
const path = require("path");

const dbUrl = process.argv[2];
const certPath = process.argv[3];

if (!dbUrl) {
    console.error(`No DB URL given`);
    help();
}

if (!certPath) {
    console.error(`No cert path given`);
    help();
}

const url = new URL(dbUrl);
const today = new Date();
const outputFile = `schema-dump-${today.getFullYear()}-${today.getMonth() + 1}-${today.getDate()}.sql`;

// Notice there's a database parsing issue - need to handle the case when pathname might be empty
const dbName = url.pathname.slice(1) || url.hostname.split('.')[0] || 'postgres';

// Set SSL environment variable and password
const env = {
    ...process.env,
    PGSSLROOTCERT: certPath,
    PGSSLMODE: 'verify-ca',
    PGPASSWORD: url.password
};

const command = `pg_dump -h ${url.hostname} -p ${url.port || '5432'} -d ${dbName} -U ${url.username} -s -F p -E UTF-8 -f ${outputFile}`;

console.log(`Executing: ${command}`);
console.log(`Using SSL cert: ${certPath}`);

exec(command, { env }, (error, stdout, stderr) => {
    if (error) {
        console.error(`Error executing pg_dump: ${error.message}`);
        process.exit(2);
    }

    if (stderr) {
        console.error(`pg_dump stderr: ${stderr}`);
    }

    console.log(`Database schema dump saved to ${outputFile}`);
});

function help() {
    console.log(`Usage: node pg-dump.js <postgres-db-url> <cert-path>`);
    process.exit(1);
}