NodeJS MySQL Tutorial

One of the main advantages of using the NodeJS framework is the easy integration with the Database libraries.

MySQL is one of the most popular database. NPM MySQL library provides easy integration with NodeJS application.

In this tutorial, we’ll learn following operations from NodeJS application:

1.     Installing MySQL NPM module
2.     Connect to MySQL database
3.     Execute select query from MySQL database
4.     Execute Insert query
5.     Execute Update Query
6.     Delete Query
7.     Close Connection with MySQL database

Installing MySQL NPM module

For installing MySQL module, type below command in the terminal.

npm install mysql

This will add library reference in the dependencies section in your NodeJS project package.json file.

Connection NodeJS to MySQL Database

For establishing connection to database, use below code:

var mysql = require('mysql'); 
var connection = mysql.createConnection({ 
    host: 'localhost', 
    user: 'myuser', 
    password: 'mypassword', 
    database: 'dotnetpattern' 
}); 

connection.connect();

In this first line, we are importing the MySQL module. In this third line, we are establishing a connection with MySQL instance. We have provided server name in the host property, username, password and the database name.

Select Query

For fetching the results from the query, we need to use “query” method from the connection object. Below is the code:

connection.query("SELECT * FROM Employee", function (error, results) { 
    if (error) 
        throw error; 
    console.log(results); 
});

In the first parameter of query method, we can write our select query and in the second parameter we can pass a callback method.

In the callback method, first method is the error object, second is the results object in which we will get the results.

Below is the result of above query.

[ 
    RowDataPacket { id: 1, name: 'George' }, 
    RowDataPacket { id: 2, name: 'Branda' } 
]

Insert into MySQL Database

For inserting records, we can use same query method.

connection.query("INSERT INTO Employee VALUES ('3', 'Brandon')", function(error, results) 
{ 
    if (error) 
        throw error; 

    console.log(results); 
})

If the result, we got a dictionary object. In the results, we can check affectedRows property to check whether any rows are inserted or not.

OkPacket 
{ 
    fieldCount: 0, 
    affectedRows: 1, 
    insertId: 0, 
    serverStatus: 2, 
    warningCount: 0, 
    message: '', 
    protocol41: true, 
    changedRows: 0 
}

Update Query

Query method is also used for updating the records. Below is the code.

connection.query("UPDATE Employee SET name='Charles' WHERE id=1", function(error, result) { 
    if (error) 
        throw error; 
    console.log(result.affectedRows + " records updated"); 
});

In the result object, we got the affectedRows property which can be used for checking how many records are updated with the update query.

Delete Query

Below is the code for deleting records.

connection.query("DELETE FROM Employee WHERE id=2", function(error, result) { 
    if (error) 
        throw error; 
    console.log(result.affectedRows + " records updated"); 
});

Close Connection

For closing the connection, we can use end() method of connection object from MySQL module. Below is the code:

connection.end();

---

This is the end of 5 minute tutorial of using MySQL into the NodeJS application. Please comments if you like this post.