NodeJs Express PostgreSQL Tutorial (Part 2)
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | <!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:
1 2 3 4 5 6 7 8 9 10 11 12 | <!-- 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:
1 2 3 4 5 6 7 8 9 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
1 2 3 4 5 | 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:
1 2 3 4 5 6 7 8 | // 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:
1 2 3 4 5 | // 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:
1 2 3 4 5 6 7 8 | // 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:
1 2 3 4 5 6 | // 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:
1 2 3 4 5 6 | // 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | // 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:
1 2 3 4 5 6 | // 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:
1 2 3 4 5 6 7 8 | // '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:
1 2 3 4 5 6 7 | // '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:
1 2 3 4 5 6 7 8 | // 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | // 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:
1 2 3 4 5 6 7 8 9 10 11 | // 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | // 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
1 2 3 4 5 | 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:
1 | 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.
Just the Code
Shown below is the complete HTML markup used in the Postgres application:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | <!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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | 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}` ); }); |
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started