Author: Francis Ndungu
Last Updated: Tue, Dec 13, 2022Vultr provides production-ready PostgreSQL database clusters that you can use with Node.js to create mission-critical applications. Managed databases automate the most challenging aspects of database administration, allowing you to focus on your app.
This guide shows you how to use the Node.js pg library on Ubuntu 20.04 server to pass queries to a managed PostgreSQL database cluster. The library supports all functions for creating data-driven applications like parameterized queries.
To test the guide:
Install Node.js using Option 2: (Install via PPA Version).
You should skip step 2, "Install Express.js," because this tutorial doesn't require Express.js dependency.
Navigate to the PostgreSQL database cluster Connection Details under the Overview tab. This guide uses the following sample connection details:
username: vultradmin
password: EXAMPLE_POSTGRESQL_PASSWORD
host: SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com
port: 16751
Every data-driven application requires a database to store data permanently. You need to set up a sample database and a table. In a production environment, you may require more than one table based on the complexity of your application. Follow the steps below to initialize the database:
Begin by updating your server's package information index.
$ sudo apt update
Install the postgresql-client package. This is a lightweight client package for interacting with a managed PostgreSQL cluster without installing a complete PostgreSQL package on your server.
$ sudo apt install -y postgresql-client
Run the command below to log in to your managed PostgreSQL cluster. Replace SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com with the correct host for the PostgreSQL database cluster.
$ psql -h SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com -p 16751 -U vultradmin defaultdb
Output.
Password for user vultradmin:
Enter your PostgreSQL cluster database password and press ENTER to proceed. Then, ensure you get the following output.
Output.
defaultdb=>
Issue the command below to create a sample company_db database.
defaultdb=> CREATE DATABASE company_db;
Output.
CREATE DATABASE
Connect to the new company_db database.
defaultdb=> \c company_db;
Output.
...
You are now connected to database "company_db" as user "vultradmin".
Create a customers table. This table stores customers' information. Later, this guide shows you how to execute INSERT, UPDATE, DELETE, and SELECT commands from Node.js code to interact with the customers table.
company_db=> CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
Output.
CREATE TABLE
Insert sample data into the customers table to ensure you've got the correct schema.
company_db=> INSERT INTO customers (first_name, last_name) VALUES ('JOHN', 'DOE');
INSERT INTO customers (first_name, last_name) VALUES ('MARY', 'SMITH');
INSERT INTO customers (first_name, last_name) VALUES ('PETER', 'JONES');
Output.
...
INSERT 0 1
Query the customers to verify the data.
company_db=> SELECT
customer_id,
first_name,
last_name
FROM customers;
Output.
customer_id | first_name | last_name
-------------+------------+-----------
1 | JOHN | DOE
2 | MARY | SMITH
3 | PETER | JONES
(3 rows)
Log out from the managed PostgreSQL database cluster.
company_db=> \q
After setting up the database and sample table, proceed to the next step to create a central Node.js database module for interacting with your managed PostgreSQL database cluster.
Node.js allows you to package your application into different modules to organize complex functionalities that you can reuse in multiple locations throughout your source code. When designing Node.js applications that interact with databases, it's conventional to create a central database module. Later, you can include this module in every file that requires access to the database. Follow the steps below to create a database gateway module:
Create a new project directory for your application to separate your source code from system files.
$ mkdir project
Switch to the new project directory.
$ cd project
Open a new postgresql_gateway.js file on a text editor.
$ nano postgresql_gateway.js
Enter the following information into the postgresql_gateway.js file. Replace SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com and EXAMPLE_POSTGRESQL_PASSWORD with the correct PostgreSQL database cluster hostname and password.
class postgresql_gateway {
connectDb() {
const { Client } = require('pg');
const client = new Client({
user: "vultradmin",
database: "company_db",
password: "EXAMPLE_POSTGRESQL_PASSWORD",
port: 16751,
host: "SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com",
ssl: { rejectUnauthorized: false }
});
client.connect();
return client;
}
execute_query(callBack, queryString, paramValues) {
var db_client = this.connectDb();
db_client.query(queryString, paramValues, (err, res) => {
if (err) {
callBack(err, null);
} else {
callBack(null, res.rows);
}
db_client.end();
}) ;
}
save_data(jsonData, callBack) {
var paramValues = [];
paramValues.push(jsonData.first_name);
paramValues.push(jsonData.last_name);
var queryString = "insert into customers (first_name, last_name) values ($1, $2) RETURNING customer_id, first_name, last_name";
this.execute_query(callBack, queryString, paramValues);
}
update_data(jsonData, callBack) {
var paramValues = [];
paramValues.push(jsonData.first_name);
paramValues.push(jsonData.last_name);
paramValues.push(jsonData.customer_id);
var queryString = "update customers set first_name = $1, last_name = $2 where customer_id = $3 RETURNING customer_id, first_name, last_name";
this.execute_query(callBack, queryString, paramValues);
}
delete_data(jsonData, callBack) {
var paramValues = [];
paramValues.push(jsonData.customer_id);
var queryString = "delete from customers where customer_id = $1 RETURNING customer_id, first_name, last_name";
this.execute_query(callBack, queryString, paramValues);
}
query_data(customerId, callBack) {
var queryString = "select * from customers";
var paramValues = [];
if (customerId != "") {
queryString += " where customer_id = $1";
paramValues.push(customerId);
}
this.execute_query(callBack, queryString, paramValues)
}
}
module.exports = postgresql_gateway;
Save and close the postgresql_gateway.js file.
postgresql_gateway.js file explained:The postgresql_gateway.js file contains one class module (postgresql_gateway) with six different methods.
class postgresql_gateway {
connectDb() {
...
}
execute_query(callBack, queryString, paramValues) {
...
}
save_data(jsonData, callBack) {
...
}
update_data(jsonData, callBack) {
...
}
delete_data(jsonData, callBack) {
...
}
query_data(customerId, callBack) {
...
}
}
module.exports = postgresql_gateway;
The six methods in the postgresql_gateway class module perform the following functions:
connectDb(): This method uses the managed PostgreSQL database cluster's credentials to connect to the database and return a reusable client connection using the return client; statement.
execute_query(callBack, queryString, paramValues): This method uses the var db_client = this.connectDb(); statement to connect to the PostgreSQL database. Then, the method calls the db_client.query(queryString, paramValues, ...) function to execute different database queries.
save_data(jsonData, callBack): This method accepts a JSON payload (first_name and last_name) containing the customer's details and then calls the execute_query(...) method to save data to the database using an INSERT command.
update_data(jsonData, callBack): This method accepts a JSON payload containing a customer_id and queries the database to find a match. Then, the update_data(...) method calls the execute_query(...) method to update the first_name and last_name fields of the record matching the customer_id.
delete_data(jsonData, callBack): This method accepts a JSON payload containing the customer_id of the record you want to delete. The delete_data(...) method then calls the this.execute_query(...) method to send a DELETE command to the PostgreSQL database.
query_data(customerId, callBack): This method uses the select * from customers statement to fetch records from the PostgreSQL customers table. If you request a single record, the query_data() method uses the following declaration to append a filter parameter to the query.
...
if (customerId != "") {
queryString += " where customer_id = $1";
paramValues.push(customerId);
}
...
The RETURNING customer_id, first_name, last_name statement at the end of the SQL statements allows you to retrieve the field values for the affected rows.
The module.exports = postgresql_gateway; statement at the end of the postgresql_gateway.js file allows you to import and use the postgresql_gateway module in other Node.js files using the following declarations.
const postgresql_gateway = require('./postgresql_gateway.js');
var dg = new postgresql_gateway();
dg.save_data(JSON.parse(json_payload), callBack);
dg.update_data(JSON.parse(json_payload), callBack);
dg.delete_data(JSON.parse(json_payload), callBack);
dg.query_data(customerId, callBack);
The postgresql_gateway module is now ready. Follow the next step to create the application's entry point.
Every Node.js application requires an entry point. This is a file that runs when you start the application. This guide uses the main.js file to execute the application. Follow the steps below to create the file:
Open a new main.js on a text editor.
$ nano main.js
Enter the following information into the main.js file.
const http = require('http');
const postgresql_gateway = require('./postgresql_gateway.js');
const hostname = 'localhost';
const port = 8080;
const server = http.createServer(httpHandler);
server.listen(port, hostname, () => {
console.log(`Server running at http://${hostname}:${port}/`);
});
function httpHandler(req, res) {
var dg = new postgresql_gateway();
var json_payload = "";
req.on('data', function (data) {
json_payload += data;
});
req.on('end', function () {
function callBack(err, result) {
var response = {};
if (err) {
response.error = err.message;
} else {
response.data = result;
}
res.write(JSON.stringify(response, null, 4));
res.end();
}
switch (req.method) {
case "POST":
dg.save_data(JSON.parse(json_payload), callBack);
break;
case "PUT":
dg.update_data(JSON.parse(json_payload), callBack);
break;
case "DELETE":
dg.delete_data(JSON.parse(json_payload), callBack);
break;
case "GET":
const url = require('url');
const queryparams = url.parse(req.url, true).query;
var customerId = "";
if (queryparams.customer_id) {
customerId = queryparams.customer_id
}
dg.query_data(customerId, callBack);
break;
}
});
}
Save and close the main.js file.
main.js file explained:The two lines at the beginning of the main.js file load the http server module and the custom postgresql_gateway database gateway module.
const http = require('http');
const postgresql_gateway = require('./postgresql_gateway.js');
...
The http server module creates a local web server that listens for incoming connections on port 8080. The const server = http.createServer(httpHandler); delegates incoming HTTP to the custom httpHandler(){...} function. The server.listen(port, hostname, ..); statement tells the web server to listen for incoming requests on the defined port and host.
const hostname = 'localhost';
const port = 8080;
const server = http.createServer(httpHandler);
server.listen(port, hostname, () => {
console.log(`Server running at http://${hostname}:${port}/`);
});
The httpHandler(req, res) {..} function runs most of the application's logic.
Under the httpHandler() function, you're creating a new database gateway object using the var dg = new postgresql_gateway(); statement.
The following declarations allow you to capture the JSON payload from HTTP clients' requests when creating new customers, updating customer details, and deleting customers from the database.
...
var json_payload = "";
req.on('data', function (data) {
json_payload += data;
});
...
Under the req.on(...){...} function, you're defining a callBack(err, result) function that fires every time you make an HTTP request to the server. Then, you're using the Node.js switch (req.method) {...} statement to evaluate the HTTP method and route HTTP requests to the appropriate database functions.
req.on('end', function () {
function callBack(err, result) {
...
res.write(JSON.stringify(response, null, 4));
res.end();
}
switch (req.method) {
...
}
}
The following list shows your application's HTTP request methods and the matching database functions that run the requests.
POST: Runs the dg.save_data(JSON.parse(json_payload), callBack); method.
PUT: Runs the dg.update_data(JSON.parse(json_payload), callBack); method.
DELETE: Runs the dg.delete_data(JSON.parse(json_payload), callBack); method.
GET: Runs the dg.query_data(customerId, callBack); method.
After setting up all the required Node.js source code files, proceed to the next step to test your application.
The final step in this guide is using the Node.js npm module to initialize and set up your project's attribute. Then, use the npm module to install the pg module and run some tests using the Linux curl command. Follow the steps below:
Ensure the npm package is up to date.
$ sudo npm install npm -g
Output.
...
removed 14 packages, changed 73 packages, and audited 223 packages in 7s
...
Use the npm package to initialize your project.
$ npm init
Enter the following responses when you receive the prompts followed by ENTER:
package name: (project) : ENTER
version: (1.0.0) : ENTER
description: Node.js and PostgreSQL application ENTER
entry point: (main.js) ENTER
test command: ENTER
git repository: ENTER
keywords: node.js, postgresql ENTER
author: test author ENTER
license: (ISC) ENTER
...
About to write to /home/francis/project/package.json: {...}
Is this OK? (yes) yes ENTER
Use the npm package to install the PostgreSQL pg module for Node.js.
$ npm install pg
Output.
...
added 15 packages, and audited 16 packages in 2s
Use the node command to run the application. Remember, the main.js file is the application's entry point.
$ node main.js
Output.
Server running at http://localhost:8080/
Do not run any other command in your active SSH terminal window because the previous command has a blocking function.
Establish another SSH session on your server and run the following Linux curl commands to send HTTP requests to the application.
Retrieve all customers using the HTTP GET command:
$ curl -X GET http://127.0.0.1:8080
Output.
{
"data": [
{
"customer_id": 1,
"first_name": "JOHN",
"last_name": "DOE"
},
{
"customer_id": 2,
"first_name": "MARY",
"last_name": "SMITH"
},
{
"customer_id": 3,
"first_name": "PETER",
"last_name": "JONES"
}
]
}
Retrieve a specific customer by appending the customer_id in the URL.
$ curl -X GET http://127.0.0.1:8080?customer_id=2
Output.
{
"data": [
{
"customer_id": 2,
"first_name": "MARY",
"last_name": "SMITH"
}
]
}
Create a new customer using the HTTP POST command and a JSON payload containing the customer's details.
$ curl -X POST http://127.0.0.1:8080/ -H 'Content-Type: application/json' -d '{"first_name" : "HENRY", "last_name" : "ALLAN"}'
Output.
{
"data": [
{
"customer_id": 4,
"first_name": "HENRY",
"last_name": "ALLAN"
}
]
}
Update an existing customer using the HTTP PUT method. For this command, you must define the customer_id of the record you want to update and the new values for the first_name and last_name fields.
$ curl -X PUT http://127.0.0.1:8080/ -H 'Content-Type: application/json' -d '{"customer_id" : 4, "first_name" : "FRED", "last_name" : "ALEX"}'
Output.
{
"data": [
{
"customer_id": 4,
"first_name": "FRED",
"last_name": "ALEX"
}
]
}
Delete a customer using the HTTP DELETE command. Include the customer_id of the record you want to delete in a JSON payload.
$ curl -X DELETE http://127.0.0.1:8080/ -H 'Content-Type: application/json' -d '{"customer_id" : 4}'
Output.
{
"data": [
{
"customer_id": 4,
"first_name": "FRED",
"last_name": "ALEX"
}
]
}
The application is working as expected, and you can run the SELECT, INSERT, UPDATE, and DELETE operations without any problems.
This guide shows you how to implement the Node.js pg module to interact with a managed PostgreSQL database on Ubuntu 20.04 server. Although this guide demonstrated the PostgreSQL idea using a single table, you may create additional tables depending on your application's logic. The managed PostgreSQL database cluster allows you to implement a cloud database application without any complicated installations and configuration procedures.
Check out the links below for more information on using Vultr's managed databases: