Deploying node, postgres -- lack of information in digitalocean resources

DigitalOcean does not yet have a solid guide to connecting a node app to a postgres database. There is plenty of information but no script that is guaranteed to work and is up-to-date and best-practice; a barebones example that I can follow step-by-step that would set me on my way.

I switched from development database to managed database, thinking that the extra cash would solve all my problems. But nothing. It’s still too complex. Searching around on the internet yields more useful info than whatever is on digitalocean. Why is that?! Aren’t we customers? Why should I waste so much time trying to get stuff to work?

I have my node app set up quite nicely. Github syncing with the app on the cloud. I pushed several updates happily. Then I signed up for a development postgres database. That’s where it all went downhill.

There is no recommended script to connect via node to postgres. Whatever is here is out of date, and you will find customers, in the community posts, struggling to make connections and queries work.

I switched to managed database to get my hands on the ca-certificate.crt file, thinking it’s the key, but it wasn’t. I downloaded the file locally and got local psql to access the remote database. Yay. But it is fiendishly difficult to find - let alone understand - a “model” nodejs script that will let me manipulate the database via the app. Most scripts I find online do not follow the latest practices of the node-postgres module. But then digitalocean doesn’t have much to offer either! Is the ca-certificate.crt file already there on the server? Why is its location not in the environment variable, pre-set? Why on earth does this most basic of scripts not produce any results?

var express = require('express');
var router = express.Router();

const url = require('url')

const { Pool, Client, types } = require("pg");

require("dotenv").config();

const { NODE_ENV } = process.env;
const isProduction = NODE_ENV === "production";

const connectionString = `postgresql://${process.env.PG_USER}:${process.env.PG_PASSWORD}@${process.env.PG_HOST}:${process.env.PG_PORT}/${process.env.PG_DATABASE}`;

const pool = new Pool({
  connectionString: isProduction ? process.env.DATABASE_URL : connectionString,
  ssl: {
    rejectUnauthorized: false,
  },
});

const client = new Client({
  user: process.env.PGUSER,
  password: process.env.PGPASSWORD,
  host: process.env.PGHOST,
  database: process.env.PGDATABASE,
  port: process.env.PGPORT,
  ssl: {
    rejectUnauthorized: false,
    ca: process.env.CACERT,
  }
});

// Return "NUMERIC" types as floating-point numbers.
types.setTypeParser(1700, (val) => parseFloat(val));

class Database {
  constructor() {
    this.connect();
  }

  connect() {
    this.pool = pool;

    this.pool.on("error", (err) => {
      console.error('Unexpected error on idle client "DATABASE" class: ', err);
      process.exit(-1);
    });
  }

  async myTestQuery() {
    const client = await this.pool.connect();
    const data = await client.query(
      `
        SELECT * FROM users;
      `
    );

    client.release();
    return data;
  }

  async query(...args) {
    const client = await this.pool.connect();
    const data = await client.query(...args);
    client.release();
    return data;
  }
}

db = new Database()
console.log(db.myTestQuery())
//module.exports = new Database();
//module.exports = router;

Here is the .env

PG_USER= HIDDEN
PG_PASSWORD= HIDDEN
PG_HOST= HIDDEN.db.ondigitalocean.com
PG_PORT= 25060
PG_DATABASE= HIDDEN
NODE_ENV=development
SSLMODE=require