In this tutorial we'll see how to create an Express MVC CRUD application with MySQL as database and EJS as template engine. Bootstrap 5 is used for styling.
Note that this is not an Express REST application, it is an MVC application using HTML form which uses methods POST and GET.
Express MVC CRUD example
In this example User is used as Model (though direct use of MySQL doesn't have much use of Model class, if used with Sequelize then Model class has more usage).
models\user.js
module.exports = class User {
constructor(id, firstName, lastName, userType, startDate){
this.is = id;
this.firstName = firstName;
this.lastName = lastName;
this.userType = userType;
this.startDate = startDate;
}
}
Table in MySQL
CREATE TABLE `user` ( `id` int NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `user_type` varchar(15) NOT NULL, `start_date` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3
Aim is to create an application where root route ('/) should display all user data along with Edit and Delete button. On clicking Edit a modal with the data for the corresponding user should open and clicking Delete button should delete that particular User data.
A link to add user is also provided, clicking that link should display a form to enter user data.
Route definition
When creating an Express MVC application, convention is to keep route class light. Logic for the routes is kept in the Controller class. Route definition should refer the function in Controller.
routes\user.js
const express = require('express');
const router = express.Router();
const userController = require('../controllers/user');
//to show user data
router.get('/', userController.getUsers)
// show user form
router.get('/user', userController.showUserForm)
// Insert user and edit user
router.post('/adduser', userController.saveUser)
// delete user
router.post('/deleteuser', userController.deleteUser)
module.exports = router;
As you can see Controller is imported here and the function in the Controller are called for processing when corresponding paths are accessed.
Controller
controllers\user.js
const pool = require('../util/dbconfig');
const date = require('date-and-time')
exports.getUsers = async (req, res) => {
console.log('In getUsers user Controller');
const sql = "SELECT * FROM USER";
try{
const [result, fields] = await pool.query(sql);
res.render('user/displayUser', {
users: result,
pageTitle: 'Users Data',
date: date,
userType: ['Silver', 'Gold', 'Platinum']
})
console.log(result);
}catch(err){
console.log(err);
}
}
exports.showUserForm = (req, res) => {
console.log('In showUserForm user Controller');
res.render('user/userform', {
pageTitle: 'User Form',
userType: ['Silver', 'Gold', 'Platinum']
})
}
exports.saveUser = async (req, res) => {
console.log('In saveUser user Controller');
const id = req.body.id;
const firstName = req.body.firstName;
const lastName = req.body.lastName;
const userType = req.body.userType;
const startDate = req.body.startDate;
let sql = '';
let values = [];
// Works as update
if(id != undefined){
sql = `UPDATE USER SET first_name = ?, last_name = ?, user_type = ?,
start_date = ? WHERE ID = ?`;
values = [firstName, lastName, userType, startDate, id];
}else{ // Works as insert
sql = `INSERT INTO USER (first_name, last_name, user_type, start_date)
values (?, ?, ?, ?)`;
values = [firstName, lastName, userType, startDate];
}
try{
const conn = await pool.getConnection();
const [result, fields] = await conn.execute(sql, values);
console.log(result);
console.log(fields);
conn.release();
}catch(err){
console.log(err);
}
res.redirect('/');
}
exports.deleteUser = async (req, res) => {
console.log('In deleteUser user Controller');
const id = req.body.deleteid;
const sql = "DELETE FROM user WHERE id = ?";
const values = [id];
try{
const [result, fields] = await pool.query(sql, values);
console.log(result);
res.redirect('/');
}catch(err){
console.log(err);
}
}
Important points about the logic here-
- There are two imports, one is for DB configuration and another is date-time library which is used for formatting dates.
- First function getUsers() is used to get all users from DB. Fetched users are passed to the EJS template. User type is a dropdown, options for that are also passed.
- showUserForm() function is used to display a form that is used to enter and save new user.
- saveUser() function is used for both inserting and updating data. If userID is present that means an update otherwise insert. Form data is received by accessing request.body method. body-parser module is used to parse form data sent by a user by submitting a web form.
- deleteUser() function is used to delete user from DB. User id is passed from the view in a hidden field which is accessed in the deleteUser() function to create Delete query.
DB Configuration
Using mysql2 package you can also create a connection pool. For that createPool() method is used which needs few configuration values. DB pool is created in a separate file.
util\dbconfig.js
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'admin',
database: 'knpcode', //Schema Name
waitForConnections: true, // this is default anyway
connectionLimit: 10, // this is default anyway
});
module.exports = pool;
Views
Views are created using EJS template engine.
views\user\displayUser.ejs
<!DOCTYPE html>
<html>
<head>
<title><%= pageTitle %></title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<script>
function editData(user){
// Converting to object
const temp = JSON.parse(user);
// setting values in modal box form - EDIT
document.getElementById("id").value = temp.id;
document.getElementById("firstName").value = temp.first_name;
document.getElementById("lastName").value = temp.last_name;
document.getElementById("startDate").value = temp.start_date;
document.getElementById("userType").value = temp.user_type;
}
function deleteData(id){
document.getElementById("deleteid").value = id;
}
</script>
</head>
<body>
<div class="container">
<a href="/user">Add User</a>
<h2 class="text-info d-flex justify-content-center mb-3">User Data</h2>
<table class="table table-striped table-bordered table-sm">
<thead>
<tr>
<th>id</th>
<th>First Name</th>
<th>Last Name</th>
<th>User Type</th>
<th>Start Date</th>
</tr>
</thead>
<% for(let user of users) { %>
<tr>
<td><%= user.id%></td>
<td><%= user.first_name%></td>
<td><%= user.last_name%></td>
<td><%= user.user_type%></td>
<td><%= date.format(user.start_date,'YYYY-MM-DD');%></td>
<!-- Changing user.start_date to a formatted date -->
<% user.start_date = date.format(user.start_date,'YYYY-MM-DD');%>
<td class="text-center"><button type="button" class="btn btn-primary me-3" data-bs-toggle="modal" data-bs-target="#editModal" onclick="editData('<%= JSON.stringify(user)%>')">
Edit
</button>
<button type="button" class="btn btn-danger" data-bs-toggle="modal" data-bs-target="#deleteModal" onclick="deleteData('<%= user.id%>')">
Delete
</button>
</td>
</tr>
<%}%>
</table>
<!-- Edit and Delete Modal-->
<div class="modal fade" id="editModal" tabindex="-1">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="editModalLabel">Modify User</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<form action="/adduser" method="POST">
<div class="mb-3 row">
<label for="id" class="form-label" ></label>User ID</label>
<input type="text" class="form-control" name="id" id="id" readonly/>
</div>
<div class="mb-3 row">
<label for="firstName" class="form-label" ></label>Enter First Name</label>
<input type="text" class="form-control" name="firstName" id="firstName" />
</div>
<div class="mb-3 row">
<label for="lastName" class="form-label">Enter Last Name</label>
<input type="text" class="form-control" name="lastName" id="lastName" />
</div>
<div class="mb-3 row">
<label for="userType" class="form-label">User Type</label>
<select class="form-select" name="userType" id="userType">
<% for(let type of userType) {%>
<option value="<%=type%>"><%=type%></option>
<% } %>
</select>
</div>
<div class="mb-3 row">
<label for="startDate" class="form-label">Enter Start Date</label>
<input type="date" class="form-control" name="startDate" id="startDate" />
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
<button type="submit" class="btn btn-primary">Save changes</button>
</div>
</form>
</div>
</div>
</div>
</div>
<div class="modal fade" id="deleteModal" tabindex="-1">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="deleteModalLabel">Delete User</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<form action="/deleteuser" method="POST">
<p>Do you really want to delete the user</p>
<input type="hidden" class="form-control" name="deleteid" id="deleteid"/>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
<button type="submit" class="btn btn-primary">OK</button>
</div>
</form>
</div>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous">
</script>
</body>
</html>
Important points about this view-
- Bootstrap is used for styling.
- This same view code is used for showing user data in a table, modifying existing user and deleting user.
- Clicking Edit or Delete button opens a Modal box. In case of edit existing user data is pre-populated, for that JavaScript function editData() is used. In case of delete id is populated in a hidden field.
views\user\userform.ejs
<!DOCTYPE html>
<html>
<head>
<title><%= pageTitle %></title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
</head>
<body>
<div class="container">
<form action="/adduser" method="POST">
<div class="mb-3 row">
<label for="firstName" class="form-label" ></label>Enter First Name</label>
<input type="text" class="form-control" name="firstName" id="firstName" />
</div>
<div class="mb-3 row">
<label for="lastName" class="form-label">Enter Last Name</label>
<input type="text" class="form-control" name="lastName" id="lastName" />
</div>
<div class="mb-3 row">
<label for="userType" class="form-label">User Type</label>
<select class="form-select" name="userType" id="userType">
<% for(let type of userType) {%>
<option value="<%=type%>"><%=type%></option>
<% } %>
</select>
</div>
<div class="mb-3 row">
<label for="startDate" class="form-label">Enter Start Date</label>
<input type="date" class="form-control" name="startDate" id="startDate" />
</div>
<button type="submit" class="btn btn-primary">Save</button>
</form>
</div>
</body>
</html>
App.js
This JS file acts as a starting point.
const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const port = 3000;
app.set('view engine', 'ejs');
app.set('views', 'views');
const userRoutes = require('./routes/user');
app.use(bodyParser.urlencoded({ extended: false }));
app.use(userRoutes);
app.listen(port, () => {
console.log(`Example app listening on port ${port}`)
})
With all the changes done you can run the application by using the command.
node app.js
On accessing http://localhost:3000/ all the users are displayed.
Edit user data
Delete User
Add user
That's all for the topic ExpressJS MVC CRUD Application + MySQL + EJS. If something is missing or you have something to share about the topic please write a comment.
You may also like
- ExpressJS MVC CRUD Application + MongoDB + EJS
- Express Route Parameters - Creating Dynamic URL
- ExpressJS Query Parameters
- ExpressJS Template Engines Introduction
- ExpressJS: EJS Partials
- LinkedList Internal Implementation in Java
- Marker Interface in Java
- Python Multi-threading Tutorial
- Dynamic Route in React
- Spring Boot + Spring Data REST Example
No comments:
Post a Comment