How to connect your NodeJS App to a PostgreSQL instance in ObjectRocket
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.
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
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.
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:
express
–express
is a minimal and flexible Node.js web application framework that provides a robust set of features for web and mobile applications. expresspg
–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
.
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.
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”
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:
Click Save
.
When you do, you will be connected to your server instance.
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!
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started