Most real-world applications persist data in databases. So it’s important to know how to connect your application to a database and fetch data from it.

MySQL is an excellent choice for a database. It's easy to use, has widespread support, and is one of the most popular relational databases.

Before You Begin

Since you’ll be testing out the connection code on a local MySQL server, you’ll need to install a MySQL server first. Once you’ve done so, create an empty database and make sure you have the username and password to hand.

Create a Node Application

In your terminal, run the following command to create a new folder for your node application:

        mkdir node-mysql

Navigate to the node-mysql folder and initialize it using npm:

        cd node-mysql
npm init -y

This will generate a package.json file which, amongst other configuration, will hold the dependencies of your project.

Connect Node to MySQL

There are several ways you can connect your node application to MySQL. The mysql2 package is an excellent choice, which you can install with a command like this:

        npm install mysql2

To make the connection, you need several values namely the host, port, user, database, and password. Here is an example that connects to a database called nodedb on a MySQL server installed locally.

        const config = {
    host: "localhost",
    port: 3306,
    database: "nodedb",
    user: "root",
    password: "********",
}

This is the config object you'll pass to the connection method as in the program below.

        const mysql = require("mysql2")
 
const config = {
    host: "localhost",
    database: "db_name",
    user: "mysql_username",
    password: "mysql_password",
    connectionLimit: 100,
}
 
const connection = mysql.createConnection(config)
 
connection.connect(function(err) {
    if (err) throw(err);
    console.log("Connected!");
});

When you pass the database details to the mysql.createConnection() method, you create a connection object. The connection object will let you perform transactions like creating, deleting, or reading tables.

For example, the following code creates a table in the database you connect to.

        connection.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");
 
    let createUser = `CREATE TABLE users (user_id INT(100), user_name VARCHAR(255), email VARCHAR(255))`;
 
    connection.query(createUser, function(err, results, fields) {
        if (err) {
            console.log(err.message);
        }
    });
});

When you run it, this query will create a new table called users in the database. The query uses common syntax that you can adapt to create a MySQL database schema that suits your needs.

The connection program above works fine if you only want to make a single connection. However, when making concurrent connection requests, it is best to use a connection pool.

Connect Node to MySQL Using a Connection Pool

Opening and closing connections can become expensive, especially when there are many. Database connection pooling helps reduce this cost by maintaining a pool of open connections. When a request is made, the pool can provide an open connection on demand.

By doing this, you save time because you do not have to open a new connection every time.

Use the following code to create a connection pool in Node:

        const mysql = require("mysql2");
  
const config = {
    host: "localhost",
    database: "db_name",
    user: "mysql_username",
    password: "mysql_password",
    connectionLimit: 100,
}
 
const pool = mysql.createPool(config);
 
pool.query("SELECT * FROM some_table", (err, data) => {
    if (err) throw(err)
    console.log(data);
});

Remember to change the configuration details to match your own environment.

Securely Connecting to MySQL

The mysql2 library simplifies the process of connecting a Node application to a MySQL database. You can use either a single client or a connection pool. However, If you have to make multiple requests, it is best to use a connection pool since it is less costly.

Connecting to the database is only the first step. You should also ensure that the data you store in the database is secure. After all, MySQL is a popular target for attackers. There are some basic steps you can take to secure your MySQL database server. Set a strong user password, restrict remote logins, and don’t grant unnecessary privileges.