✦ Connect Node.js to MySQL: Step-by-Step Database Tutorial

❖ Introduction

Node.js is widely used to develop fast and scalable backend applications. However, most real-world applications need a database to store and manage information permanently. For example, a student management system stores student names, mobile numbers, and course details, while an e-commerce website stores products, customers, and orders.

MySQL is a popular relational database management system that stores data in tables. By connecting Node.js to MySQL, developers can create applications that insert, read, update, and delete records.

In this tutorial, you will learn how to connect Node.js to MySQL using Express.js, mysql2, and dotenv. You will also create a practical Student Management API with database connectivity, input validation, and error handling.

✦ 1. Prerequisites

Before starting, install the following tools:

• Node.js and npm: To run JavaScript on the server.

• MySQL Server: To store application data.

• MySQL Workbench or phpMyAdmin: To execute SQL queries, if available.

• Visual Studio Code: To write and manage code.

• Postman: To test API requests.

If you are using XAMPP, make sure its MySQL/MariaDB service is running. The default local configuration commonly uses localhost and port 3306, but check your own settings.

✦ 2. Create a Node.js Project

Open your terminal and run these commands:

mkdir node-mysql-tutorial
cd node-mysql-tutorial
npm init -y
npm install express mysql2 dotenv

These packages have different responsibilities:

• express creates the API routes.

• mysql2 connects Node.js to MySQL and supports promises.

• dotenv loads configuration values from a .env file.

Create this project structure:

node-mysql-tutorial/
├── server.js
├── .env
├── .gitignore
└── package.json

✦ 3. Create a MySQL Database and Table

Open your MySQL client and execute the following SQL:

CREATE DATABASE student_db;

USE student_db;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL,
    course VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The student_db database contains a students table. The id column automatically generates a unique number for every student.

You can insert a sample record to check the table:

INSERT INTO students (name, mobile, course)
VALUES ('Rahul Sharma', '9876543210', 'Node.js');

To view the saved records, run:

SELECT * FROM students;

✦ 4. Configure Database Credentials Using .env

Create a file named .env in your project folder:

PORT=3000
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=
DB_NAME=student_db
DB_PORT=3306

These are example local development settings. If your MySQL account has a password, enter it in DB_PASSWORD. Never assume that every installation uses an empty password or the same port.

Create a .gitignore file:

node_modules/
.env

This prevents your environment configuration from being accidentally committed to a Git repository. Do not share real database credentials in public code or screenshots.

✦ 5. Connect Node.js to MySQL

Open server.js and add the following complete code. It creates a connection pool, checks the database connection, and provides endpoints to list, retrieve, and create students.

require("dotenv").config();

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

const app = express();

app.use(express.json());

const pool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    port: Number(process.env.DB_PORT || 3306),
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Check API and database connectivity
app.get("/", async (req, res) => {
    try {
        await pool.query("SELECT 1");

        res.json({
            success: true,
            message: "Node.js API and MySQL are connected"
        });
    } catch (error) {
        console.error("Database connection error:", error.message);

        res.status(500).json({
            success: false,
            message: "Database connection failed"
        });
    }
});

// Get all students
app.get("/students", async (req, res) => {
    try {
        const [rows] = await pool.execute(
            "SELECT id, name, mobile, course, created_at FROM students ORDER BY id DESC"
        );

        res.json({
            success: true,
            data: rows
        });
    } catch (error) {
        console.error("Fetch students error:", error.message);

        res.status(500).json({
            success: false,
            message: "Unable to fetch students"
        });
    }
});

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

    if (!Number.isInteger(id) || id < 1) {
        return res.status(400).json({
            success: false,
            message: "A valid student ID is required"
        });
    }

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

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

        res.json({
            success: true,
            data: rows[0]
        });
    } catch (error) {
        console.error("Fetch student error:", error.message);

        res.status(500).json({
            success: false,
            message: "Unable to fetch student"
        });
    }
});

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

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

    if (name.trim().length > 100 ||
        mobile.trim().length > 15 ||
        course.trim().length > 100) {
        return res.status(400).json({
            success: false,
            message: "One or more fields exceed the allowed length"
        });
    }

    try {
        const [result] = await pool.execute(
            "INSERT INTO students (name, mobile, course) VALUES (?, ?, ?)",
            [name.trim(), mobile.trim(), course.trim()]
        );

        res.status(201).json({
            success: true,
            message: "Student added successfully",
            studentId: result.insertId
        });
    } catch (error) {
        console.error("Insert student error:", error.message);

        res.status(500).json({
            success: false,
            message: "Unable to save student"
        });
    }
});

const PORT = Number(process.env.PORT || 3000);

async function startServer() {
    try {
        // Verify the database before accepting requests
        await pool.query("SELECT 1");

        app.listen(PORT, () => {
            console.log(`Server running at http://localhost:${PORT}`);
        });
    } catch (error) {
        console.error("Could not connect to MySQL:", error.message);
        process.exitCode = 1;
    }
}

startServer();

❖ Important Concepts in This Code

Connection pool: mysql.createPool() manages reusable database connections. It is generally more suitable for web applications than creating a brand-new connection for every request.

Promise-based queries: Using mysql2/promise allows database operations to work with async and await.

Parameterized queries: The ? placeholders keep user-provided values separate from the SQL query structure. This helps protect against SQL injection.

Input validation: The API checks that the required fields are strings, are not empty, and do not exceed the database column lengths.

✦ 6. Run the Node.js Server

Start your application:

node server.js

If the connection succeeds, you should see a message similar to:

Server running at http://localhost:3000

Open this URL in your browser:

http://localhost:3000/

A successful response should look like this:

{
  "success": true,
  "message": "Node.js API and MySQL are connected"
}

If the server does not start, check that MySQL is running and your .env settings are correct.

✦ 7. Test the Student API

Use Postman to test the endpoints.

❖ Get All Students

Method: GET

URL:

http://localhost:3000/students

This endpoint returns student records stored in MySQL.

❖ Get One Student

Method: GET

URL:

http://localhost:3000/students/1

Replace 1 with an existing student ID.

❖ Add a New Student

Method: POST

URL:

http://localhost:3000/students

In Postman, select Body → raw → JSON and enter:

{
  "name": "Aman Verma",
  "mobile": "9123456780",
  "course": "Node.js Backend"
}

A successful request returns a response similar to:

{
  "success": true,
  "message": "Student added successfully",
  "studentId": 2
}

The generated ID may be different depending on your existing records. Check MySQL with SELECT * FROM students; to verify that the record was saved.

✦ 8. Why Use MySQL Instead of an Array?

Beginners often first store data in a JavaScript array:

const students = [];

students.push({
    name: "Aman Verma",
    course: "Node.js"
});

This is useful for learning, but the data exists only in the running process. It normally disappears when the application restarts.

When you insert data into MySQL, it is stored in the database and remains available after a Node.js restart, subject to the database and its persistence configuration.

For applications such as student registration, school management, billing software, inventory management, and CRM systems, persistent database storage is essential.

✦ 9. Common Node.js and MySQL Errors

❖ Access denied for user

Your MySQL username or password may be incorrect. Check DB_USER and DB_PASSWORD.

❖ ECONNREFUSED

MySQL may not be running, or the configured host or port may be incorrect. Verify DB_HOST and DB_PORT.

❖ Unknown database

Make sure you have created student_db and that the database name in .env matches it exactly.

❖ Table doesn't exist

Run the CREATE TABLE students query in the correct database.

❖ Cannot read properties of undefined

If req.body is missing, confirm that app.use(express.json()) appears before the API routes and that Postman sends valid JSON with the appropriate content type.

❖ Environment variables are missing

Ensure .env is in the project root and require("dotenv").config() runs before the connection pool is created.

✦ 10. Best Practices for Production Applications

• Use a dedicated MySQL user with only the permissions the application requires instead of using the database root account.

• Keep credentials in environment variables or a suitable secrets manager.

• Use parameterized queries for values received from users.

• Validate all request data on the server.

• Avoid returning raw database errors or sensitive configuration to API clients.

• Use HTTPS when transmitting sensitive information in production.

• Back up the database regularly and test your recovery process.

• Close the pool gracefully when shutting down a long-running application.

✦ Frequently Asked Questions (FAQs)

❖ 1. Can Node.js connect directly to MySQL?

Yes. Node.js can connect to MySQL using packages such as mysql2. Express.js is optional; it is used in this tutorial to create API endpoints.

❖ 2. What is the difference between mysql and mysql2?

mysql2 is a widely used MySQL client for Node.js. It supports promise-based APIs, prepared statements, and features useful for modern asynchronous applications.

❖ 3. Do I need Express.js to connect Node.js to MySQL?

No. You can use mysql2 directly in a Node.js application. Express.js makes it easier to organize HTTP routes and build REST APIs.

❖ 4. Will MySQL data disappear when I restart Node.js?

Normally, committed records remain stored in MySQL after the Node.js process restarts. Data durability still depends on the database configuration and storage environment.

❖ 5. Can I use this project for a real student management system?

This is a beginner foundation. A complete production system would also need features such as authentication, authorization, update and delete routes, pagination, stronger validation, logging, and appropriate database backups.

✦ Conclusion

Connecting Node.js to MySQL is an important backend development skill. In this practical tutorial, you created a MySQL database, configured credentials with .env, established a connection pool using mysql2, and built API endpoints to list, retrieve, and register students.

Once these concepts are clear, you can expand the project into a complete Student Management System with CRUD operations, login functionality, and an admin dashboard.

If you want to learn Node.js, Express.js, MySQL, and practical backend development, explore the learning resources at Param Classes. For website development and digital solutions, visit ParamWebInfo.

❖ Suggested Blog Tags

Node.js, MySQL, Node.js MySQL Connection, Express.js, mysql2, Node.js REST API, Backend Development, Student Management API, JavaScript, Database Connectivity