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

• 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:

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:

• Make a connection to obtain the PostgresSQL table’s CRUD functionality so that you can continue the setup Node Postgres CRUD application instructions:

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:

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:

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:

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:

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.

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.

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

• 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

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:

• 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.