In this tutorial, you will learn how to create an application using Express JS and how to perform basic CRUD operations in MySQL database using Express.js. First, we will create a database in MYSQL, define our table, and then connect to our database using our Express JS application. After that, we will declare the routes in our server.js file and use them to perform the basic CRUD operations.
But before we get started with CRUD in MYSQL with ExpressJS, in case you’re wondering, we should first understand the following.
What is CRUD?
These are fundamental functions that are most frequently used for basic functionalities while working with any database, especially relational databases such as MySQL.
Create: This creates a new instance as defined in our model and adds it to the database. The INSERT command is there in SQL
Read: It is used for reading the data set. The SELECT command is there in SQL
Update: It is used for updating an instance currently present in the database. The UPDATE command is there in SQL
Delete: It is used for deleting an instance from the database. In SQL, this is done with the help of the DELETE command
Implementation of CRUD in MySQL with ExpressJS
Let's see the Implementation of CRUD in MySQL with ExpressJS.
Setup MySQL Workbench
Follow the below steps to setup your MySQL workbench.
Open a local instance of the database, create a database named 'test'.
Navigate to the create table and add the fields you want in your database.
Mark checkboxes such as requirements type of field VARCHAR corresponding to a string, boolean or INT, NN to make the field not null, UQ to make the field only accept unique value, AQ to make the filed auto-increment such as id
Click apply.
Note: SQL script can also be coded in this review section.
Creating a MySQL Database and Table
To create a new database, we will use CREATE DATABASE keyword. CREATE TABLE is the keyword used for creating a new table. Use the following query to create a database and a table.
CREATE DATABASE db;
USE db;
CREATE TABLE `test`.`booksdata` (
`id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`author` VARCHAR(100) NOT NULL,
`price` INT NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE);
Creating a Server Application using Nodejs and ExpressJs
Follow the given steps to create a Server application using Nodejs and ExpressJs.
Step- 1: Create a new directory for your project, for example, My-app. Use the following commands:
mkdir my-app
cd my-app
Step- 2: Then, we need to install MySQL and Express as our dependencies.
npm install mysql express
Step- 3: Create a new file named server.js and write the code to set up a basic express server and routes to handle CRUD operations and perform the following steps:
Creating a connection to our database
First, we need to import "express" and "mysql".
app.use(express.json()) will specify that our application will accept JSON datatype and will be able to parse it.
Then we create a connection using the "createConnection" keyword and specify the host, user (default = root), password and database name.
This will create a connection to our database if all the credentials are valid.
Creating our route handler function for CRUD in Express JS
To fetch all the books, we use the app.get() method on our route to fetch data from our database. We create a string to write our SQL query "SELECT * FROM booksdata" which will fetch all the entries from our table if any error occurs, it will log the error in the console else, return the data.
To create a new book, we use the app.post() method on our route, which signifies that we are sending data to the database. Then we create our SQL query as 'INSERT INTO booksdata(`title`,`author`,`price`) VALUES (?)' then create a values variable to get all the values from our request body and then send these values in our query as db.query(q, [values], (err, data).
To delete a book, we use the app.delete("/books/:id") method on our route and send the book id as a parameter, for example, /books/3. Then we write our delete query in SQL as " DELETE FROM booksdata WHERE id = ? ". It will delete the entry which matches the id.
To update an existing book, we use the app.put('/books/:id') method on our route and send the book id as a parameter. Then we write our update query in SQL as "UPDATE booksdata SET `title`= ?, `author`= ?, `price`= ? WHERE id = ?" and send the values to be updated with our query.
Server.js
import express from "express";
import mysql from "mysql";
const app = express();
app.use(express.json());
// Connect to the database
const db = mysql.createConnection({
host: "localhost",
user: "root",
password: "password",
database: "test",
});
// To fetch all the books
app.get("/books", (req, res) => {
const q = "SELECT * FROM booksdata";
db.query(q, (err, data) => {
if (err) {
console.log(err);
return res.json(err);
}
return res.json(data);
});
});
// To create a new book
app.post("/books", (req, res) => {
const q = 'INSERT INTO booksdata(`title`,`author`,`price`) VALUES (?)';
const values = [
req.body.title,
req.body.author,
req.body.price,
];
db.query(q, [values], (err, data) => {
if (err) return res.send(err);
return res.json(data);
});
});
// To delete an existing book
app.delete("/books/:id", (req, res) => {
const bookId = req.params.id;
const q = " DELETE FROM booksdata WHERE id = ? ";
db.query(q, [bookId], (err, data) => {
if (err) return res.send(err);
return res.json(data);
});
});
// To update an existing book
app.put('/books/:id', (req, res) => {
const bookId = req.params.id;
const q = "UPDATE booksdata SET `title`= ?, `author`= ?, `price`= ? WHERE id = ?";
const values = [
req.body.title,
req.body.author,
req.body.price,
];
db.query(q, [...values,bookId], (err, data) => {
if (err) return res.send(err);
return res.json(data);
});
});
app.listen(8800, () => {
console.log("Connected to backend!");
});
Step- 4: Run the Express Application. To run the express.js application, we need to make sure to save the above code in a file, for example, server.js. Open a new terminal and then start the server application by using the following command in the terminal:
node server.js
This command will start the server application at port 8800, as specified above. We can access the routes we defined in the code by opening any web browser and navigating to the following URL: http://localhost:8800/
Output
Fetch all user queries
Update an existing book query result
Delete an existing book query result
Create a new book query result
Frequently Asked Questions
What is the advantage of using MySQL?
MySQL can run on all Operating Systems. It focuses on performance and has Full-Text Indexing and Searching. It is very fast because of its query caching feature.
What is the use of (req, res, next) arguments in the Express JS route handler function?
Req is the request object, res is the response object, and next is optional. It is a function in the Express router which executes the middleware succeeding the current middleware.
How is SQL different from MYSQL?
SQL is the language for querying a database, whereas MYSQL is a database management system for SQL.
Conclusion
In this article, we discussed how to perform CRUD in MYSQL with Express JS. We learned about creating databases, setting up connections to that database, and performing the CRUD operations using the routes that we created in the above examples. You can also check how to carry out the CRUD Operations in SQL
Alright! So now that you have learned about CRUD operations using Express JS in the MYSQL database, you can also refer to other similar articles.
But suppose you have just started your learning process and are looking for questions from tech giants like Amazon, Microsoft, Uber, etc. For placement preparations, you must look at the problems, interview experiences, and interview bundles.
306+ registered