NodeJs Express PostgreSQL Tutorial (Part 2)

Written by orkb
November 12, 2019
  • NodeJS
  • PostgreSQL

NodeJs Express PostgreSQL tutorial introduction

If you’re interested in building a PostgreSQL web app using Node.js and the Express framework library, this tutorial will walk you through the entire process. In the first article of our tutorial series, we explained how to set up the project for the application and the Express framework library; we also showed how to run the Express server in Node. This article will pick up where we left off and complete the series: We’ll show how you can make RESTful API calls to a Postgres database and then return the records as a bootstrapped HTML table.

If you’d like to review how to set up the Node project and install the modules using npm, you can review the first article in the series.

HTML for the NodeJs Express application’s front end

Let’s begin with a recap of the index.html file that we created in the first part of this tutorial series:

<!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 HTML form is used for the HTTP POST method request to the application’s back end:

  <!-- 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 sure that the action parameter in the form tag has the correct domain (IP address), port, and Express route for the NodeJs PostgreSQL application.

Create a Bootstrap container for the PostgreSQL table data

Let’s look at some HTML that creates a Bootstrap container to hold 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

The following code shows the JavaScript used to append PostgreSQL data to the Bootstrap HTML container we created in the previous section:

  <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

The next phase of our project will involve the JavaScript file for the Node app. Be sure to use the .js file extension and check that the "main" field in your package.json file matches the name of your script.

Use Node’s require() function to include the necessary modules

The code shown below 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");

NOTE: You’ll need to install the Express and pg Node module libraries with NPM before executing the Javascript file with Node. Use the npm i express and npm i pg commands to install these libraries.

Create a new application instance of the Express library

Next, we’ll call the express() method to instantiate an app instance. This instance will be given routes for the HTTP requests later on:

// 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: Be sure to include the bodyParser instance created earlier using the app’s use() method in the above code.

Set the constants for the NodeJs Express PostgreSQL app

In the following block of code, we set a port for the application. We also set the path for the HTML file that was 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");

After that, we 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: Be sure that you pass the correct credentials for your PostgreSQL database and table when you call the Pool() method.

Create an HTML table from the PostgreSQL table data

In this section, we’ll declare a function that will create an HTML table from our PostgreSQL table data. The following code will declare the function and accept the table’s column names and row data as arguments:

// function that will return Postgres records as an HTML table
function createHtmlTable( tableRows, tableCol ) {

    // open a <script> tag for the string
    let htmlData = '<script>var tableData = `<thead>\n<tr>'
    console.log(`\ncreateHtmlTable:`)

The above code declares an HTML string that the function will return that’s designed to assign a JavaScript string for the front end.

Iterate over Postgres the table column names

Use the JavaScript’s map() function to iterate over the PostgreSQL table’s column names and append them to the string enclosed within HTML table header (<th>) tags:

// use map() to iterate over the table column names
tableCol.map(col => {
  col = col[0].toUpperCase() + col.substring(1, col.length);
  htmlData += `\n<th>${col}</th>`;
  console.log(`col: ${col}`);
});

Iterate over the PostgreSQL table rows for the HTML string

The following code does the same for the table’s row records as well, but this time encloses them in <td> tags:

// open the head and body tags for the table
htmlData += `\n</thead>\n<tbody>\n`;
console.log(`tableRows: ${typeof tableRows}`);

// iterate over the table row data with map()
tableRows.map(row => {
  // open a new table row tag for each Postgres row
  htmlData += `\n<tr>`;

  // iterate over the row's data and enclose in <td> tag
  for (val in row) {
    console.log(`row[val]: ${row[val]}`);
    htmlData += `\n<td>${row[val]}</td>`;
  }

  // close the table row <tr> tag
  htmlData += `\n</tr>`;
});

Make sure to include this last bit of code to close the table body tags and return the HTML string before ending the function:

    // close the table body and script tags
    htmlData += '\n</tbody>`;</script>'

    // return the string
    return htmlData
}

‘GET’ route for the PostgreSQL Express app

In this section of code, we’ll use the path for the HTML file and the Express sendFile() method to return the HTML data with a GET HTTP request in cases where no parameter has been passed by the user:

// '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 PostgreSQL Express app

Now that we’ve reviewed the GET route, let’s take a look at the POST route. The code will make API calls to the PostgreSQL database that we selected in the Pool object instance above. It will then pass the returned data to the createHtmlTable() function declared earlier.

Get the query for the Express app’s POST request

In the following block of code, we parse the query that is passed to the app’s post() method from the front end of the application:

// '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}`)

Check if the user’s PostgreSQL query is an integer value

In this example web application, the code is designed to query for data in the table’s “int” column, so we’ll need to make sure that the parameter passed is a valid integer before we make our SQL query to the Postgres database:

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

// only make an API call if the user query is valid
if (isNaN(userQuery) == false && userQuery.length > 0) {

// concatenate an SQL string to SELECT the table column names
let sqlColNames = `SELECT * FROM information_schema.columns WHERE table_name = $1;`

Assuming the above conditions are met, the code will concatenate a SQL string designed to return all of the table’s column names.

Get the column names for the PostgreSQL table

We’ll use the client instance of the pg module to make a query call using the sqlColNames string. We’ll insert the table name into the string when we call the method to avoid SQL injection attacks:

// create a Promise object for the query
client
.query(sqlColNames, [tableName])

.then(colResp => {

// access the "rows" Object attr
let colRows = colResp["rows"]

// use map() function to create an array of col names
let colNames = colRows.map(colKeys => {
return colKeys["column_name"]
})

This code will return the column names and then make another query API call to get the table row data:

// concatenate another string for the table row data
let sqlSelectRows = `SELECT * FROM ${tableName} WHERE int >= $1`
console.log(`sqlSelectRows: ${sqlSelectRows}`)

client
.query(sqlSelectRows, [userQuery])

.then(rowResp => {

// get the row data from 'rows' attribute
let rowData = rowResp.rows

Call the function to create the HTML table data

The final part of the Express POST route passes the Postgres data to the function we created earlier:

// call function to create HTML data
let html = createHtmlTable( rowData, colNames )
console.log(`HTML table data: ${html}`)

// send the HTML file data and table data back to front end
resp.send( htmlData + `
` + html )
})
})
} else {
// send an error message if the query is not an integer
resp.send(htmlData + `
ERROR: You must input an integer value.

`)
}

Have the PostgreSQL application listen on the port

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

At this point, we’ve completed our project– it’s time to test the application. Save the JavaScript file, then use the command node app.js to run the application. You can also use nodemon to run the app if you’d like to make changes while the app is running.

You might get an error with the message Error: listen EADDRINUSE: address already in use; if this happens, you’ll have to grep for the process using the open port:

sudo lsof -i -P -n | grep 3456

Then, simply use sudo kill -9 followed by the PID to kill the process.

Conclusion to the NodeJs Express Postgresql tutorial

Now that we’ve tested our application on the command line, let’s try it out in a browser. Navigate to http://127.0.0.1:3456/ or localhost:3456 in your browser, and you should see the app load the HTML to the front end. The application should return all of table’s records where the value in the int column is greater than or equal to the integer submitted.

This article concludes our two-part NodeJS Express PostgreSQL tutorial with a detailed look at the code needed to create our web application. In this second installment of our series, we showed you how to make RESTful API calls to a Postgres database and return the records as a bootstrapped HTML table. With these instructions as a guide, you’ll be able to create your own web apps using PostgreSQL, Node.js and the Express framework library.

NodeJs app returning POST request query for PostgreSQL table data

Just the Code

Shown below is the complete HTML markup used in the Postgres 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());

// declare a constant for the PostgreSQL table
const tableName = `some_table`;
const port = process.env.port || 3456;

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

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

// function that will return Postgres records as an HTML table
function createHtmlTable(tableRows, tableCol) {
  // open a <script> tag for the string
  let htmlData = "<script>var tableData = `<thead>\n<tr>";
  console.log(`\ncreateHtmlTable:`);

  // use map() to iterate over the table column names
  tableCol.map(col => {
    col = col[0].toUpperCase() + col.substring(1, col.length);
    htmlData += `\n<th>${col}</th>`;
    console.log(`col: ${col}`);
  });

  // open the head and body tags for the table
  htmlData += `\n</thead>\n<tbody>\n`;
  console.log(`tableRows: ${typeof tableRows}`);

  // iterate over the table row data with map()
  tableRows.map(row => {
    // open a new table row tag for each Postgres row
    htmlData += `\n<tr>`;

    // iterate over the row's data and enclose in <td> tag
    for (val in row) {
      console.log(`row[val]: ${row[val]}`);
      htmlData += `\n<td>${row[val]}</td>`;
    }

    // close the table row <tr> tag
    htmlData += `\n</tr>`;
  });

  // close the table body and script tags
  htmlData += "\n</tbody>`;</script>";

  // return the string
  return htmlData;
}

// '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");

  // only make an API call if the user query is valid
  if (isNaN(userQuery) == false && userQuery.length > 0) {
    // concatenate an SQL string to SELECT the table column names
    let sqlColNames = `SELECT * FROM information_schema.columns WHERE table_name = $1;`;

    // create a Promise object for the query
    client
      .query(sqlColNames, [tableName])

      .then(colResp => {
        // access the "rows" Object attr
        let colRows = colResp["rows"];

        // use map() function to create an array of col names
        let colNames = colRows.map(colKeys => {
          return colKeys["column_name"];
        });

        // concatenate another string for the table row data
        let sqlSelectRows = `SELECT * FROM ${tableName} WHERE int >= $1`;
        console.log(`sqlSelectRows: ${sqlSelectRows}`);

        client
          .query(sqlSelectRows, [userQuery])

          .then(rowResp => {
            // get the row data from 'rows' attribute
            let rowData = rowResp.rows;

            // call function to create HTML data
            let html = createHtmlTable(rowData, colNames);
            console.log(`HTML table data: ${html}`);

            // send the HTML file data and table data back to front end
            resp.send(htmlData + `<br>` + html);
          });
      });
  } else {
    // send an error message if the query is not an integer
    resp.send(
      htmlData +
        `<br><p color="red">ERROR: You must input an integer value.</p>`
    );
  }
});

var server = app.listen(port, function() {
  console.log(
    `\nPostgres Node server is running on port: ${server.address().port}`
  );
});
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.