Connect MySQL with Nodejs – Simple Guide

In this tutorial, we will learn how to connect to a MySQL Database server using Nodejs. This guide is designed to be super simple for beginners!

Installing Nodejs driver

First of all, we will create a folder called Databases where the Nodejs app will be stored. After this use the below command to load package.json file.

npm initCode language: Bash (bash)

Now we will install Nodejs for MySQL using the below command-

npm install mysqlCode language: Bash (bash)

Now inside the folder Databases create a javascript file named connection.js where we will be writing the code which will connect to the MySQL server.

Before that, open the MySQL Client or MySQL Workbench to create a database named app. The code for it is-

Create Database app;Code language: SQL (Structured Query Language) (sql)

The database is created and now we are set to connect the MySQL server from the Nodejs application.

Connecting to a MySQL Database from Nodejs

Here we will write the code for connecting the server from Nodejs. First of all, we will import mysql –

let mysql = require('mysql');Code language: SQL (Structured Query Language) (sql)

Now let us create a connection to the MySQL database server to connect to the Nodejs application. For this, we will call createConnection() method. In this code, we will provide all the necessary information such as host, password, user, and database name to which the connection is to be made.

let connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '******',
    database: 'app'
});Code language: SQL (Structured Query Language) (sql)

Here we will connect to the database app by making a connection. Now we will call connect() method on the connection object to connect to the MySQL database server. The code for this is:

connection.connect(function(err) {
  if (err) {
    return console.error('error: ' + err.message);
  }

  console.log('Connected to the MySQL server.');
});Code language: SQL (Structured Query Language) (sql)

Now we will test this connection.js program by running the following code on MySQL Client-

node connection.jsCode language: CSS (css)

The output for the following is –

Here, we see the message Connected to the MySQL server. This means that the connection is successful. If the database did not exist, an error would appear on the screen saying unknown database.

Closing the MySQL-Nodejs database connection

We have connected the database server to the Nodejs application and now we want to close it. For this, we will use the end() method on the connection object. The code for the following is-

connection.end(function(err) {
  if (err) {
    return console.log('error:' + err.message);
  }
  console.log('Close the database connection.');
});Code language: SQL (Structured Query Language) (sql)

The end() method makes sure that all the queries are executed before the connection is closed.

The other way to close a connection is to use the destroy() method. It guarantees no more queries or callbacks will be triggered.

connection.destroy();Code language: SQL (Structured Query Language) (sql)

There are no arguments passed in the destroy() method for closing the connections.

Pooling Connections

If we want to create a connection pool with 4 connections then the code for it is-

var pool = mysql.createPool({
    connectionLimit: 4,
    host: 'localhost',
    user: 'root',
    password: '*******', 
    database: 'app'
});Code language: SQL (Structured Query Language) (sql)

• To get a connection from the pool, we will use getConnection() method.
• To return a connection to the pool, call the method connection.release() method.
• To close all the connections in the pool call the end() method.

Conclusion

In this tutorial, we learned how to connect to a MySQL database server from Nodejs Application.