How to Connect to a MySQL Database Using the mysql2 Package in Node.js?

We will explore how to connect the Node.js application to a MySQL database using the mysql2 package. MySQL can be widely used as a relational database and mysql2 provides fast, secure, and easy access to MySQL servers, it can allow you to handle database queries efficiently in Node.js applications. We will cover all possible approaches for establishing the connection and demonstrate each with step-by-step instructions.

These are the following ways to Connect to a MySQL Database Using the mysql2 Package in NodeJS:

Steps to Connect to a MySQL Database Using the mysql2 Package in Node.js

Step 1: Create the new Node.js project

Open the terminal or command prompt and navigate to the folder where you want to create the project. We can run the following command to initialize the new Node.js project.

mkdir mysql-basic-connection
cd mysql-basic-connection
npm init -y

Project Structure:

Step 2: Install the required dependencies

We can install the mysql2 package and use the following command.

npm install mysql2

Updated dependencies:

{
  "name": "mysql-basic-connection",
  "version": "1.0.0",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "description": "",
  "dependencies": {
    "mysql2": "^3.11.2"
  }
}

Step 3: Start XAMPP

• Open XAMPP Control Panel: Launch the XAMPP Control Panel.
• Start Apache and MySQL: Click on the "Start" buttons for both Apache and MySQL.

Step 4: Access phpMyAdmin

• Open your web browser: Type http://localhost/phpmyadmin in the address bar and press Enter.
• Access phpMyAdmin: This will take you to the phpMyAdmin interface.

Step 5: Create the Database and Table

• Go to the structure and run the following command:

CREATE DATABASE IF NOT EXISTS testdb;

USE testdb;

CREATE TABLE IF NOT EXISTS users (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

INSERT INTO users(name, email) VALUES
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]'),
('Alice Johnson', '[email protected]'),
('Bob Brown', '[email protected]'),
('Charlie Davis', '[email protected]'),
('Eve White', '[email protected]'),
('Frank Black', '[email protected]'),
('Grace Green', '[email protected]'),
('Hank Blue', '[email protected]'),
('Ivy Yellow', '[email protected]');

Basic Connection Approach

The basic connection approach is a simple and direct method to establish the connection to the MySQL database and execute queries. In this approach, we can create the connection, run queries, and then close the connection once the work is done. This method can be suitable for small projects or when you don't need to run multiple database operations simultaneously.

• Create the connection: Use the mysql2.createConnection() function to create the connection object by passing the configuration details host, user, password, and database name.
• Execute a query: Once the connection can be established, use the connection.query() method to execute the SQL queries on the database.
• Handle errors: Any connection errors are passed as the first parameter of the callback function, and should be handled appropriately.
• Close the connection: Once you are done querying the database close the connection using the connection.end() to release resources.

Example: This example shows the implementation of the above-explained approach.

// app.js
const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'mypassword',
  database: 'testdb'
});

// Connect to the database
connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL Database!');
  
  // Example query
  connection.query('SELECT * FROM users', (err, results, fields) => {
    if (err) throw err;
    console.log(results);
  });

  // Close the connection
  connection.end();
});

Step 6: Run the application

we can use the following command to run the application.

node app.js

Output:

Using the Promises

The promise-based approach can involves the basic connection method by making use of the JavaScript promises. This can allows for the more efficient and readable asynchronous code, especially when you have multiple queries to the execute. Instead of using the callbacks, promises can help avoid the nesting and make it easier to handle the success and failure scenarios using the .then() and .catch() or async/await.

• Create the connection: Use the mysql2/promise module to create the connection with the promise-based API.
• Execute queries: Queries are executed using the execute() method, which returns the promise that resolves with the query results.
• Async/await syntax: We can use the async/await syntax to write the code that looks synchronous but works asynchronously, making it easier to the read and manage.

Example: This example shows the implementation of the above-explained approach.

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

async function main() {
  try {
    // Connect to the database using promises
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'mypassword',
      database: 'testdb'
    });
    
    console.log('Connected to MySQL Database!');
    
    // Execute a query using promise
    const [rows, fields] = await connection.execute('SELECT * FROM users');
    console.log('Query Result:', rows);
    
    // Close the connection
    await connection.end();
  } catch (err) {
    console.error('Error:', err);
  }
}

main();

Output:

Using the Connection Pooling

In larger applications, where you need to handle the multiple concurrent database requests efficiently, its better to use the connection pooling. Instead of the creating and closing the connection for the every query and the pool of connections from the pool and returned when the query is finished.

• Create the connection pool: The connection pool is created using the mysql.createpool(). This pool can maintains the multiple connections that can be reused and reducing the overhead of creating and closing connections frequently.
• Efficient connection management: The pool manages the set number of the connections (eg: 10), which can be used by different queries. When the query is run then the pool picks an available connection.
• Execute queries: Queries are executed using the pool.query(). Once the query completes then the connection can be returned to the pool for reuse.

Example: This example shows the implementation of the above-explained approach.

const mysql = require('mysql2');

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

// Query the database using a pooled connection
pool.query('SELECT * FROM users', (err, results, fields) => {
  if (err) throw err;
  console.log(results);
});

Output:

Comparison of the Approaches

Conclusion

The mysql2 package in the Node.js offers the various ways to connect to and query the MySQL database, each suited for the different types of the applications. The basic connection approach works well for the simple applications, while the promise-based approach can be ideal for the cleaner and more manageable asynchronous code. For the larger and more scalable applications, connection pooling offers the better performance by reusing connections. By choosing the right approach based on the application's needs, we can ensure and effective interaction with the MySQL database.