NodeJs and PostgreSQL CRUD Example Application (Part 3)
NodeJs and PostgreSQL CRUD example introduction
Welcome to the last lesson in the multiple-series tutorial, “NodeJs and PostgreSQL CRUD Example Application (Part 3).” In Parts 1 and 2, you learned how to get started with the application. As you built it, you utilized the JavaScript embedded templates (EJS) module to create HTML from the js code, and that code was returned to the front end of your application. This was done so that you can experience smoother app development in a shorter time span.
Now in Part 3, we’ll continue to explain the setup Node Postgres CRUD application process and also give a NodeJs PostgreSQL CRUD example. This tutorial will go over how to export functions to the front end. It will also explain about making API calls using Node’s pg
client library for Postgres. Finally, it will demonstrate how to execute and use the app’s CRUD functions. Let’s begin.
Setting up the NodeJs PostgreSQL CRUD example application
Please review Parts 1 and 2 of the multiple-series “NodeJs and PostgreSQL CRUD Example Application” before proceeding on with this lesson which is Part 3.
NOTE: The sub-directories and files for the setup Node Postgres CRUD application were created in Part 2 of NodeJs and PostgreSQL CRUD Example Application.” It should resemble something similar to the image shown directly below.
JavaScript code for the PostgreSQL CRUD operations
Make a project source directory folder and name it
/routes
, if you haven’t yet.Edit the
./routes/employee.js
file so that you can have the method HTTP return responses.
./routes/employee.js
1 2 | >routes employee.js |
- Make the file
employee.js
if you don’t have one. It’s for making a PostgreSQL database connection with the granted privilegesROLES
.
Use NodeJs and ‘pg’ to connect to a PostgreSQL database
- Add the client library
pg
using the functionrequire()
from Node:
1 2 3 4 5 6 | const { Client } = require("pg"); // Declare constants for the PostgreSQL Pool connection const postgresUser = "some_username"; const postgresDb = "some_db"; const postgresPass = "1234"; |
NOTE: Postgres parameters for the database are the source for the constants (
const
) after the client librarypg
is added.Prior to going live, be sure to save to your environmental variables all of the confidential database credential values. Hard-coding data that is confidential is ill-advised.
Declare a connection string using a Template string literal
- Use the snytax
${}
to pass the values:
1 | var connectionString = `postgres://${postgresUser}:${postgresPass}@localhost:5432/${postgresDb}`; |
- Make a connection to obtain the PostgresSQL table’s CRUD functionality so that you can continue the setup Node Postgres CRUD application instructions:
1 2 3 4 5 | // Connect to the Postgres database using pg Client library const client = new Client({ connectionString: connectionString }); client.connect(); |
Use module.exports to send the pg API functions for Postgres to the Express app
The integrated module exports
is handy because it enables developers to seamlessly export functions from one script to a different one.
Bring API call results to your application’s front end with the method exports
. To do this, send the API query functions of PostgreSQL to the Express server.
- It’s best to accomplish the task in parts. Therefore, begin by getting the code your table for your
employee
record data returned to the front end, like this:
1 2 3 4 5 6 7 8 9 | exports.list = function(req, res) { client.query("SELECT * FROM employee", function(err, result) { if (err) { console.log(err); res.status(400).send(err); } res.render("employee/list", { title: "Employee", data: result.rows }); }); }; |
NOTE: The method
render()
is where the record rows were passed to by the module template language of the EJS library. And that action is also what causes the data from the PostgreSQL record to be returned back to EJS.
Export a function that will create an HTML button to add a PostgreSQL record
- Call the method
render()
from the EJS library to place on the front end an HMTLAdd Employee
button:
1 2 3 | exports.add = function(req, res) { res.render("employee/add", { title: "Add Employee" }); }; |
Export a function that finds a PostgreSQL record with ‘SELECT’
- First, get the variable
id
by using theedit
function. Theid
it will reference is in the body of the parameter of the request. Before the record can be edited though, it must be found. So, it searches for and finds the record using the querySELECT
like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | exports.edit = function(req, res) { // get the Postgres record ID from the request 'params' body var id = req.params.id; client.query("SELECT * FROM employee WHERE id=$1", [id], function( err, result ) { if (err) { console.log(err); res.status(400).send(err); } res.render("employee/edit", { title: "Edit Employee", data: result.rows }); }); }; |
Export a function that will save the changes made to the PostgreSQL table record using ‘UPDATE’
- Use the statement
INSERT
to call the methodredirect()
from the library EJS. It will then move through the URL/employee
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | exports.save = function(req, res) { var cols = [req.body.name, req.body.address, req.body.email, req.body.phone]; client.query( "INSERT INTO employee(name, address, email, phone) VALUES($1, $2, $3, $4) RETURNING *", cols, function(err, result) { if (err) { console.log("Error. Not Saved! : %s ", err); } res.redirect("/employee"); } ); }; |
Export a function that will save the changes made to the PostgreSQL table record using ‘UPDATE’
- To do this, have the client
pg
perform anUPDATE
query, and then use the clauseWHERE
for the Postgres record you want to evaluate.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | exports.update = function(req, res) { // Postgres table column names go here var cols = [ req.body.name, req.body.address, req.body.email, req.body.phone, req.params.id ]; client.query( "UPDATE employee SET name=$1, address=$2,email=$3, phone=$4 WHERE id=$5", cols, function(err, result) { if (err) { console.log("Error. Updating : %s ", err); } res.redirect("/employee"); } ); }; |
Export the function to delete PostgreSQL table records
- Start with exporting the function
delete
. You’re also querying to locate theid
using the keywordWHERE
. After that, use the SQL keywordDELETE
to take out a record.
1 2 3 4 5 6 7 8 9 10 | exports.delete = function(req, res) { var id = req.params.id; client.query("DELETE FROM employee WHERE id=$1", [id], function(err, rows) { if (err) { console.log("Error deleting : %s ", err); } res.redirect("/employee"); }); }; |
CSS styling and JavaScript files for the public folder
To finish the setup Node Postgres CRUD application steps, place a couple of important files in the public folder. The first one you want to add is the cascading style sheets file. After that, add the main.js file that contains the code for the JavaScript functions.
- Add the CSS file to format your PostgreSQL table on the front end of your NodeJs PostgreSQL CRUD example.
./public/css/style.css
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 | * { box-sizing: border-box; } html { width: 100%; height: 100%; padding: 0; margin: 0; } body { font: 14px/1 Arial, sans-serif; background: rgb(177, 240, 211); text-align: center; } .page { padding: 50px; } .page-data { width: 70%; } .data-btn { padding-bottom: 10px; text-align: left; } .data-table table { border-collapse: collapse; width: auto; } .data-table th, .data-table td { border: 1px solid #bbb; max-width: 300px; padding: 8px 16px; } .data-table th { background-color: lightgrey; border-width: 1px; } .data-table td { border-width: 1px; } input[type="text"], textarea { width: 100%; padding: 6px 10px; margin: 8px 0; box-sizing: border-box; border: 2px solid #ccc; } .label { color: #555; vertical-align: middle; } button { background-color: #008cba; border: none; color: white; padding: 7px 15px; text-align: center; text-decoration: none; display: inline-block; font-size: 14px; } a { text-decoration: none; } a.menu_links { text-align: center; display: inline-block; font-size: 25px; } hr { width: 50%; } .center { margin-left: 30%; margin-right: auto; padding-left: 22%; } table.center { margin-left: 35%; margin-right: auto; } |
- Perform a URL site redirect using the object
window.location
. The example below shows that the function foremployee
is where the URL will be directed.
./public/js/main.js
1 2 3 4 5 6 | function addUser() { window.location.href = "/employee/add"; } function cancelAdd() { window.location.href = "/employee"; } |
Conclusion to the use of NodeJs and PostgreSQL to create a CRUD example
- Go to the project root directory.
__NOTE: If you’re using UNIX, input
cd
, first, and then move up one level in the directory to find the root directory.
- Execute your NodeJs PostgreSQL CRUD example with this command:
1 | node app.js |
- From your browser, go to the http://localhost:5005/.
This concludes the multiple-series tutorial “NodeJs and PostgreSQL CRUD Example Application (Part 3).” Parts 1 and 2 explained the setup Node Postgres CRUD application. This lesson, Part 3 explained how to export the necessary functions on the front end to run your NodeJs PostgreSQL CRUD example. You learned about using the pg
client library from Node to make API calls too. The functions to create, read, update, and delete are basic to computer programming. Moving forward, refer to these steps in all three lessons for your NodeJs PostgresSQL app development needs.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started