NodeJs and PostgreSQL CRUD Example Application (Part 2)
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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 withtouch
. 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.
>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:
1 2 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | #!/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 |
>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.
1 | bash create_files.sh |
Show all the current directory files in alphabetical order with the command
ls -R .
The result should look like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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 |
>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 methodsPOST
andGET
.
app.js
- The function
require()
obtains the essential libraries and modules to make them script accessible:
1 2 3 | 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:
1 2 3 | 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 theapp
instance. This way, you’ll obtain the routes and connect to the database:
1 2 3 4 5 | // 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:
1 2 3 | 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:
1 2 3 4 5 | 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:
1 2 | 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
.
1 2 3 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
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 | <!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
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 | 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