How to Connect Node.js with MySQL

Node.js is a powerful JavaScript runtime that enables developers to build fast and scalable backend applications. When paired with MySQL, a widely-used open-source relational database, you can create robust, data-driven applications. This comprehensive blog walks you through the process of connecting Node.js to MySQL, complete with practical code examples, error handling, and best practices for production-ready applications.

Table of Contents

1. Prerequisites
2. Why Use Node.js with MySQL?
3. Setting Up MySQL and Creating a Database
4. Initializing a Node.js Project
5. Installing Required Dependencies
6. Establishing a Connection Between Node.js and MySQL
7. Performing CRUD Operations
8. Resolving MySQL Authentication Errors (MySQL 8+)
9. Best Practices for Production
10. Conclusion

1. Prerequisites

To follow this guide, ensure you have the following installed on your system:

• Node.js (version 12 or higher): Download and install from nodejs.org.
• MySQL Server: Obtain from dev.mysql.com/downloads/mysql/.
• MySQL Workbench or Command-Line Interface (CLI): Optional for easier database management.

Basic knowledge of JavaScript, Node.js, and SQL is recommended but not mandatory, as this guide is beginner-friendly.

2. Why Use Node.js with MySQL?

Combining Node.js and MySQL offers several advantages:

• Non-Blocking I/O: Node.js’s asynchronous nature ensures efficient handling of database queries, improving application performance.
• Reliable Database: MySQL is a mature, open-source relational database with strong community support and extensive features.
• Seamless Integration: Libraries like mysql or mysql2 simplify connecting and querying MySQL from Node.js.
• Versatile Applications: Ideal for building web applications, dashboards, admin panels, and APIs for small to medium-sized projects.

This stack is particularly well-suited for developers seeking a lightweight yet powerful backend solution.

3. Setting Up MySQL and Creating a Database

To begin, you need a MySQL database and a table to store data.

Step 1: Access MySQL

Open your MySQL CLI or MySQL Workbench to interact with the MySQL server.

Step 2: Create a Database and Table

Execute the following SQL commands to create a database named testdb and a table named users:

CREATE DATABASE testdb;

USE testdb;

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

• The users table has three columns: id (auto-incrementing primary key), name, and email.
• These commands set up the database structure for our Node.js application.

4. Initializing a Node.js Project

Create a new Node.js project by running the following commands in your terminal:

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

This creates a project directory with a default package.json file, setting up the foundation for your application.

5. Installing Required Dependencies

For MySQL 8 and above, the mysql2 package is recommended due to its support for modern authentication protocols.

Install mysql2 by running:

npm install mysql2

This package provides the necessary tools to connect Node.js to MySQL.

6. Establishing a Connection Between Node.js and MySQL

Create a file named db.js to configure and establish a connection to the MySQL database:

const mysql = require('mysql2');

const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',        // Replace with your MySQL username
    password: '',        // Replace with your MySQL password
    database: 'testdb'
});

db.connect((err) => {
    if (err) {
        console.error('Database connection failed:', err.message);
        return;
    }
    console.log('Connected to MySQL database!');
});

module.exports = db;

This script:

• Imports the mysql2 package.
• Creates a connection object with your MySQL credentials.
• Tests the connection and logs the result.
• Exports the connection for use in other files.

Ensure you replace user and password with your actual MySQL credentials.

7. Performing CRUD Operations

Create a file named app.js to demonstrate basic CRUD (Create, Read, Update, Delete) operations:

const db = require('./db');

// Create a new user
const newUser = { name: 'John Doe', email: 'john@example.com' };

db.query('INSERT INTO users SET ?', newUser, (err, result) => {
    if (err) throw err;
    console.log('User inserted with ID:', result.insertId);

    // Fetch all users
    db.query('SELECT * FROM users', (err, results) => {
        if (err) throw err;
        console.log('All Users:', results);

        // Update a user
        db.query('UPDATE users SET name = ? WHERE id = ?', ['Jane Doe', result.insertId], (err) => {
            if (err) throw err;
            console.log('User updated');

            // Delete a user
            db.query('DELETE FROM users WHERE id = ?', [result.insertId], (err) => {
                if (err) throw err;
                console.log('User deleted');
            });
        });
    });
});

This script demonstrates:

• Create: Inserts a new user into the users table.
• Read: Retrieves all users from the table.
• Update: Modifies the name of the newly inserted user.
• Delete: Removes the user from the table.

Run the script using node app.js to see the operations in action.

8. Resolving MySQL Authentication Errors (MySQL 8+)

When using MySQL 8 or later, you may encounter the following error:

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server

This occurs because MySQL 8 uses a newer authentication plugin (caching_sha2_password) that may not be supported by older clients.

Solution: Modify the Authentication Plugin

Log in to MySQL and execute:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;

Alternatively, create a new user with the native authentication plugin:

CREATE USER 'nodeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
GRANT ALL PRIVILEGES ON testdb.* TO 'nodeuser'@'localhost';
FLUSH PRIVILEGES;

Update db.js with the new user credentials if you created a new user. Replace your_password with a secure password.

9. Best Practices for Production

To ensure your Node.js and MySQL application is secure and efficient, follow these best practices:

• Use Environment Variables: Store sensitive credentials (e.g., database username and password) in a .env file using the dotenv package.
• Implement Connection Pooling: Use mysql2.createPool() instead of createConnection() to manage multiple connections efficiently in high-traffic applications.
• Prevent SQL Injection: Always validate and sanitize user inputs or use parameterized queries (as shown in the examples).
• Use Async/Await or Promises: Replace callbacks with modern JavaScript asynchronous patterns for better readability and error handling.
• Consider ORMs: For complex applications, use Object-Relational Mapping (ORM) tools like Sequelize or TypeORM to simplify database interactions.

Example of connection pooling:

const mysql = require('mysql2');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'testdb',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

module.exports = pool.promise();

This creates a pool of up to 10 connections, using Promises for modern async handling.

10. Conclusion

Connecting Node.js with MySQL enables developers to build powerful, data-driven applications with ease. In this guide, you’ve learned how to:

• Set up a MySQL database and Node.js project.
• Connect Node.js to MySQL using the mysql2 package.
• Perform CRUD operations to manage data.
• Resolve common authentication issues in MySQL 8+.
• Apply best practices for secure and scalable applications.