NodeJs Express PostgreSQL Tutorial (Part 1)

NodeJs Express PostgreSQL tutorial introduction

This is part one of a NodeJs express PostgreSQL tutorial series explaining how to create a web application for Postgres. The NodeJs express web application is a simple framework that allows for the easy creation of mobile and web applications using just a few lines of code. The goal of this tutorial is to create a web app that will retrieve PostgreSQL records in a bootstraped HTML table. Once set up, the app will be able to query data using the NodeJs JavaScript runtime environment, the pg Postgres client module and the Express framework library.

Prerequisites for using NodeJs and Express with PostgreSQL

  • PostgreSQL must be installed and working properly.

  • A Postgres database and an existing table holding some records. The JavaScript code used in this NodeJs express PostgreSQL tutorial is designed to automatically retrieve a PostgreSQL table’s column name information for the HTML table.

Note that the example code used in this NodeJs express PostgreSQL tutorial series will use the SQL SELECT keyword to retrieve Postgres records. These records must have a specific INTEGER value in a column and use an HTML form to make a POST method HTTP request.

Create a project directory for the NodeJs Express application

Create a directory for the web app project using mkdir in a terminal. Enter the directory with the cd command followed by the folder name. Once inside the project directory, create a new JavaScript file (.js) for the Node web app and an HTML file for the application’s front end.

Use the Node Package Manager to install the necessary Node modules

A Node package manager, referred to as NPM, must be installed on the machine in order to install the necessary packages for the Postgres and Express web application. If it is already installed, executing the following command will return the current NPM version number:

npm -V

Now execute the npm init command inside the project directory to initialize the package.json file for the project. Execute the text prompts for the package name, version number and other criteria. Always type yes and hit the “Return” key whenever the system asks “Is this OK?” at the end of the prompts. The following screenshot provides an example:

Screenshot of NodeJs application setup initialization with NPM

NOTE: Things can be change anything after the initial setup by editing the package.json file, however, it is critical to enter the correct values at the system prompts. Of particular importance, the value for the "main" field must match the JavaScript file for the Node application. An example follows:

Screenshot of the package.json file for a Node application

Use NPM to install the necessary Node modules for the Postgres web app

Execute either the NPM i or install commands to install a NodeJs package module for the project. The next section explains how to install the Express and the pg PostgreSQL client Node modules.

Install the Express web application framework for NodeJs

The following command will install the Express framework for the Postgres project directory:

npm install express

Now follow the same procedure for installing the pg PostgreSQL client library:

npm install pg

NOTE: The -g flag can be used to install Node modules globally.

Install the NodeJs body-parser package for parsing middleware

If using Express version 4.0 or later, the framework for the Node body parser must be installed as it no longer comes bundled with Express. Execute the following command to install the Node body parser:

npm install body-parser --save

Install nodemon for the Node application

Installing the nodemon command-line interface utility will allow changes to be made to the Node application without having to restart the Express server each time. Execute the following command to install the nodemon module globally:

npm i nodemon -g

The following command will install nodemon as a dependency for the project:

npm install nodemon --save-dev

As shown below, executing the nodemon command followed by the JavaScript file name will run the Node application via the CLI utility:

nodemon app.js

NOTE: Getting a nodemon: command not found response indicates the utility was not properly installed in the path. Try reinstalling the application with sudo npm install -g --force nodemon. Alternatively, try installing and running the application locally with npx nodemon.

HTML for the NodeJs Express application’s front end

Create an HTML file for the web application inside the project directory and name it index.html. The following HTML code sets up the front end of the app and uses Bootstrap 4 to style the table:

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8" />

    <title>ObjectRocket Articles</title>
    <!-- BS4 source -->
    <link
     rel="stylesheet"
     href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
     integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
     crossorigin="anonymous"
   />

    <script
     src="https://code.jquery.com/jquery-3.4.1.js"
     integrity="sha256-WpOohJOqMqqyKL9FccASB9O0KwACQJpFTUBLTYOVvVU="
     crossorigin="anonymous"
   ></script>

    <!-- JSON 2 HTML source -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/json2html/1.3.0/json2html.min.js"></script>

    <!-- CSS for doc body -->
    <style>
      body {
        margin: 1rem;
      }
    </style>
  </head>
  <body>
    <h1>ObjectRocket Node Postgres App</h1>
  </body>
</html>

Create an HTML form for HTTP requests to the Node app

The following code is an HTML form for the HTTP POST method request to the application’s backend:

  <!-- HTML form for the NodeJs Postgres app -->
  <form method="POST" action="http://127.0.0.1:3456/query">
    <div class="form-group">
      <label for="query-input">Postgres Integer Query:</label>
      <input id="query-input" class="form-control"
     type="number" name="query" placeholder="0"></input>
      <br>
    </div>

    <button class="btn btn-success" type="submit" value="Submit">Submit Query</button>
  </form>
  <br>

NOTE: Make absolutely certain the above action parameter for the -form- tag has the correct domain port, or IP address, as well as an Express route for the NodeJs PostgreSQL application.

Create a Bootstrap container for the PostgreSQL table data

The following HTML code creates a Bootstrap container for a PostgreSQL table’s record data and column names:

<!-- BS4 table begins here -->
<div class="container">
  <h2>Postgres-NodeJs Table Example</h2>
  <table id="postgres-table" class="table table-striped">
    <!--
       HTML data returned from NodeJs app will go here
     -->
  </table>
</div>

Add PostgreSQL record data to the HTML Bootstrap container

Use the following JavaScript to append the PostgreSQL data to the Bootstrap container:

  <script>
    window.addEventListener('load', function() {
      let pgTable = document.getElementById("postgres-table");
      try {
        pgTable.innerHTML = tableData;
        console.log(`\ntableData: ${tableData}`);
      } catch(err) {
        pgTable.innerHTML = "";
      }
    });

  </script>

</body>
</html>

Create code for the NodeJs web application

Next, create the JavaScript code for the NodeJs PostgreSQL application. The following code uses Node’s require() function to add the Node modules to the script:

const express = require("express");
const { Pool } = require("pg");
const path = require("path");
const fs = require("fs");
const bodyParser = require("body-parser");

Create a new application instance of the Express library

Use the following express() method call to instantiate an app instance that will be given routes for future HTTP requests:

// create a new Express app server object
const app = express();

// parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: false }));

// parse application/json
app.use(bodyParser.json());

NOTE: Make certain to include the bodyParser instance, created earlier in the tutorial, using the app’s use() method in the above code.

Set the constants for the NodeJs Express PostgreSQL app

The following code will set a port for the application and obtain the path for the HTML file created earlier:

// set the port for the Node application
const port = process.env.port || 3456;

// set the file path for the HTML file
const htmlPath = path.join(__dirname + "/index.html");

The following JavaScript code will create a new client instance of the pg Node-PostgreSQL library:

// create a client instance of the pg library
const client = new Pool({
  user: "objectrocket",
  host: "localhost",
  database: "some_database",
  password: "1234",
  port: "5432"
});

NOTE: Make certain the correct credentials for the PostgreSQL database and table are passed to the above Pool() method call.

Create

routes for the NodeJs Express PostgreSQL application

The following code returns the HTML file, created earlier, using the app instance’s resp.sendFile() method call:

// 'GET' route for the web app
app.get("/", (req, resp) =- {
console.log(`req: ${req}`);
console.log(`resp: ${resp}`);

// send the HTML file back to the front end
resp.sendFile(htmlPath);
});

Note that the second POST request route will return the HTML file along with some additional data. The following code parses the request (req) object data sent from the front end by accessing its body attribute:

// 'POST' route for the web app
app.post('/query', function (req, resp) {

// parse the user's query
let userQuery = req.body.query
console.log(`nuserQuery: ${typeof userQuery}`)
console.log(`${userQuery}`)

NOTE: Make sure the request attribute name matches the route’s name, for example: Access req.body.myreq if the route’s name is /myreq.

The final step is to access the HTML data by passing the HTML file name to the fs library’s readFileSync() method call as follows:

    // load the HTML file into the Node app's memory
    let htmlData = fs.readFileSync('./index.html', 'utf8')

    // Concatenate an HTML string for the Postgres data
    let html = `<script>var tableData = "QUERY: ${userQuery}";</script>`

    // send the HTML file and query response to the front end
    resp.send( htmlData + `<br>` + html )

})

var server = app.listen(port, function () {
    console.log(`\nPostgres Node server is running on port: ${server.address().port}`)
})

Now save the JavaScript file and then use the command node app.js to run the application. Additionally, as shown below, the nodemon app can be used to make changes while the app is running.

Screenshot of the HTML NodeJs app returning POST request query

Conclusion to the NodeJs Express Postgresql tutorial

This was part one in a NodeJs express PostgreSQL tutorial series explaining how to create a web application for Postgres. Part one covered how to create a project directory for the NodeJs Express application, use the Node package manager to install the Node modules, install the Express web application framework for NodeJs and install the NodeJs body-parser package. The article also covered how to install nodemon for the Node application, create an HTML form for HTTP requests to the Node app, create a Bootstrap container for the PostgreSQL table data and set the constants and set routes for the NodeJs Express PostgreSQL application. Part two of the series will cover how to make an API request to PostgreSQL in order to return table records as HTML table data.

Just the Code

Following is the complete code for the NodeJs Express PostgreSQL application:

<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">

  <title>ObjectRocket Articles</title>
  <!-- BS4 source -->
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

  <!-- CSS for doc body -->
  <style>
    body {
      margin: 1rem;
    }
  </style>

</head>
<body>


  <h1>ObjectRocket Node Postgres App</h1>

  <!-- HTML form for the NodeJs Postgres app -->
  <form method="POST" action="http://127.0.0.1:3456/query">
    <div class="form-group">
      <label for="query-input">Postgres Integer Query:</label>
      <input id="query-input" class="form-control"
     type="number" name="query" placeholder="0"></input>
      <br>
    </div>

    <button class="btn btn-success" type="submit" value="Submit">Submit Query</button>
  </form>
  <br>

  <!-- BS4 table begins here -->
  <div class="container">
    <h2>Postgres-NodeJs Table Example</h2>
    <table id="postgres-table" class="table table-striped">

      <!--
       HTML data returned from NodeJs app will go here
     -->

    </table>
  </div>

  <script>
    window.addEventListener('load', function() {
      let pgTable = document.getElementById("postgres-table");
      try {
        pgTable.innerHTML = tableData;
        console.log(`\ntableData: ${tableData}`);
      } catch(err) {
        pgTable.innerHTML = "";
      }
    });

  </script>

</body>
</html>

JavaScript code for the Node web app’s backend

const express = require("express");
const { Pool } = require("pg");
const path = require("path");
const fs = require("fs");
const bodyParser = require("body-parser");

// create a new Express app server object
const app = express();

// parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: false }));

// parse application/json
app.use(bodyParser.json());

// set the port for the Node application
const port = process.env.port || 3456;

// set the file path for the HTML file
const htmlPath = path.join(__dirname + "/index.html");

// create a client instance of the pg library
const client = new Pool({
  user: "objectrocket",
  host: "localhost",
  database: "some_database",
  password: "1234",
  port: "5432"
});

// 'GET' route for the web app
app.get("/", (req, resp) => {
  console.log(`req: ${req}`);
  console.log(`resp: ${resp}`);

  // send the HTML file back to the front end
  resp.sendFile(htmlPath);
});

// 'POST' route for the web app
app.post("/query", function(req, resp) {
  // parse the user's query
  let userQuery = req.body.query;
  console.log(`\nuserQuery: ${typeof userQuery}`);
  console.log(`${userQuery}`);

  // load the HTML file into the Node app's memory
  let htmlData = fs.readFileSync("./index.html", "utf8");

  // Concatenate an HTML string for the Postgres data
  let html = `var tableData = "QUERY: ${userQuery}";`;

  // send the HTML file and query response to the front end
  resp.send(htmlData + `` + html);
});

var server = app.listen(port, function() {
  console.log(
    `\nPostgres Node server is running on port: ${server.address().port}`
  );
});

Pilot the ObjectRocket Platform Free!

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

Get Started

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.