In this tutorial, we will be looking at how easily we can perform CRUD operations in nodejs using Sequelize in databases like Postgres, MySQL, MariaDB, SQLite, DB2, and Microsoft SQL Server. In this case, we will be using MySQL as a database.
Before we start coding let’s firstly understand what we are going to build.
We will be making a library-management system, where we can
- Add a book.
- Show all the available books.
- Assign a book to a reader.
- Return the book.
So let’s get started, shall we?
Initialize our nodejs-mysql project with all required deps
For that, open the terminal in a new directory and run the command:
npm init -ynpm install express cors http-errors module-alias mysql2 sequelizeAs you can see, we have installed a “module-alias” package which makes our life easy by creating aliases of directories and registering custom module paths by tweaking inside our package.json file. This is so that paths can easily be accessible.
"_moduleAliases": {
"@root": ".",
"@models": "./models",
"@routes": "./routes",
"@utils": "./utils",
"@config": "./config",
"@controllers": "./controllers"
}Once we are ready with our package.json file we need to install sequelize-CLI and initialize a project using Sequalize. To do so run these commands in the terminal.
npm install --save-dev sequelize-clinpx sequelize-cli initRunning the above command will automatically generate some directories namely config, migrations, models, seeders. Inside the models directory, we have an index.js file that collects all the models from the models directory and associates them if needed.
We have tweaked the index.js file for handling errors and auto importing the models.
const fs = require("fs");
const path = require("path");
const Sequelize = require("sequelize");
const creatError = require("http-errors");
const sequelizeErrorHandler = require("@utils/sequelizeErrorHandler");
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || "development";
const config = require("@config/config")[env];
const db = {};
let sequelize = {};
if (config.use_env_variable) {
sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
sequelize = new Sequelize(
config.database,
config.username,
config.password,
config
);
}
fs.readdirSync(__dirname)
.filter((file) => {
return (
file.indexOf(".") !== 0 && file !== basename && file.slice(-3) === ".js"
);
})
.forEach((file) => {
const model = require(path.join(__dirname, file))(
sequelize,
Sequelize,
creatError,
sequelizeErrorHandler
);
db[model.name] = model;
});
Object.values(db).forEach((model) => {
if (typeof model.associate === "function") model.associate(db);
});
db.sequelize = sequelize;
db.Sequelize = Sequelize;
module.exports = db;
Understand directory structure
Inside our root directory, we’ll have 7 directories namely.
- config: Contains config file, takes care of connection with database
- controllers: Contains logic for our controllers.
- migrations: Contains all migration files.
- models: Contains all models for our project.
- routes: Contains all the route files for our project.
- seeders: Contains all seed files.
- utils: Contains all the utility files.
Create our expressjs server
Now that we are ready with our directories, we are good to create our app.js file that will hold all the code logic for our server.
app.js
require("module-alias/register");
const port = process.env.PORT || 3000;
const express = require("express");
const { json } = require("express");
const addBookRoute = require("@routes/addBookRoute");
const cors = require("cors");
const app = express();
app.use(json());
app.use(cors());
app.use("/", addBookRoute);
app.listen(port, () =>
console.log(`Server is up and running on PORT: ${port}`)
);
Models for our nodejs-mysql app
A model is an abstraction that represents a table in our database. It tells Sequelize several things about the entity it represents, such as the name of the table in the database and which columns it has (and their data types).
For our project, we have created three models:
- Book
- Assign
- User
Let’s first create the Book model by simply running a command:
npx sequelize-cli model:generate --name Book --attributes title:string,author:string,description:stringSimilarly, to create the Assign model run a command
npx sequelize-cli model:generate --name Assign --attributes bookId:integer,userId:integer,returnStatus:boolean
Then, to create the User model we have to run a command:
npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string It not only creates a model in our models directory but also creates a migration file corresponding to that model in the migration directory.
Once we have all our models ready with us, we have to associate them accordingly. In this case, the Assign model has a 1:1 relation between both Book and the User model. Therefore, we will be using .belongsTo() method in our Assign model.
Assign.js
"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
class Assign extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
// define association here
Assign.belongsTo(models.Book);
Assign.belongsTo(models.User);
}
}
Assign.init(
{
bookId: {
type: DataTypes.INTEGER,
allowNull: false,
references: {
model: "books",
key: "id",
},
},
userId: {
type: DataTypes.INTEGER,
allowNull: false,
references: {
model: "users",
key: "id",
},
},
returnStatus: {
type: DataTypes.BOOLEAN,
allowNull: false,
defalutValue: false,
},
},
{
sequelize,
modelName: "Assign",
}
);
return Assign;
};
Book.js
"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
class Book extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {}
}
Book.init(
{
title: DataTypes.STRING,
author: DataTypes.STRING,
description: DataTypes.STRING,
},
{
sequelize,
modelName: "Book",
}
);
return Book;
};
User.js
"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
class User extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {}
}
User.init(
{
firstName: DataTypes.STRING,
lastName: DataTypes.STRING,
},
{
sequelize,
modelName: "User",
}
);
return User;
};Once we have made the required changes in our models we can migrate them using the command:
npx sequelize-cli db:migrateControllers
Now that we are ready with our models, we have created three controllers with their functionalities.
- BookController.js
- UserController.js
- ReaderController.js
Inside our BookController.js we will have the below structure.
const { Book } = require("@models");
const save = async (req, res) => {
// Saves book into the database.
};
const findAllBooks = async (req, res) => {
// Fetch all books from books table
};
module.exports = { save, findAllBooks };
save()
This function handles the creation of a new book
const save = async (req, res) => {
// Saves book into the database.
const { title, author, description } = req.body.data;
try {
await Book.create({
title,
author,
description,
});
return res.json({
status: 200,
message: `New book ${title} by ${author} successfully added! `,
});
} catch (error) {
return res.send({
status: 500,
message: error.message,
});
}
};findAllBooks()
This function fetches all the books present in the table.
const findAllBooks = async (req, res) => {
// Fetch all books from books table
try {
const data = await Book.findAll();
return res.send({ status: 200, data });
} catch (error) {
return res.send({ status: 404, data: error.message });
}
};Similarly below is the structure of UserController.js.
const { User } = require("@models");
const createUser = async (req, res) => {
// Creates a new user into Users table
};
const findAllUsers = async (req, res) => {
// fetch all the users from the database
};
module.exports = { createUser, findAllUsers };
createUser
This function handles the logic for the creation of a new user
const createUser = async (req, res) => {
// Creates a new user into Users table
const { firstName, lastName } = req.body.data;
try {
await User.create({
firstName,
lastName,
});
return res.send({
status: 200,
message: `New user: ${firstName} ${lastName} has been created`,
});
} catch (error) {
return;
}
};findAllUsers
This function contains the logic of fetching all the users.
const findAllBooks = async (req, res) => {
// Fetch all books from books table
try {
const data = await Book.findAll();
return res.send({ status: 200, data });
} catch (error) {
return res.send({ status: 404, data: error.message });
}
};Now let’s see the structure of ReaderController.js
const { Assign, Book, User } = require("@models");
const assignBook = async (req, res) => {
// Saves the userId and bookId
};
const returnBook = async (req, res) => {
// changes returnStatus : false
};
const fetchAssignedBooks = async (req, res) => {
// fetchs the book and users information.
};
module.exports = { assignBook, fetchAssignedBooks, returnBook };
assignBook
This function is responsible for storing the bookId and userId.
const { userId, bookId } = req.body.data;
try {
await Assign.create({ userId, bookId, returnStatus: false });
return res.send({ statusCode: 200, message: `Book Assigned!` });
} catch (error) {
return res.send({ statusCode: 404, message: error.message });
}returnBook
Holds the code logic for returning the book
const returnBook = async (req, res) => {
try {
await Assign.update({ returnStatus: true });
return res.send({ statusCode: 200, message: `Returned!` });
} catch (error) {}
};fetchAssignedBooks
const fetchAssignedBooks = async (req, res) => {
try {
const assignedInfo = await Assign.findAll({
where: {
returnStatus: false,
},
include: [
{
model: Book,
attributes: ["title"],
},
{
model: User,
attributes: ["firstName", "lastName"],
},
],
});
return res.send({ statusCode: 200, data: assignedInfo });
} catch (error) {
return res.send({ statusCode: error.statusCode, message: error.message });
}
};Routes
Next is to set up our corresponding routes to CREATE, READ, and UPDATE our database.
/add-book – Here we will be implementing the code logic for adding books to our database.
route.post("/add-book", save);
);/inventory – Here we will be fetching all the book details from our database.
route.get("/inventory", findAllBooks);
/assign-book – Here we have code logic to assign a book to a reader.
route.post("/assign-book", assignBook);
/return-book – Here we will be implementing the code logic for returning the book from the reader.
route.post("/return-book", returnBook);
/add-user – Here we will be implementing the code logic for adding a new user.
route.post("/add-user", createUser);
/get-users – Here we will be implementing the code logic for fetching all users.
route.get("/get-users", findAllUsers);
/assigned-books -Here we will be implementing the code logic for fetching all the assigned books details.
route.get("/assigned-books", fetchAssignedBooks);After the successful implementation of our routes, it will look something like this.
const express = require("express");
const { save, findAllBooks } = require("@controllers/BookController");
const { createUser, findAllUsers } = require("@controllers/UserController");
const {
assignBook,
returnBook,
fetchAssignedBooks,
} = require("@controllers/ReaderController");
const route = express.Router();
// Add Book & Get all books Routes
route.post("/add-book", save);
route.get("/inventory", findAllBooks);
// Assign Book and Return Book Routes
route.post("/assign-book", assignBook);
route.post("/return-book", returnBook);
route.get("/assigned-books", fetchAssignedBooks);
// Add User and Get User Routes
route.post("/add-user", createUser);
route.get("/get-users", findAllUsers);
module.exports = route;
Once we have successfully implemented the Backend Logic of our nodejs-mysql app, we need to create a UI to interact with. Therefore, we have created a front end.
Front end part
To make our UI interactive we have used TailwindCss and created 3 HTML files namely.
- Book.html
- AssignBook.html
- User.html
And, to interact with them we have 3 JS files that will be responsible for handling all the control logic for our front end.
- assign.js
- book.js
- user.js
Book.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" href="../css/table.css" />
<script src="https://cdn.tailwindcss.com"></script>
<title>Inventory</title>
</head>
<body>
<div class="h-screen w-screen flex bg-gray-200">
<link rel="stylesheet" href="https://unpkg.com/[email protected]/css/boxicons.min.css" />
<div class="min-h-screen flex flex-row bg-gray-100">
<div class="flex flex-col w-56 bg-white rounded-r-3xl overflow-hidden">
<div class="flex items-center justify-center h-20 shadow-md">
<h1 class="text-3xl uppercase text-indigo-500">LIBRARY</h1>
</div> <ul class="flex flex-col py-4"> <li>
<a href="./Book.html" class=" flex flex-row items-center h-12 transform hover:translate-x-2 transition-transform ease-in duration-200 text-gray-500 hover:text-gray-800 " >
<span class=" inline-flex items-center justify-center h-12 w-12 text-lg text-gray-400 " ><i class="bx bx-home"></i ></span>
<span class="text-sm font-medium">Books</span>
</a>
</li>
<li>
<a href="./AssignBook.html" class=" flex flex-row items-center h-12 transform hover:translate-x-2 transition-transform ease-in duration-200 text-gray-500 hover:text-gray-800 " >
<span class=" inline-flex items-center justify-center h-12 w-12 text-lg text-gray-400 " >
<svg xmlns="http://www.w3.org/2000/svg" class="h-6 w-6" fill="none" viewBox="0 0 24 24" stroke="currentColor" >
<path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M9 13h6m-3-3v6m-9 1V7a2 2 0 012-2h6l2 2h6a2 2 0 012 2v8a2 2 0 01-2 2H5a2 2 0 01-2-2z" />
</svg >
</span>
<span class="text-sm font-medium">Assign Books</span>
</a>
</li>
<li>
<a href="./User.html" class=" flex flex-row items-center h-12 transform hover:translate-x-2 transition-transform ease-in duration-200 text-gray-500 hover:text-gray-800 " >
<span class=" inline-flex items-center justify-center h-12 w-12 text-lg text-gray-400 " ><i class="bx bx-user"></i ></span> <span class="text-sm font-medium">Users</span>
</a>
</li>
</ul>
</div>
</div>
<div class="bg-white p-8 rounded-md w-full object-center">
<div class="flex items-center justify-between pb-6">
<div>
<h2 class="text-gray-600 font-semibold">ALL BOOKS</h2>
</div>
<div class="flex items-center justify-between">
<div class="lg:ml-40 ml-10 space-x-8">
<button id="add-book" class=" bg-indigo-600 px-4 py-2 rounded-md text-white font-semibold tracking-wide cursor-pointer " > ADD NEW BOOK </button>
</div>
</div>
</div>
<div>
<div class="-mx-4 sm:-mx-8 px-4 sm:px-8 py-4 overflow-x-auto">
<div class="inline-block min-w-full shadow rounded-lg overflow-hidden" >
<table id="book-table" class="min-w-full leading-normal"> <thead> <tr> <th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > BOOK ID </th>
<th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > BOOK NAME </th> <th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > AUTHOR NAME </th>
<th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > DESCRIPTION </th> </tr> </thead>
</table>
</div>
</div>
</div>
<div id="modal" class=" flex justify-center h-150 w-100 items-center antialiased h-20 hidden " > <div class=" flex flex-col w-11/12 sm:w-4/5 lg:w-1/2 max-w-2xl mx-auto rounded-lg shadow-xl " >
<div class=" flex flex-row justify-between p-6 bg-white border-b border-gray-200 rounded-tl-lg rounded-tr-lg " >
<p class="font-semibold text-gray-800">Add Book</p> <svg id="cancel-button" class="w-6 h-6" fill="none" stroke="currentColor" viewBox="0 0 24 24" xmlns="http://www.w3.org/2000/svg" >
<path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M6 18L18 6M6 6l12 12" ></path>
</svg>
</div>
<div class="flex flex-col px-6 py-5 bg-gray-50"> <p class="mb-2 font-semibold text-gray-700">Enter details:</p>
<input type="text" class=" p-5 mb-5 bg-white border border-gray-200 rounded shadow-sm h-10 " placeholder="Enter Book Name" id="title" />
<input type="text" class=" p-5 mb-5 bg-white border border-gray-200 rounded shadow-sm h-10 " placeholder="Enter Author Name" id="author" />
<input type="text" class=" p-5 mb-5 bg-white border border-gray-200 rounded shadow-sm h-10 " placeholder="Enter Description" id="description" />
<hr />
</div>
<div class=" flex flex-row items-center justify-between p-5 bg-white border-t border-gray-200 rounded-bl-lg rounded-br-lg " > <button id="save-btn" class="px-4 py-2 text-white font-semibold bg-blue-500 rounded" > Save </button> </div>
</div>
</div>
</div>
</div>
<script src="../js/books.js"></script>
</body>
</html> AssignBook.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" href="../css/table.css" />
<script src="https://cdn.tailwindcss.com"></script>
<title>Inventory</title>
</head>
<body> <!-- navigation section -->
<div class="h-screen w-screen flex bg-gray-200"> <!-- container -->
<link rel="stylesheet" href="https://unpkg.com/[email protected]/css/boxicons.min.css" />
<div class="min-h-screen flex flex-row bg-gray-100">
<div class="flex flex-col w-56 bg-white rounded-r-3xl overflow-hidden">
<div class="flex items-center justify-center h-20 shadow-md">
<h1 class="text-3xl uppercase text-indigo-500">LIBRARY</h1>
</div> <ul class="flex flex-col py-4"> <li> <a href="./Book.html" class=" flex flex-row items-center h-12 transform hover:translate-x-2 transition-transform ease-in duration-200 text-gray-500 hover:text-gray-800 " >
<span class=" inline-flex items-center justify-center h-12 w-12 text-lg text-gray-400 " >
<i class="bx bx-home"></i >
</span>
<span class="text-sm font-medium">Books</span>
</a>
</li>
<li>
<a href="./AssignBook.html" class=" flex flex-row items-center h-12 transform hover:translate-x-2 transition-transform ease-in duration-200 text-gray-500 hover:text-gray-800 " >
<span class=" inline-flex items-center justify-center h-12 w-12 text-lg text-gray-400 " >
<svg xmlns="http://www.w3.org/2000/svg" class="h-6 w-6" fill="none" viewBox="0 0 24 24" stroke="currentColor" >
<path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M9 13h6m-3-3v6m-9 1V7a2 2 0 012-2h6l2 2h6a2 2 0 012 2v8a2 2 0 01-2 2H5a2 2 0 01-2-2z" />
</svg >
</span>
<span class="text-sm font-medium">Assign Books</span>
</a>
</li>
<li>
<a href="./User.html" class=" flex flex-row items-center h-12 transform hover:translate-x-2 transition-transform ease-in duration-200 text-gray-500 hover:text-gray-800 " >
<span class=" inline-flex items-center justify-center h-12 w-12 text-lg text-gray-400 " >
<i class="bx bx-user">
</i >
</span>
<span class="text-sm font-medium">Users</span>
</a>
</li>
</ul>
</div>
</div>
<div class="bg-white p-8 rounded-md w-full object-center">
<div class="flex items-center justify-between pb-6"> <div>
<h2 class="text-gray-600 font-semibold">ALL ASSIGNED</h2>
</div>
<div class="flex items-center justify-between">
<div class="lg:ml-40 ml-10 space-x-8">
<button id="assign-book" class=" bg-indigo-600 px-4 py-2 rounded-md text-white font-semibold tracking-wide cursor-pointer " > Assign A BOOK </button>
</div>
</div>
</div>
<div>
<div id="table" class="-mx-4 sm:-mx-8 px-4 sm:px-8 py-4 overflow-x-auto" >
</div>
<div id="modal" class=" flex justify-center h-150 w-100 items-center antialiased h-20 hidden " >
<div class=" flex flex-col w-11/12 sm:w-4/5 lg:w-1/2 max-w-2xl mx-auto rounded-lg shadow-xl " >
<div class=" flex flex-row justify-between p-6 bg-white border-b border-gray-200 rounded-tl-lg rounded-tr-lg " >
<p class="font-semibold text-gray-800">Assign a book to user</p> <svg id="cancel-button" class="w-6 h-6" fill="none" stroke="currentColor" viewBox="0 0 24 24" xmlns="http://www.w3.org/2000/svg" >
<path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M6 18L18 6M6 6l12 12" >
</path> </svg>
</div>
<div class="flex flex-col px-6 py-5 bg-gray-50">
<p class="mb-2 font-semibold text-gray-700">Enter details:</p>
<input type="text" class=" p-5 mb-5 bg-white border border-gray-200 rounded shadow-sm h-10 " placeholder="Enter user Id" id="user-id" />
<input type="text" class=" p-5 mb-5 bg-white border border-gray-200 rounded shadow-sm h-10 " placeholder="Enter Book Id" id="book-id" />
</div>
<hr />
</div>
<div class=" flex flex-row items-center justify-between p-5 bg-white border-t border-gray-200 rounded-bl-lg rounded-br-lg " >
<button id="save-btn" class="px-4 py-2 text-white font-semibold bg-blue-500 rounded" > Assign </button>
</div>
</div>
</div>
<table id="assign-table" class="min-w-full leading-normal">
<thead>
<tr>
<th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > USER ID </th>
<th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > USER NAME </th>
<th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > BOOK NAME </th>
<th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > RETURN </th>
</tr>
</thead>
</table> <!-- Table ends here! -->
<!-- Navigation ends here-->
<script src="../js/assign.js"></script>
</body>
</html> User.html
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8" /> <meta http-equiv="X-UA-Compatible" content="IE=edge" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" href="../css/table.css" />
<script src="https://cdn.tailwindcss.com"></script> <title>Inventory</title> </head> <body> <!-- navigation section -->
<div class="h-screen w-screen flex bg-gray-200"> <!-- container --> <link rel="stylesheet" href="https://unpkg.com/[email protected]/css/boxicons.min.css" />
<div class="min-h-screen flex flex-row bg-gray-100"> <div class="flex flex-col w-56 bg-white rounded-r-3xl overflow-hidden"> <div class="flex items-center justify-center h-20 shadow-md"> <h1 class="text-3xl uppercase text-indigo-500">LIBRARY</h1> </div>
<ul class="flex flex-col py-4"> <li> <a href="./Book.html" class=" flex flex-row items-center h-12 transform hover:translate-x-2 transition-transform ease-in duration-200 text-gray-500 hover:text-gray-800 " > <span class=" inline-flex items-center justify-center h-12 w-12 text-lg text-gray-400 " ><i class="bx bx-home"></i >
</span>
<span class="text-sm font-medium">Books</span> </a> </li>
<li> <a href="./AssignBook.html" class=" flex flex-row items-center h-12 transform hover:translate-x-2 transition-transform ease-in duration-200 text-gray-500 hover:text-gray-800 " >
<span class=" inline-flex items-center justify-center h-12 w-12 text-lg text-gray-400 " > <svg xmlns="http://www.w3.org/2000/svg" class="h-6 w-6" fill="none" viewBox="0 0 24 24" stroke="currentColor" >
<path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M9 13h6m-3-3v6m-9 1V7a2 2 0 012-2h6l2 2h6a2 2 0 012 2v8a2 2 0 01-2 2H5a2 2 0 01-2-2z" /></svg ></span>
<span class="text-sm font-medium">Assign Books</span> </a>
</li>
<li>
<a href="./User.html" class=" flex flex-row items-center h-12 transform hover:translate-x-2 transition-transform ease-in duration-200 text-gray-500 hover:text-gray-800 " >
<span class=" inline-flex items-center justify-center h-12 w-12 text-lg text-gray-400 " ><i class="bx bx-user"></i ></span> <span class="text-sm font-medium">Users</span> </a> </li>
</ul>
</div>
</div>
<div class="bg-white p-8 rounded-md w-full object-center"> <!-- Modal pops up here --> <div class="flex items-center justify-between pb-6">
<div>
<h2 class="text-gray-600 font-semibold">ALL ASSIGNED</h2>
</div>
<div class="flex items-center justify-between"> <div class="lg:ml-40 ml-10 space-x-8"> <button id="add-user" class=" bg-indigo-600 px-4 py-2 rounded-md text-white font-semibold tracking-wide cursor-pointer " > ADD NEW USER </button> </div> </div> </div>
<!-- Modal pops up ends here--> <div> <div id="table" class="-mx-4 sm:-mx-8 px-4 sm:px-8 py-4 overflow-x-auto" > <div class="inline-block min-w-full shadow rounded-lg overflow-hidden" > <!-- Table starts here! --> <table id="user-table" class="min-w-full leading-normal"> <thead>
<tr>
<th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > USER ID </th> <th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > FIRST NAME </th> <th class=" px-5 py-3 border-b-2 border-gray-200 bg-gray-100 text-left text-xs font-semibold text-gray-600 uppercase tracking-wider " > LAST NAME </th> </tr> </thead> </table> </div> </div>
<div id="modal" class=" flex justify-center h-150 w-100 items-center antialiased h-20 hidden " > <div class=" flex flex-col w-11/12 sm:w-4/5 lg:w-1/2 max-w-2xl mx-auto rounded-lg shadow-xl " > <div class=" flex flex-row justify-between p-6 bg-white border-b border-gray-200 rounded-tl-lg rounded-tr-lg " > <p class="font-semibold text-gray-800">Add new user</p> <svg id="cancel-button" class="w-6 h-6" fill="none" stroke="currentColor" viewBox="0 0 24 24" xmlns="http://www.w3.org/2000/svg" >
<path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M6 18L18 6M6 6l12 12" ></path> </svg> </div> <div class="flex flex-col px-6 py-5 bg-gray-50"> <p class="mb-2 font-semibold text-gray-700">Enter details:</p>
<input type="text" class=" p-5 mb-5 bg-white border border-gray-200 rounded shadow-sm h-10 " placeholder="Enter First Name" id="firstName" />
<input type="text" class=" p-5 mb-5 bg-white border border-gray-200 rounded shadow-sm h-10 " placeholder="Enter Last Name" id="lastName" />
<hr />
</div>
<div class=" flex flex-row items-center justify-between p-5 bg-white border-t border-gray-200 rounded-bl-lg rounded-br-lg " >
<button id="save-btn" class="px-4 py-2 text-white font-semibold bg-blue-500 rounded" > Save </button></div>
<script src="../js/user.js"></script>
</body>
</html>Now let’s create our JS files.
assign.js
const url = "http://localhost:3000";
window.addEventListener("load", () => {
const modal = document.getElementById("assign-modal");
const returnModal = document.getElementById("return-modal");
const cancelButton = document.getElementById("cancel-button");
const assignBtn = document.getElementById("assign-book");
const returnBtn = document.getElementById("return-book");
const returnCancelButton = document.getElementById("return-cancel-button");
const toggleModal1 = () => {
modal.classList.toggle("show");
modal.classList.toggle("hidden");
};
const toggleModal2 = () => {
returnModal.classList.toggle("show");
returnModal.classList.toggle("hidden");
};
assignBtn.addEventListener("click", toggleModal1);
cancelButton.addEventListener("click", toggleModal1);
returnBtn.addEventListener("click", toggleModal2);
returnCancelButton.addEventListener("click", toggleModal2);
});
const returnButton = document.getElementById("return-btn");
// Table Logic
const tbodyEl = document.querySelector("tbody");
const tableEl = document.querySelector("table");
const allRows = (data, index) => {
const {
id,
Book: { title },
User: { firstName, lastName },
} = data;
const table = document.getElementById("assign-table");
const row = table.insertRow(-1);
row.insertCell(0).textContent = `${index}`;
row.insertCell(1).innerHTML = `<td><p class="userId">${id}</p></td>`;
row.insertCell(2).textContent = firstName + " " + lastName;
row.insertCell(3).textContent = title;
};
// Sending Data
const data = () => {
const userId = document.getElementById("user-id").value;
const bookId = document.getElementById("book-id").value;
return {
userId,
bookId,
};
};
// assign a new book
const assignBook = async () => {
const response = await fetch(`${url}/assign-book`, {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({ status: 200, data: data() }),
});
};
// Return book logic
const returnBook = async () => {
const id = document.getElementById("assigned-id").value;
await fetch(`${url}/return-book`, {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({ status: 200, id }),
});
location.reload();
};
// get all assigned books
const getAllAssignedBook = async () => {
const response = await fetch(`${url}/assigned-books`);
const { data } = await response.json();
data.forEach((book, index) => {
allRows(book, index + 1);
});
};
getAllAssignedBook();
// Save button logic
const saveBtn = document.getElementById("save-btn");
saveBtn.addEventListener("click", () => {
assignBook();
location.reload();
});
returnButton.addEventListener("click", returnBook);
books.js
const addBookBtn = document.getElementById("add-book");
const modal = document.getElementById("modal");
const cancelButton = document.getElementById("cancel-button");
const assignBtn = document.getElementById("add-book");
const successModal = document.getElementById("success-modal");
const saveCancelData = document.getElementById("save-cancel-button");
const successBtn = document.getElementById("success");
// Modal logic
window.addEventListener("load", () => {
const toggleModal = () => {
modal.classList.toggle("show");
modal.classList.toggle("hidden");
};
const toggleSaveModal = () => {
successModal.classList.toggle("show");
successModal.classList.toggle("hidden");
};
assignBtn.addEventListener("click", toggleModal);
cancelButton.addEventListener("click", toggleModal);
saveCancelData.addEventListener("click", toggleSaveModal);
successBtn.addEventListener("click", toggleSaveModal);
});
// Table Logic
const allRows = (data) => {
const table = document.getElementById("book-table");
const row = table.insertRow(-1);
const { id, title, author, description } = data;
row.insertCell(0).textContent = id;
row.insertCell(1).textContent = title;
row.insertCell(2).textContent = author;
row.insertCell(3).textContent = description;
};
const saveBtn = document.getElementById("save-btn");
const url = "http://localhost:3000";
// get all books
const getAllBooks = async () => {
const response = await fetch(`${url}/inventory`);
const { data } = await response.json();
data.forEach((book) => {
allRows(book);
});
};
getAllBooks();
// Data to send to the server
const data = () => {
const title = document.getElementById("title").value;
const author = document.getElementById("author").value;
const description = document.getElementById("description").value;
return { title, author, description };
};
// Logic for adding new book
const addBook = async () => {
try {
const response = await fetch(`${url}/add-book`, {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({ data: data() }),
});
const { status } = await response.json();
console.log(status);
} catch (error) {
alert(error.message);
}
};
// Save button logic
saveBtn.addEventListener("click", (e) => {
addBook();
location.reload();
});
user.js
const url = "http://localhost:3000";
window.addEventListener("load", () => {
const modal = document.getElementById("modal");
const cancelButton = document.getElementById("cancel-button");
const assignBtn = document.getElementById("add-user");
const toggleModal = () => {
modal.classList.toggle("show");
modal.classList.toggle("hidden");
};
assignBtn.addEventListener("click", toggleModal);
cancelButton.addEventListener("click", toggleModal);
});
// Add to table logic
const allRows = (data) => {
const { id, firstName, lastName } = data;
const table = document.getElementById("user-table");
const row = table.insertRow(-1);
row.insertCell(0).textContent = id;
row.insertCell(1).textContent = firstName;
row.insertCell(2).textContent = lastName;
};
// Data to send to the server
const data = () => {
const firstName = document.getElementById("firstName").value;
const lastName = document.getElementById("lastName").value;
return { firstName, lastName };
};
// Add new user
const addUser = async () => {
try {
const response = await fetch(`${url}/add-user`, {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({ data: data() }),
});
} catch (error) {
return error.message;
}
};
// fetch all users
const fetchUsers = async () => {
const response = await fetch(`${url}/get-users`);
const { data } = await response.json();
data.forEach((user) => {
allRows(user);
});
};
fetchUsers();
// Add user
const userBtn = document.getElementById("save-btn");
userBtn.addEventListener("click", () => {
addUser();
location.reload();
});
Testing
Now that we have successfully created our application, let’s test it.
ADD A BOOK
ASSIGN A BOOK
Add a USER
Return Book
Conclusion
Walah! we have successfully created our library management system and learned about Sequelize. If you find this post useful please feel free to appreciate it in the comments. Also, if you have any queries don’t forget to raise them in the comment section.
THANK YOU!
2 thoughts on “Nodejs MySQL with Sequelize”