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

Last Updated : 19 Sep, 2024

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:

Table of Content

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.

JavaScript

// 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.

JavaScript

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.

JavaScript

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

Approach	Best Use Case	Advantages	Disadvantages
Basic Connection	Simple applications with the limited queries.	Simple to implement the direct interaction.	Inefficient for the frequent queries and connection overhead.
Promises	Medium applications needing the multiple queries.	Clean async flow, better error handling with promises.	Requires the understanding of the promises or async/await.
Connection Pooling	Large scale applications with high concurrency	Efficient performance, connection reuse, scalability.	More complex setup, needs the careful tuning of the pool limits.

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.

How to Connect SQLite3 Database using Node.js ?

geekforgs9hp

Improve

Article Tags :

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

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

Step 1: Create the new Node.js project

Project Structure:

Step 2: Install the required dependencies

Updated dependencies:

Step 3: Start XAMPP

Step 4: Access phpMyAdmin

Step 5: Create the Database and Table

Basic Connection Approach

Step 6: Run the application

Using the Promises

Using the Connection Pooling

Comparison of the Approaches

Conclusion

Similar Reads