NodeJs and PostgreSQL CRUD Example Application (Part 2)

Written by orkb

November 18, 2019

Express
NodeJS
PostgreSQL

NodeJs and PostgreSQL CRUD Example Introduction

Welcome to “NodeJs and PostgreSQL CRUD Example Application (Part 2),” a tutorial multiple-series. In this lesson, you’ll learn how to add the required folder subdirectories and files to structure the layout for your NodeJs PostgreSQL CRUD example. You’ll also discover how to incorporate embedded JavaScript template (EJS) functionality into your application.

If you’re familiar with the setup Node Postgres CRUD application steps and want to bypass the detailed instructions, go to Just the Code.

Folder Structure for the Node, Postgres, and EJS Application

Structure the subdirectories for the needed files and Express routing for your app so that it will work correctly:
The folder order should look similar to this one:

app.js
> node_modules
package.json
> public
> css
style.css
> js
main.js
> routes
employee.js
index.js
> views
> employee
add.ejs
edit.ejs
list.ejs

Create the subfolders and files for the Node Express and EJS functionality

Make the directories with mkdir, and create each file separately with touch. That’s the long way.
Alternatively, get it done spontaneously by writing a .sh shell file extension script using Bourne Again Shell (bash) commands. Discover how to do this later in this lesson.

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

>NOTE: Remember to use NPM to install the necessary node_modules for the NodeJs PostgreSQL CRUD example. Your Node app’s source directory is where you want to run npm i path ejs express body-parser pg. If you don’t see the folder node_modules, install the modules now.

Use a bash script to create the project files and subdirectories for the Node app

For the setup Node Postgres CRUD application, make the project and the subdirectories with a shell bash script like this:

mkdir my-app && cd my-app
nano create_files.sh

Use Sublime with subl create_files.sh command or use the General Public License (GNU) nano text editor. Copy and paste the commands for your bash script:

#!/bin/bash

# create the app.js file
touch app.js

# create parent dir 'public' and its files and subdir
mkdir -p public/css && touch public/css/style.css
mkdir -p public/js && touch public/js/main.js

# create 'routes' dir and its files
mkdir routes && touch routes/employee.js routes/index.js

# create parent dir 'views' and its files and subdir
mkdir -p views/employee && touch views/employee/add.ejs
touch views/index.ejs
touch views/employee/edit.ejs views/employee/list.ejs

Screenshot of the nano terminal editor writing bash script to create files for Node app

>NOTE: Save it in your Node project parent directory. After you edit the script in nano by pushing CTRL+O, exit nano. Next, use the command bash to run the script for your NodeJs PostgreSQL CRUD example.

bash create_files.sh

Show all the current directory files in alphabetical order with the command ls -R .
The result should look like this:

app.js public/ views/
create_files.sh routes/

./public:
css/ js/

./public/css:
style.css

./public/js:
main.js

./routes:
employee.js index.js

./views:
employee/ index.ejs

./views/employee:
add.ejs edit.ejs list.ejs

Screenshot of a UNIX terminal listing ls du all of the Postgres Node app's files and subdirectories

>NOTE: Use npm init to do a project initialization. Follow the prompts to finish the process for making the file package.json .

Create a JavaScript file for the NodeJs and Postgres CRUD app

Your app will run using the Express server. Make a .js file extension JavaScript file for that and it will also be used to show the routes for HTTP that will be used for the methods POST and GET.

app.js

The function require() obtains the essential libraries and modules to make them script accessible:

const express = require('express')
const bodyParser = require('body-parser')
const path = require('path')

Use this code to get the constants’ attributes and methods for your NodeJs PostgreSQL CRUD example:

var employee = require('./routes/employee')
var routes = require('./routes')
var app = express()

Save the values with the library method set(), and then make a localhost connection with the app instance. This way, you’ll obtain the routes and connect to the database:

// Use the set() method to store values
app.set('port', process.env.PORT || 5005)

// Set EJS engine as the default engine
app.set('view engine', 'ejs')

>NOTE: You must set the engine as EJS as default; otherwise, Node won’t use it.

Instruct the Express app to use the body-parser Node module

The parameters in the API request’s body are accessible because of the middleware bodyParse method. Use NPM to get the method if you don’t already have it installed.

Tell the app to use bodyParser parse the data from JSON that it received from an HTTP request:

app.use(bodyParser.json())
app.use(bodyParser.urlencoded({ extended: true }))
app.use(express.static(path.join(__dirname, 'public')))

>NOTE: The front end of the app uses the public folder. It contains stylistic code within the CSS file and other scripting pertinent to the front end.

Set the Express routes for the HTTP ‘GET’ and ‘POST’ requests

Use GET to obtain the requests from HTTP that are received by Express server:

app.get('/', routes.index)
app.get('/employee', employee.list)
app.get('/employee/add', employee.add)
app.get('/employee/delete/:id', employee.delete)
app.get('/employee/edit/:id', employee.edit)

Now, continue the setup Node Postgres CRUD application development with the POST for the HTTP requests:

app.post('/employee/add', employee.save)
app.post('/employee/edit/:id', employee.update)

>NOTE: Further in the tutorial, you’ll make route declarations for /routes/employee.js.

app.listen(app.settings.port, function () {
console.log('Server is running on Port 5005. Press CTRL+C to stop server.')
})

Edit the EJS files in the ‘views’ directory

Make sure the directory /views contains every EJS templating file (.ejs extension) so you can construct and show markup in HTML when you code with JavaScript.

Edit the ‘index.ejs’ file in ‘views’

With this code, the title data for Postgres displays using EJS markup and no functionalty:

./views/index.ejs

<!DOCTYPE html>
<html>
<head>
<title><%=title%></title>

</head>

<div class="page-header">
<h2><%=title%></h2>
<hr>
<a href="/employee" class="menu_links">Employee</a>
</div>
</div>
</body>
</html>

EJS code for the Node app’s ‘add’ and ‘edit’ functions

Give the subfolder employee the code for adding, deleting and listing by first creating an ejs file for the setup Node Postgres CRUD application.

Grant the user the capability to add a new record to the PostgreSQL table on the front end. Set up the file add.ejs to generate an HTML form for this purpose:

./views/employee/add.ejs

<!DOCTYPE html>
<html>
<head>
<title>Crud sample using PostgreSQL and NodeJs - <%=title%></title>

</head>

<div class="page-header"><h2><%=title%></h2></div>
<div class="page-data">
<div class="data-btn">
+ Add Employee</button>
</div>
<div class="data-table">
<table class="center">
<tr>
<th width="50px">No</th>
<th>Name</th>
<th>Address</th>
<th>Phone</th>
<th>Email</th>
<th width="120px">Action</th>
</tr>

<% if(data.length){
for(var i = 0;i < data.length;i++) { %>
<tr>
<td><%=(i+1)%></td>
<td><%=data[i].name%></td>
<td><%=data[i].address%></td>
<td><%=data[i].phone%></td>
<td><%=data[i].email%></td>
<td>
<a class="a-inside edit" href="../employee/edit/<%=data[i].id%>">Edit</a>
<a class="a-inside delete" href="../employee/delete/<%=data[i].id%>">Delete</a>
</td>
</tr>
<% }
}else{ %>
<tr>
<td colspan="6">No user</td>
</tr>
<% } %>
</table>
</div>
</div>
</body>
</html>

Conclusion to the use of NodeJs and PostgreSQL to create a CRUD example

This completes Part 2 of the multiple-series tutorial about the NodeJs PostgreSQL CRUD example application. You used EJS templating and added the necessary folders and subdirectories for the setup Node Postgres CRUD application process. In Part 3, you’ll learn about exporting CRUD functionality for your application’s front end, and the codes used for API call making.

Just the Code

Here’s the entire sample code for Part 2 of the NodeJs and PostgreSQL CRUD Example Application.

app.js

const express = require('express')
const bodyParser = require('body-parser')
const path = require('path')

// Access library attributes from the imported modules
var employee = require('./routes/employee')
var routes = require('./routes')
var app = express()

// Use the set() method to store values
app.set('port', process.env.PORT || 5005)

// Set EJS engine as the default engine
app.set('view engine', 'ejs')

// Use the body-parser body parsing middleware
app.use(bodyParser.json())
app.use(bodyParser.urlencoded({ extended: true }))
app.use(express.static(path.join(__dirname, 'public')))

// Express routes for the HTTP 'GET' methods
app.get('/', routes.index)
app.get('/employee', employee.list)
app.get('/employee/add', employee.add)
app.get('/employee/delete/:id', employee.delete)
app.get('/employee/edit/:id', employee.edit)

// Express routes for the HTTP 'POST' methods
app.post('/employee/add', employee.save)
app.post('/employee/edit/:id', employee.update)

// Set a port for the app to listen on
app.listen(app.settings.port, function () {
console.log('Server is running on Port 5005. Press CTRL+C to stop server.')
})

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started