NodeJs and PostgreSQL CRUD Example Application (Part 3)

Written by Data Pilot
November 18, 2019
  • Express
  • NodeJS
  • PostgreSQL
Have a Database Problem? Speak with an Expert for Free
Get Started >>

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.

Screenshot of the NodeJs PostgreSQL CRUD example app

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.

Screenshot of the file and folder hierarchy for the NodeJs EJS Postgress app

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 privileges ROLES.

Use NodeJs and ‘pg’ to connect to a PostgreSQL database

  • Add the client library pg using the function require() 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 library pg 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 HMTL Add 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 the edit function. The id 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 query SELECT 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 method redirect() 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 an UPDATE query, and then use the clause WHERE 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 the id using the keyword WHERE. After that, use the SQL keyword DELETE 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 for employee 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

GIF screenshot of the NodeJs PostgreSQL CRUD example app running in a browser tab

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.

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.