Question

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


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

Hello @protoahmed,

I am really sorry to know that you are facing issues while connecting your node.js app to a Postgres Database(Dev/Managed).

Here’s a code snippet that you can use and I’m sure this will work as it has been tested out.

//config/database.js

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'postgres',
        host: env('DATABASE_HOST', '127.0.0.1'),
        port: env.int('DATABASE_PORT', 5432),
        database: env('DATABASE_NAME', 'strapi'),
        username: env('DATABASE_USERNAME', 'postgres'),
        password: env('DATABASE_PASSWORD', ';'),
        ssl: {
          rejectUnauthorized: false,
        },
      },
      options: {},
    },
  },
});

The database details used in the snippet above have been defined as the environment variables in the app.

Here’s a documentation on the environment variables that you can refer to. https://docs.digitalocean.com/products/app-platform/how-to/use-environment-variables/#databases

The code should work for Dev as well as Managed Database.

We will definitely ensure all our documents are up to date and have the relevant information.

As far as the CA Cert is concerned, you can always define it as an environment variable in the below format. We have this documented here

Key: CA_CERT
Value: ${your_db.CA_CERT}

As of now, I would request you to test it out. In case you see issues, please raise a ticket to our support team and we shall have it addressed as soon as possible.

Cheers, Taha Fatima