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

E:\node>npm install mysql
npm notice created a lockfile as package-lock.json. You should commit this file.
npm WARN node@1.0.0 No description
npm WARN node@1.0.0 No repository field.+ mysql@2.16.0
added 11 packages in 2.169s

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

npm init – -yes

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!!!

Before running the program, ensure that MySQL is running at the provided location.

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

E:\node>node firstprogram.js
Unable to connect { Error: ER_ACCESS_DENIED_ERROR: Access denied for user ‘root’@’localhost’ (using password: YES)
at Handshake.Sequence._packetToError (E:\node\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
—–

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();

 

0

Share your thoughts !!

Close Menu

Never Miss an article !

Get the new post delivered straight into your inbox, enter your email and hit the button

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

codippa will use the information you provide on this form to be in touch with you and to provide updates and marketing.