This is the whole process of how I made a restaurant website with Express & Sequelize. In this project, I focus on a content management system inclueds menu, faq, prize management. An admin has the permission to modify, create and delete items.
0. Table of Content
1. Structure, product features and router
2. Database structure
3. Set up environment
4. Let's start: vue -> router -> controller
5. Debug time
6. Review
7. Notes:
set up environment variables in node.js
Deploy express app in Heroku with cleardb
1. The whole picture of a website
a. Features
a.1 Vue
Draw
Menu
Faq
Sign up
Login/Logout
a.2 Content management system
Draw management: Add new prize and set up the prize ratio
a.2.1 As an administrator, I would like to have a prize page to maange prizes.
a.2.2 As an administrator, I would like to add new prize includes item, picture, url, detail and ratio.
a.2.3 As an administrator, I would like to edit prize includes item, picture, url, detail and ratio.
a.2.4 As an administrator, I would like to delete prize includes item, picture, url, detail and ratio.
a.2.5 As an administrator, I would like to let customers draw the prizes I set up in content management system.
FAQ management: Add new question and answer in content management system, the FAQ page will get data loaded from database.
a.2.6 As an administrator, I would like to create new FAQ includes title, content and sequence.
a.2.7 As an administrator, I would like to delete FAQ.
a.2.8 As an administrator, I would like to edit FAQ includes title, content and sequence.
a.2.9 As an administrator, I would like to see the data of FAQ page loaded from database.
2. Design routers
a. Login, Logout
a.1 Login
get: /login
post: /login → /
a.2 Logout: get: /logout
a.3 Sign up: get: /signup
b. CRUD friends
b.1 Draw：
b.1.1 Create: post: /create_draw/id → /
b.1.2 Delete: get: /delete_draw/id → /
b.1.3 Edit:
get /edit_draw/id → form
post /edit_draw/id → /
b.2 Menu
b.2.1 Create: post: /create_menu/id → /
b.2.2 Delete: get: /delete_menu/id → /
b.2.3 Edit:
get /edit_menu/id → form
post /edit_menu/id → /
b.3 FAQ
b.3.1 Create: post: /create_question/id → /
b.4.2 Delete: get: /delete_question/id → /
b.4.3 Edit:
get /edit_question/id → form
post /edit_question/id → /
2. Database structure
a. Draw：item, content, url and ratio
$ npx sequelize-cli model:generate --name Prize --attributes item:text,content:text,url:text,ratio:integer
b. User: id, username, password, createdAt and updatedAt
$ npx sequelize-cli model:generate --name User --attributes username:string,password:string
c. Menu: item, price and photo
$ npx sequelize-cli model:generate --name Menu --attributes item:string,price:integer,photo:string
d. FAQ: sequence, title and content
$ npx sequelize-cli model:generate --name Question --attributes sequence:integer,title:string,content:text
3. Set up environment
a. $ npm init -y
b. $ npm install eslint prettier prettier-eslint then eslint --init
c. $ npm install express then $ npm install ejs body-parser express-session connect-flash bcrypt
d. $ npm install --save sequelize mysql2
e. $ npm install --save-dev sequelize-cli
f. $ npx sequelize-cli init
g. Revise config file for the development section
h. Build four models
I use 3306 port in Xampp, don't forget to set up a database.
i. Running Migrations $ npx sequelize-cli db:migrate
It supposed to show the four tables in database.
4. Let's start
a. Build MVC files, set up index.js and link to css file
b. Prize view & controller
c. Q&A view & controller
d. Menu
e. Signup and Login/Logout
f. CMS：FAQ、draw & menu
5. Error logs
Error log 1:
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' `photo` TEXT, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, ...' at line 1
When buliding database with Sequelize, it shows the error above
Try 1:
At first I ran:
$ npx sequelize-cli model:generate --name Menu --attributes item:text,price:number,photo:text
I changed it to:
$ npx sequelize-cli model:generate --name Menu --attributes item:string,price:number,photo:string
It doesn't work this way.
Try 2:
There's no number in Sequelize, use string instead
Datatypes
Notes:
1. The prize items should be gotten from database, it's not a hard code.
<div class="draw__prize-content">
<% prizes.forEach(function(prize) { %>
<div>❤ <%= `${prize.item}: ${prize.content}`%></div>
<% }) %>
</div>
2. Put the background image with bootstrap
<div class="prize__result bg-image" style="background-image:url(${result.url})">
<div class="text-center prize__result-content">
<div class="card-body">
<h1 class="card-text">${result.item}</h1>
<h4 class="card-text">${result.content}</h4>
<button class="draw__btn"><a href="/draw">Draw again!</a></button>
</div>
</div>
</div>
3. Center three pictures in menu page with class= "w-75" in bootstrap
4. Draw an arrow down
// html
<div class="arrow">
<h1>Find Us</h1>
</div>
// css
.arrow {
font-size: 18px;
color: #414141;
margin: 1rem auto 6rem;
display: flex;
justify-content: center;
align-items: inherit;
position: relative;
}
.arrow::after {
content: "";
width: 10px;
height: 10px;
border-bottom: 1px solid #000;
border-right: 1px solid #000;
transform: rotate(45deg);
position: absolute;
top: 180%;
}
.arrow::before {
content: "";
width: 15px;
height: 15px;
border-bottom: 1px solid #000;
border-right: 1px solid #000;
transform: rotate(45deg);
position: absolute;
top: 150%;
}
5. Bcrypt for password
const hashPassword = bcrypt.hashSync(password, saltRounds);
6. The distance of pictures in menu page & center sold out words
<div class="wrapper">
<section class="section">
<title>Hot sale NOW!</title>
<div class="menu__dishes">
<div class="menu__dish"><img src="images/menu/menu-1.png"/></div>
<div class="menu__dish"><img src="images/menu/menu-2.png"/></div>
<div class="menu__dish"><img src="images/menu/menu-3.png"/></div>
</div>
</section>
</div>
.menu__dishes {
display: flex;
justify-content: center;
}
.menu__dish {
width: 100%;
position: relative;
}
.menu__dish > img {
max-width: 420px;
width: 100%;
filter: brightness(105%) opacity(0.5);
}
.menu__dish::before {
content: "Sold Out";
position: absolute;
display: block;
z-index: 2;
left: 50%;
top: 50%;
transform: translate(-50%, -50%);
width: 80px;
height: 80px;
line-height: 80px;
border-radius: 80%;
background-color: rgb(49, 45, 45);
color: #fefefe;
}
7. Duplicate registration problem
The best way is to set username as unique in database, when I use try...catch...I will have the error message.
handleRegister: async (req, res, next) => {
const { username, password } = req.body
if(!username || !password) {
req.flash('errMessage', 'Please enter username or password')
next()
return
}
const hashPassword = await bcrypt.hash(password, saltRounds)
try {
await UserDb.create({
username,
password: hashPassword
})
} catch (err) {
console.log(err)
req.flash('errMessage', 'user exists')
next()
return
}
req.session.isLogin = true
res.redirect('/admin-item')
return
},
8. Check permission
- If the user isn't admin, redirect user to home page.
// index.js
app.use((req, res, next) => {
res.locals.username = req.session.username;
res.locals.role = req.session.role;
res.locals.errorMessage = req.flash("errorMessage");
next();
});
function isAdmin(req, res, next) {
if (req.session.role !== "admin") {
return res.redirect("/");
}
return next();
}
app.get("/cms", isAdmin, userController.cms);
app.get("/cms/prize", isAdmin, prizeController.managePrize);
app.get("/cms/faq", isAdmin, faqController.manageFaq);
app.get("/cms/menu", isAdmin, menuController.manageMenu);
app.get("/cms/order", isAdmin, orderController.manageOrder);
app.get("/cms/add-prize", isAdmin, prizeController.add);
app.post("/cms/add-prize", isAdmin, prizeController.handleNewPrize);
app.get("/cms/delete-prize/:id", isAdmin, prizeController.delete);
app.get("/cms/edit-prize/:id", isAdmin, prizeController.edit);
app.post("/cms/edit-prize/:id", isAdmin, prizeController.handleEdit);
app.get("/cms/add-faq", isAdmin, faqController.add);
app.post("/cms/add-faq", isAdmin, faqController.handleNewFaq);
app.get("/cms/delete-faq/:id", isAdmin, faqController.delete);
app.get("/cms/edit-faq/:id", isAdmin, faqController.edit);
app.post("/cms/edit-faq/:id", isAdmin, faqController.handleEdit);
app.get("/cms/add-menu", isAdmin, menuController.add);
app.post("/cms/add-menu", isAdmin, menuController.handleNewMenu);
app.get("/cms/delete-menu/:id", isAdmin, menuController.delete);
app.get("/cms/edit-menu/:id", isAdmin, menuController.edit);
app.post("/cms/edit-menu/:id", isAdmin, menuController.handleEdit);
9. Prevent XSS injection
What is the HtmlSpecialChars equivalent in JavaScript?
// public/script/api.js
function escapeHtml(text) {
return text
.replace(/&/g, "&")
.replace(/</g, "<")
.replace(/>/g, ">")
.replace(/"/g, """)
.replace(/'/g, "'");
}
async function drawResult() {
const result = await getDrawAPI();
try {
mainHTML.innerHTML = `
<div class="prize__result bg-image" style="background-image:url(${result.url})">
<div class="text-center prize__result-content">
<div class="card-body">
<h1 class="card-text">${escapeHtml(result.item)}</h1>
<h4 class="card-text">${escapeHtml(result.content)}</h4>
<button class="draw__btn"><a href="/draw">Draw again!</a></button>
</div>
</div>
</div>
`;
} catch (err) {
return console.log(`Error: ${result.error} & ${result.message}`);
}
}
6. Review
This is the first-time practice of how to use Express and Sequelize to build a CMS. It's brand new experience for me with back end tool and I found that totally different with PHP I used before.
I like back end modern tool but realize the reason why it was invented. With pure PHP, the code might be a mess. The MVC framework solves the problem and make the structure tidy.
