Question

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

Posted October 3, 2021 149 views
Node.jsPostgreSQLDigitalOcean Managed PostgreSQL DatabaseDigitalOcean App Platform

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

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.

×
Submit an Answer
1 answer

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

  • Hi @tahafatima

    Many thanks for your prompt reply.

    Perhaps I did not make myself clear: I want to push the local codebase to the server and I want code that works both locally and live.

    First, I want the code to work locally via “npm start” on my local machine. Therefore, I need to feed the app all the information to connect to and query the remote database. That’s where I am at the moment. The code I pasted earlier does not produce any errors but it does not seem to retrieve any data.

    As I stated before, using psql, I am able from the local machine to connect to the database and issue the query and get results. (I had to add my IP address to the trusted list.)

    I have a .env file on the local machine, which I feed through the node package “dotenv” into my node app. This is the information it contains:

    PG_USER= ***
    PG_PASSWORD= ***
    PG_HOST= ***.b.db.ondigitalocean.com
    PG_PORT= 25060
    PG_DATABASE= ***
    NODE_ENV=development
    SSLMODE=require
    CA_CERT='./ca-certificate.crt'
    

    This information, together with the script in my original post, throws no errors, and the database “logs & queries” on DO servers indicate that the query was received. But no data was sent.

    [15-1] pid=3469936,user=***,db=***,app=[unknown],client=82.42.MY.IP LOG: connection authorized: user=*** database=*** SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256, compression=off)
    

    The query issued from the localhost increments the query counter too. So, it was received. Why it does not return with the data is beyond me.

    Once I get my local script to fetch data from the remote database without issue, I will then move to worry about amending the codebase to work remotely after pushing and deploying.

    Your reply and the code in it is useful - and I appreciate your time - but the information missing is:

    • How do I set up the code to work locally (while querying the db remotely)?
    • What is a good skeleton code to work locally and remotely?
    • Hi @protoahmed,

      I understand that you are unable to fetch the data. The code that I shared earlier should work on your local too. I would request you to give it a shot.

      Please ensure the environment variables mentioned in the code are stored in the .env file.

      Do let me know how that goes!

      Cheers,
      Taha Fatima