In web and app development, databases play a crucial role in managing data efficiently. MySQL, one of the most popular SQL databases, is widely used for its reliability, performance, and ease of use. This article will guide you through the process of integrating MySQL with a Node.js application, enabling you to perform Create, Read, Update, and Delete (CRUD) operations on your data. We'll be using Express, a popular web application framework for Node.js, to build our server and mysql2, a MySQL client for Node.js, to interact with our MySQL database.

Ensure Node.js is installed on your system. You can download it from the official Node.js website or use a package manager like Homebrew for macOS or apt for Linux.

Download Node.js Installer: Visit the official Node.js website at https://nodejs.org/en/download/ and download the Windows Installer (.msi) for the latest stable version with long-term support (LTS). The LTS version is recommended for most users as it is tested and stable 

Step 1: Setting Up MySQL Database

First, you need to create a MySQL database and a table to store your data. You can do this using MySQL Workbench or any other MySQL client. Here's how to create a database and a table named students:

CREATE DATABASE studentdb;

USE studentdb;
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    grade VARCHAR(10) NOT NULL
);

• CREATE DATABASE studentdb; creates a new database named studentdb.
• USE studentdb; selects the studentdb database for use.
• The CREATE TABLE statement defines a table named students with columns for id, name, age, and grade.

Step 2: Setting Up Your Node.js Project

1. Initialize Your Project: Open VSCode, create a new folder for your project, and open it in VSCode. Open the terminal in VSCode and run npm init -y to create a package.json file.
2. Install Dependencies: You need to install express, body-parser, and mysql2 packages. Run the following commands in the terminal:

npm install express body-parser mysql2

Step 3: Writing Your Node.js Code

Create a new file named app.js in your project folder and paste the following code:

Importing Modules

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

• Express: A web application framework for Node.js, designed for building web applications and APIs.
• Body-parser: Middleware to parse incoming request bodies in a middleware before your handlers, available under the req.body property.
• mysql2: A MySQL client for Node.js with focus on performance. It is a drop-in replacement for the mysql module.

Setting Up Express App

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

• express() creates an Express application.
• port is set to 3000, which is the port the server will listen on.

MySQL Connection

const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: "enter_your_password_here",
    database: 'studentdb'
});

• mysql.createConnection() establishes a connection to the MySQL database.
• host, user, password, and database are configuration options for the connection.

Connecting to MySQL Database

db.connect((err) => {
    if (err) {
        console.error('Error connecting to MySQL database: ' + err.stack);
        return;
    }
    console.log('Connected to MySQL database');
});

• db.connect() attempts to connect to the database.
• If there's an error, it logs the error message.
• If the connection is successful, it logs a success message.

Middleware

app.use(bodyParser.json());

• app.use(bodyParser.json()) adds the body-parser middleware to the application. This allows Express to parse JSON request bodies.

Creating a Student

app.post('/students', (req, res) => {
    const { name, age, grade } = req.body;
    const sql = 'INSERT INTO students (name, age, grade) VALUES (?, ?, ?)';
    db.query(sql, [name, age, grade], (err, result) => {
        if (err) {
            console.error('Error creating student: ' + err.stack);
            res.status(500).send('Error creating student');
            return;
        }
        console.log('Student created successfully');
        res.status(201).send('Student created successfully');
    });
});

• app.post('/students', ...) defines a route for creating a new student.
• It extracts name, age, and grade from the request body.
• It executes an SQL query to insert the new student into the students table.
• If there's an error, it sends a 500 status code with an error message.
• If successful, it sends a 201 status code with a success message.

Reading All Students

app.get('/students', (req, res) => {
    const sql = 'SELECT * FROM students';
    db.query(sql, (err, results) => {
        if (err) {
            console.error('Error retrieving students: ' + err.stack);
            res.status(500).send('Error retrieving students');
            return;
        }
        res.json(results);
    });
});

• app.get('/students', ...) defines a route for retrieving all students.
• It executes an SQL query to select all students from the students table.
• If there's an error, it sends a 500 status code with an error message.
• If successful, it sends the results as JSON.

Updating a Student

app.put('/students/:id', (req, res) => {
    const id = req.params.id;
    const { name, age, grade } = req.body;
    const sql = 'UPDATE students SET name=?, age=?, grade=? WHERE id=?';
    db.query(sql, [name, age, grade, id], (err, result) => {
        if (err) {
            console.error('Error updating student: ' + err.stack);
            res.status(500).send('Error updating student');
            return;
        }
        console.log('Student updated successfully');
        res.send('Student updated successfully');
    });
});

• app.put('/students/:id', ...) defines a route for updating a student by ID.
• It extracts the id from the URL parameters and name, age, and grade from the request body.
• It executes an SQL query to update the student in the students table.
• If there's an error, it sends a 500 status code with an error message.
• If successful, it sends a success message.

Deleting a Student

app.delete('/students/:id', (req, res) => {
    const id = req.params.id;
    const sql = 'DELETE FROM students WHERE id=?';
    db.query(sql, [id], (err, result) => {
        if (err) {
            console.error('Error deleting student: ' + err.stack);
            res.status(500).send('Error deleting student');
            return;
        }
        console.log('Student deleted successfully');
        res.send('Student deleted successfully');
    });
});

• app.delete('/students/:id', ...) defines a route for deleting a student by ID.
• It extracts the id from the URL parameters.
• It executes an SQL query to delete the student from the students table.
• If there's an error, it sends a 500 status code with an error message.
• If successful, it sends a success message.

Starting the Server

app.listen(port, () => {
    console.log(`Server running at http://localhost:${port}`);
});

• app.listen(port, ...) starts the server on the specified port.
• It logs a message indicating the server is running.

Now your final codes in the app.js should look like this:

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

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

// MySQL Connection
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: "enter_your_password_here",
    database: 'studentdb'
});

db.connect((err) => {
    if (err) {
        console.error('Error connecting to MySQL database: ' + err.stack);
        return;
    }
    console.log('Connected to MySQL database');
});

// Middleware
app.use(bodyParser.json());

// Create Student
app.post('/students', (req, res) => {
    const { name, age, grade } = req.body;
    const sql = 'INSERT INTO students (name, age, grade) VALUES (?, ?, ?)';
    db.query(sql, [name, age, grade], (err, result) => {
        if (err) {
            console.error('Error creating student: ' + err.stack);
            res.status(500).send('Error creating student');
            return;
        }
        console.log('Student created successfully');
        res.status(201).send('Student created successfully');
    });
});

// Read All Students
app.get('/students', (req, res) => {
    const sql = 'SELECT * FROM students';
    db.query(sql, (err, results) => {
        if (err) {
            console.error('Error retrieving students: ' + err.stack);
            res.status(500).send('Error retrieving students');
            return;
        }
        res.json(results);
    });
});

// Update Student
app.put('/students/:id', (req, res) => {
    const id = req.params.id;
    const { name, age, grade } = req.body;
    const sql = 'UPDATE students SET name=?, age=?, grade=? WHERE id=?';
    db.query(sql, [name, age, grade, id], (err, result) => {
        if (err) {
            console.error('Error updating student: ' + err.stack);
            res.status(500).send('Error updating student');
            return;
        }
        console.log('Student updated successfully');
        res.send('Student updated successfully');
    });
});

// Delete Student
app.delete('/students/:id', (req, res) => {
    const id = req.params.id;
    const sql = 'DELETE FROM students WHERE id=?';
    db.query(sql, [id], (err, result) => {
        if (err) {
            console.error('Error deleting student: ' + err.stack);
            res.status(500).send('Error deleting student');
            return;
        }
        console.log('Student deleted successfully');
        res.send('Student deleted successfully');
    });
});

app.listen(port, () => {
    console.log(`Server running at http://localhost:${port}`);
});

This code does the following:

• Sets up an Express server.
• Connects to the MySQL database using the mysql2 package.
• Defines routes for creating, reading, updating, and deleting students in the students table.
• Starts the server on port 3000.

Step 4: Running Your Application

1. Start Your Server: In the terminal, run node app.js. You should see the message "Server running at http://localhost:3000".
2. Testing Your API: Use Postman or Thunder Client to test your API endpoints. For example, to create a new student, send a POST request to http://localhost:3000/students with a JSON body containing name, age, and grade.

Navigate to http://localhost:3000

Navigate to /students route to retrieve students data:

  http://localhost:3000/students

To install the Thunder Client extension in VSCode, follow these steps:

1. Open VSCode: Start by opening Visual Studio Code on your computer.
2. Access Extensions: Navigate to the Extensions view by clicking on the square icon on the sidebar or by using the shortcut Ctrl + Shift + X.
3. Search for Thunder Client: In the Extensions view, type "Thunder Client" into the search bar.
4. Install Thunder Client: Find the Thunder Client extension in the search results and click the "Install" button.
5. Launch Thunder Client: Once installed, you can launch Thunder Client by clicking on its icon in the Activity Bar on the side, or by using the shortcut Ctrl + Shift + R.

To create a new student using Thunder Client in VSCode, follow these steps:

1. Open Thunder Client: Click on the Thunder Client icon in the Activity Bar on the side of VSCode to launch it.
2. Create a New Request: Click on the "New Request" button to start a new API request.
3. Configure the Request:

• Method: Select POST from the dropdown menu since you're creating a new student.
• URL: Enter the endpoint URL for creating a student. For example, if your server is running locally on port 3000, the URL might be http://localhost:3000/students.
• Headers: Ensure you have the Content-Type header set to application/json to indicate that you're sending JSON data.
• Body: Switch to the "Body" tab and select "JSON" from the options. Enter the student data in JSON format, like so:

{"name": "Sah Titus","age": 100,"grade": "400"
}

1. Send the Request: Click the "Send" button to execute the request. Thunder Client will display the response from your server in the "Response" section.
2. Review the Response: Check the response status and body to ensure the student was created successfully.

Conclusion

Integrating MySQL with Node.js using Express provides a powerful foundation for building RESTful APIs. This setup allows for efficient data management and manipulation, serving as a solid starting point for developers to explore more complex applications. Whether for personal projects or professional development, mastering this combination of technologies is crucial for modern web development.