How to connect your NodeJS App to a PostgreSQL instance in ObjectRocket

Written by Data Pilot
April 01, 2020
  • NodeJS
  • ObjectRocket
  • PostgreSQL
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this tutorial, we are going to learn how to connect a NodeJS app to a PostgreSQL instance on the ObjectRocket platform.

Prerequisites

  • You must have created an instance with a PostgreSQL database instance on ObjectRocket If you don’t have an instance, ObjectRocket does offer a free trial that you can create your instance on.
  • You must have node installed Node

Add User to Your PostgreSQL Instance in ObjectRocket

Visit your ObjectRocket dashboard seen below.

Image from Gyazo

In your instance click ‘Add Users’ and add a user as an admin. For this demo we’ve added a user with the following credentials.

  • username: demouser
  • password: demopass

Image from Gyazo

We will be using this username and password to make our connection.

Get the Connection URL

Now you need a connection url to access your instance. To get it, click on your instance, then ‘View More Details’, then the ‘CONNECT’ tab. You’ll see a url similar to below.

Image from Gyazo

Copy this url because we will need it later to make the connection.

Creating a Simple NodeJS App

We’ll start off creating our NodeJS app by creating a project directory called demoapp. In your terminal, navigate to that directory and run the following command. This will setup a node project for you with a package.json file.

1
npm init -y

We will need to install the following dependencies to get started:

  1. express – express is a minimal and flexible Node.js web application framework that provides a robust set of features for web and mobile applications. express
  2. pg – pg is a non-blocking PostgreSQL client for NodeJS. pg

You can install both of these dependencies by running the following command:

1
npm install express pg

This will install both packages and we verify that they are installed by looking at the package.json file. You can see that express and pg are listed under dependencies.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
{
  "name": "demoapp",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "pg": "^8.0.0"
  }
}

Next we create our index.js file and add the following code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
const express = require("express");
const { Client } = require("pg");

const connectionString = "";
const client = new Client({
  connectionString: connectionString
});

client.connect();

var app = express();

app.set("port", process.env.PORT || 4000);

app.listen(4000, function(err, res) {
  if (err) {
    console.log("there was an error", err);
  }
  console.log("database connected");
  console.log("Server is running on Port 4000");
});

As you can see the connectionString is blank. The next step is to paste the connection url we copied earlier into that string. That line should now look similar to this:

1
2
const connectionString =
  "postgres://USERNAME:PASSWORD@ingress.w98sujpz.launchpad.objectrocket.cloud:4158/postgres?sslmode=require";

As you can see, that connection url has placeholders for USERNAME and PASSWORD. Replace those placeholders with the credentials for your user. We’ve updated that line with our users credentials and the line now is:

1
2
const connectionString =
  "postgres://demouser:demopass@ingress.w98sujpz.launchpad.objectrocket.cloud:4158/postgres?sslmode=require";

Let’s take a look at our index.js and explain the parts.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
const express = require("express");
const { Client } = require("pg");

const connectionString =
  "postgres://demouser:demopass@ingress.w98sujpz.launchpad.objectrocket.cloud:4158/postgres?sslmode=require";
const client = new Client({
  connectionString: connectionString
});

client.connect();

var app = express();

app.set("port", process.env.PORT || 4000);

app.listen(4000, function(err, res) {
  if (err) {
    console.log("there was an error", err);
  }
  console.log("database connected");
  console.log("Server is running on Port 4000");
});

Line 1 and 2 import the express and pg packages into index.js file or us to use.

Then we create a new Client using our connectionString add save it to client variable. Next we call the client.connect() function.

Next we call the express function and set the port to 4000.

Now we can run our app with the following command:

1
node index.js

In the terminal if the connection is successful we’ll see this on the terminal:

1
2
database connected
Server is running on Port 4000

Now, we know that we’ve successfully connected to the ObjectRocket database.

Taking Action on the Database

Now that we’ve connected to the database let’s prove that we can take action on the database. We’ll do this by creating a table in the database. We can easily do that by adding this code to our index.js, before we listen to the port.

1
2
3
4
5
6
7
8
9
10
11
12
app.get("/", function(req, res, next) {
  client.query(
    "CREATE TABLE demotable(ID serial PRIMARY KEY,tag VARCHAR (255) NOT NULL,name VARCHAR (255) NOT NULL,description VARCHAR (255),rel VARCHAR (50));",
    function(err, result) {
      if (err) {
        console.log(err);
        res.status(400).send(err);
      }
      res.status(200).send(result);
    }
  );
});

What this code is doing is creating a table called hooks if someone goes to the / route of our app. We’ll show you how to hit the route in a sec.

The code changes have not taken effect though so we need to restart the server.

Let’s stop the server with + .

Now restart the server, by again running the following command in your terminal:

1
node index.js

You’ll see this response indicating that you’ve connected to your database and your express server is running:

1
2
database connected
Server is running on Port 4000

Now visit the route http://localhost:4000/ in a browser, and that will kick-off our table creation process and a new table will be created in our database called demotable.

Image from Gyazo

To confirm this table was created we’re gonna install the tool PGAdmin which will let us connect to our database and view our tables and data locally. You can install PGAdmin here.

Connecting to your PostgreSQL Instance on ObjectRocket using PGAdmin

After downloading and installing, you will be prompted to add a password to secure your servers once you run it.

Image from Gyazo

The next step will be creating a connection to the database in ObjectRocket.

Click on the “Add New Server”. You’ll be presented with a modal where you’ll add your connection details.

Under general: Name is the only thing required. You can call this anything, we’ll call ours “Demo”.

Name: “Demo”

Image from Gyazo

Now go to the connection tab.

For your Host name/address we will use a portion of the connection string from ObjectRocket. We use the host name which is everything between the @ symbol and the colon.

For our example, this was our connection url:

1
postgres://demouser:demopass@ingress.w98sujpz.launchpad.objectrocket.cloud:4158/postgres?sslmode=require

And this will be the host name to enter into PGAdmin:

1
ingress.w98sujpz.launchpad.objectrocket.cloud

The PORT will be the number in the connection url after the colon:

1
4158

The Username and Password are the credentials you set up earlier:

  • Username: demouser
  • Password: demopass

Your connection should look similar to the following:

Image from Gyazo

Click Save.

When you do, you will be connected to your server instance.

Image from Gyazo

Under your server, you can navigate down to:

1
Servers -> Demo (Your server name) -> Databases -> postgres -> Schemas -> public -> Tables

Under tables, you will see our new table created!

Image from Gyazo

Conclusion

In this tutorial we covered the basics of setting up a connection between a NodeJS app and a PostgreSQL instance on ObjectRocket. If you want to get started, ObjectRocket offers a free trial.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.