MySQL Connection in Node.js

Learn how to connect Node.js with MySQL, create databases and tables, insert records, retrieve data, update records, and delete records.

1. Why Connect Node.js with MySQL?

Node.js can handle application logic and API requests, while MySQL stores application data permanently.

2. Requirements

Before connecting Node.js with MySQL, install the following:

• Node.js and npm
• MySQL Server
• A code editor such as Visual Studio Code
• MySQL Workbench or another MySQL client (optional)

Check your Node.js and npm installations:

node -v
npm -v

3. Create a Node.js Project

Create a folder named node-mysql-app.

mkdir node-mysql-app
cd node-mysql-app
npm init -y

The npm init -y command creates a default package.json file.

4. Install the MySQL2 Package

We will use the mysql2 package to connect Node.js with MySQL.

npm install mysql2

5. Create a MySQL Database

Open your MySQL client and execute the following SQL command:

CREATE DATABASE school_db;

Select the database:

USE school_db;

Now create a table named students:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    course VARCHAR(100) NOT NULL
);

Table Structure

6. Connect Node.js with MySQL

Create a file named db.js in your project folder.

const mysql = require("mysql2/promise");

const pool = mysql.createPool({
    host: "localhost",
    user: "YOUR_MYSQL_USER",
    password: "YOUR_MYSQL_PASSWORD",
    database: "school_db",
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

module.exports = pool;

Replace the following values with your MySQL credentials:

• YOUR_MYSQL_USER – your MySQL username.
• YOUR_MYSQL_PASSWORD – your MySQL password.

7. Test the MySQL Connection

Create a file named test-db.js.

const pool = require("./db");

async function testConnection() {
    try {
        const connection = await pool.getConnection();

        console.log("MySQL connected successfully!");

        connection.release();
    } catch (error) {
        console.error("MySQL connection failed:", error.message);
    } finally {
        await pool.end();
    }
}

testConnection();

Run the file:

node test-db.js

MySQL connected successfully!

8. Insert Data into MySQL

We can insert a student into the database using an SQL INSERT query.

const pool = require("./db");

async function addStudent() {
    try {
        const sql = `
            INSERT INTO students (name, email, course)
            VALUES (?, ?, ?)
        `;

        const values = [
            "Rahul Kumar",
            "rahul@example.com",
            "Node.js"
        ];

        const [result] = await pool.execute(sql, values);

        console.log("Student added successfully!");
        console.log("Student ID:", result.insertId);
    } catch (error) {
        console.error("Error:", error.message);
    } finally {
        await pool.end();
    }
}

addStudent();

Important Explanation

• pool.execute() executes the SQL statement.
• The question marks are placeholders for parameter values.
• result.insertId returns the generated student ID.

9. Retrieve Data from MySQL

Use the SQL SELECT statement to retrieve all students.

const pool = require("./db");

async function getStudents() {
    try {
        const [rows] = await pool.execute(
            "SELECT id, name, email, course FROM students"
        );

        console.log(rows);
    } catch (error) {
        console.error("Error:", error.message);
    } finally {
        await pool.end();
    }
}

getStudents();

Example Output

[
  {
    id: 1,
    name: 'Rahul Kumar',
    email: 'rahul@example.com',
    course: 'Node.js'
  }
]

The returned rows value is an array containing the matching database records.

10. Retrieve a Student by ID

To retrieve one student, use a parameterized query with a WHERE condition.

const pool = require("./db");

async function getStudentById(id) {
    try {
        const [rows] = await pool.execute(
            "SELECT id, name, email, course FROM students WHERE id = ?",
            [id]
        );

        if (rows.length === 0) {
            console.log("Student not found");
            return;
        }

        console.log(rows[0]);
    } catch (error) {
        console.error("Error:", error.message);
    } finally {
        await pool.end();
    }
}

getStudentById(1);

11. Update Data in MySQL

Use the SQL UPDATE statement to change an existing record.

const pool = require("./db");

async function updateStudent() {
    try {
        const [result] = await pool.execute(
            "UPDATE students SET course = ? WHERE id = ?",
            ["Full Stack Development", 1]
        );

        if (result.affectedRows === 0) {
            console.log("Student not found or no changes made");
        } else {
            console.log("Student updated successfully!");
        }
    } catch (error) {
        console.error("Error:", error.message);
    } finally {
        await pool.end();
    }
}

updateStudent();

12. Delete Data from MySQL

Use the SQL DELETE statement to remove a record.

const pool = require("./db");

async function deleteStudent() {
    try {
        const [result] = await pool.execute(
            "DELETE FROM students WHERE id = ?",
            [1]
        );

        if (result.affectedRows === 0) {
            console.log("Student not found");
        } else {
            console.log("Student deleted successfully!");
        }
    } catch (error) {
        console.error("Error:", error.message);
    } finally {
        await pool.end();
    }
}

deleteStudent();

13. Connect MySQL with Express.js

Now let's build a small REST API that retrieves student data from MySQL.

Create a file named server.js.

const express = require("express");
const pool = require("./db");

const app = express();

app.use(express.json());

// GET all students
app.get("/students", async (req, res) => {
    try {
        const [rows] = await pool.execute(
            "SELECT id, name, email, course FROM students"
        );

        res.json(rows);
    } catch (error) {
        console.error(error);

        res.status(500).json({
            message: "Unable to retrieve students"
        });
    }
});

// GET one student
app.get("/students/:id", async (req, res) => {
    try {
        const id = Number(req.params.id);

        if (!Number.isInteger(id) || id <= 0) {
            return res.status(400).json({
                message: "Invalid student ID"
            });
        }

        const [rows] = await pool.execute(
            "SELECT id, name, email, course FROM students WHERE id = ?",
            [id]
        );

        if (rows.length === 0) {
            return res.status(404).json({
                message: "Student not found"
            });
        }

        res.json(rows[0]);
    } catch (error) {
        console.error(error);

        res.status(500).json({
            message: "Unable to retrieve student"
        });
    }
});

// POST a student
app.post("/students", async (req, res) => {
    try {
        const { name, email, course } = req.body;

        if (
            typeof name !== "string" || !name.trim() ||
            typeof email !== "string" || !email.trim() ||
            typeof course !== "string" || !course.trim()
        ) {
            return res.status(400).json({
                message: "Name, email, and course are required"
            });
        }

        const [result] = await pool.execute(
            `INSERT INTO students (name, email, course)
             VALUES (?, ?, ?)`,
            [name.trim(), email.trim(), course.trim()]
        );

        res.status(201).json({
            message: "Student added successfully",
            id: result.insertId
        });
    } catch (error) {
        if (error.code === "ER_DUP_ENTRY") {
            return res.status(409).json({
                message: "Email already exists"
            });
        }

        console.error(error);

        res.status(500).json({
            message: "Unable to add student"
        });
    }
});

const PORT = 5000;

app.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`);
});

Install Express if you have not already installed it:

npm install express

Start your API:

node server.js

14. Test the MySQL API Using Postman

Use http://localhost:5000 as the base URL. For POST requests, select Body → raw → JSON.

{
    "name": "Priya Sharma",
    "email": "priya@example.com",
    "course": "React"
}

Send the request and check the response status code and JSON output. The new record should also appear in the MySQL table.

15. Common MySQL Connection Errors

16. Practical Assignment

17. Lesson Summary