How to connect to Digital Oceans managed database with Node and PG (postgres)

When creating a managed database on Digital Ocean, it is not immediately clear on how to later connect to this said database.

In this example, I used Postgresql with the excellent "pg" node library to connect to my database.


Step one

Add your local dev ip to the trusted source. This is easy, just head over to the Settings-tab and add your ip under Trusted sources. They will most likely be so nice to give you the suggestion to add your ip. If you have any machine on digital ocean or elsewhere that you know will be connecting, be sure to add it here now or later as well.


Step two

This is one easy step, all you need to do is to download the ca cert to your computer and move the file into your project. I put mine in a new folder called certs/do/cert.crt. After you're done with that, copy your credentials because we're going to use them in the next step.


Step three, the last step

Now finally the code part. This is my example code from a default controller generated by the express-generator. You should probably store the connection variables as environment variables but if you are careless like me, sometimes it's nice to test the connection out by simply including it in the code.

Remember to replace the cert string to whatever directory you are using. To why I am using path is simply because I enjoy to see a full directory path if anything would go wrong.

const fs = require('fs');
const path = require("path");
const { Client } = require('pg');

const config = {
  database: 'mydb',
  host: 'somedb.ondigitalocean.com',
  port: 1337,
  user: 'myuser',
  password: 'mypassword',
  ssl: {
    rejectUnauthorized: false,
    ca: fs.readFileSync(path.resolve('certs/do/cert.crt')).toString(),
    mode: 'require'
  },
}
const db = new Client(config);

(async () => {
  await db.connect()
  const res = await db.query('SELECT $1::text as message', ['Hello world!'])
  console.log(res.rows[0].message) // Hello world!
  await db.end()
})();

If everything went well, you will now see a "Hello world" being logged when running your program.

Thank you for reading my guide on how to connect to a manged database on Digital Ocean. If you like this guide, don't hesitate to link it to other people in need of this how-to.

If you want to follow me on twitter, that is acceptable as well, @SantasDev.

Show Comments