Node.js » Connecting MySQL

Almost every back end application needs to interact with a relational database to persist data. Learning node would not be complete if you do not know how to enable your node application to save data into a database.
Since MySQL is the most commonly used database in enterprise applications, this tutorial will teach you how to connect a node application to MySQL, save, retrieve, update and delete data.
This tutorial assumes that you have MySQL installed on your system. If not, download it from here.
Installing mysql module
For connecting a node application to MySQL database, you need to install mysql module. Open command prompt on Windows or terminal on Mac/Linux systems. Navigate to the application folder and type
npm install mysql
This command will install mysql package and its output will be

Before running this command, check that you have package.json file in the folder. If you do not have it, then run command

It will create package.json file in the current folder.
Connecting to MySQL database
For starting interaction with a database, you need to establish connection to it. Connecting to MySQL database from a node application, first import mysql module using require function.
Once the module is imported, its createConnection method can be used to connect to a database. This method takes an object of key-value pairs as argument where keys are the attributes required to connect to a database such as host, port, username, password etc., with their values provided as per the MySQL database configuration.
createConnection returns an object whose connect method is used to connect to the database. connect method accepts a callback function as argument. Example,

// import mysql module
const mysql = require('mysql');
// get connection object
const connection = mysql.createConnection({
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'root'
});

// connect to database
connection.connect(function(err) {
    if(err) {
        console.log('Unable to connect', err);
    } else {
        console.log('Connected!!!');
    }
})

Above code when executed prints Connected!!!

Note that the callback function takes a single argument which is populated with the error encountered while connecting to the database. This object can be used to determine the reason when a connection can not be made. Example, if you deliberately provide a wrong password, then above code will print

Callback function can also be written using arrow function introduced in ES6 as below.

connection.connect((err) => {
    if(err) {
        console.log('Unable to connect', err);
    } else {
        console.log('Connected!!!');
    }
})

Closing connection
Above program when executed will keep on running till you press Ctrl-C at the command prompt(or terminal) or close the database connection that was created.
There are two ways to close the database connection.
1. end method
Object returned by createConnection method has an end method which is used to close the database connection. This method takes a callback function as argument and is used to report any errors while closing connection. Also, this method executes all pending database queries before closing the connection. Example,

connection.connect(function(err){
    if(err){
        console.log('Unable to connect', err);
    } else{
        console.log('Connected!!!');
    }
});
// close connection
connection.end(function(err) {
   console.log(err);
});

2. destroy method
Connection object has a destroy method which can also be used to close database connection. This method does not execute any pending queries and closes the connection immediately. This method does not take any callback function as argument unlike end method. Example,

connection.connect(function(err){
    if(err){
        console.log('Unable to connect', err);
    } else{
        console.log('Connected!!!');
    }
});
// close connection
connection.destroy();