How to Use Vultr Managed Databases for PostgreSQL with NodeJS

by cobra_admin
January 2, 2023
106

Introduction

Vultr 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.

Prerequisites

To test the guide:

Provision an Ubuntu 20.04 server.
Create a non-root sudo user.
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.
Deploy a managed PostgreSQL database cluster.
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

1. Set Up a Sample Database

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.

2. Create a Database Gateway Module

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.

The `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.

3. 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.

The `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.

Introduction Vultr 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…

Introduction Vultr 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…

How to Repair a Corrupted MySQL Table

How to Implement PostgreSQL Database Transactions with Python on Ubuntu 20.04

Cobra Softwares Blog