Using Node.js and MySQL to perform CRUD operations including retrieving, creating, updating, and deleting data

Node.js is a popular open-source JavaScript runtime built on Chrome’s V8 JavaScript engine. It allows developers to easily build scalable server-side applications. In this tutorial, we will focus on using Node.js with MySQL to perform CRUD operations, including GET, POST, PUT, and DELETE.

Prerequisites:

• Basic knowledge of Node.js and MySQL
• Node.js installed on your machine
• MySQL installed on your machine
• MySQL workbench or any other MySQL client for database operations

Step 1: Setting up the Node.js project

1. Create a new directory for your project and navigate into it.

   mkdir node-mysql-crud
   cd node-mysql-crud

2. Initialize a new Node.js project.

   npm init -y

3. Install required dependencies.

   npm install express mysql body-parser

4. Create a new file named app.js and require the necessary modules.

   
   const express = require('express');
   const mysql = require('mysql');
   const bodyParser = require('body-parser');

const app = express();
const port = 3000;

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

Step 2: Setting up the MySQL database
1. Create a new MySQL database and a table.
```sql
CREATE DATABASE node_mysql_crud;
USE node_mysql_crud;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    email VARCHAR(255)
);

1. Connect to the MySQL database in your app.js file.

   
   const connection = mysql.createConnection({
   host: 'localhost',
   user: 'root',
   password: 'password',
   database: 'node_mysql_crud'
   });

connection.connect((err) => {
if (err) {
console.error(‘Error connecting to MySQL: ‘ + err.stack);
return;
}
console.log(‘Connected to MySQL as id ‘ + connection.threadId);
});

Step 3: Implementing CRUD operations
1. Implement GET operation to fetch all users.
```javascript
app.get('/users', (req, res) => {
  connection.query('SELECT * FROM users', (err, results) => {
    if (err) throw err;
    res.send(results);
  });
});

1. Implement POST operation to create a new user.

   app.post('/users', (req, res) => {
   const { name, email } = req.body;
   connection.query('INSERT INTO users (name, email) VALUES (?, ?)', [name, email], (err, results) => {
   if (err) throw err;
   res.send('User added successfully');
   });
   });

2. Implement PUT operation to update an existing user.

   app.put('/users/:id', (req, res) => {
   const id = req.params.id;
   const { name, email } = req.body;
   connection.query('UPDATE users SET name = ?, email = ? WHERE id = ?', [name, email, id], (err, results) => {
   if (err) throw err;
   res.send('User updated successfully');
   });
   });

3. Implement DELETE operation to delete a user.

   app.delete('/users/:id', (req, res) => {
   const id = req.params.id;
   connection.query('DELETE FROM users WHERE id = ?', [id], (err, results) => {
   if (err) throw err;
   res.send('User deleted successfully');
   });
   });

Step 4: Running the Node.js server

1. Start the Node.js server by running the following command.

   node app.js

2. You can now access your API endpoints using tools like Postman or your web browser.
   • GET: http://localhost:3000/users
   • POST: http://localhost:3000/users (with name and email in the request body)
   • PUT: http://localhost:3000/users/:id (with name and email in the request body)
   • DELETE: http://localhost:3000/users/:id

Congratulations! You have successfully implemented CRUD operations using Node.js and MySQL. You can now build more complex applications using these concepts.