A restaurant website with Express & Sequelize

Posted by Christy on 2022-03-26

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

Prevent Heroku from sleeping

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


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>
  <% }) %>

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>

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>
// 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>

.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')
    const hashPassword = await bcrypt.hash(password, saltRounds)
    try {
      await UserDb.create({
        password: hashPassword
    } catch (err) {
      req.flash('errMessage', 'user exists')
    req.session.isLogin = true

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");

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, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#039;");

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

Related Posts

[ 第 12 週] 認識 JQuery

[ 第 12 週] 認識 JQuery

Longest Common Prefix

Longest Common Prefix

Git 狀況劇_我把錯的資料夾初始化了

Git 狀況劇_我把錯的資料夾初始化了