Node.js with MySQL Examples

Node.js is a popular programming language like PHP & JAVA for web applications. Also, MySQL is the most popular database used for storing values. MySQL database driver for Node.js is available under the NPM repository. In this tutorial you will learn to how to connect MySQL database using node.js and do INSERT, UPDATE, SELECT and DELETE operations on MySQL database table.

Step 1 – Create MySQL Database and Table

First, create a database and user in MySQL for your applications. For testing purpose, we are creating mydb database and use it.

mysql> CREATE DATABASE mydb;
mysql> USE mydb;

Now create an example table for doing insert, update and delete operations.

Also create a mysql user for accessing database from application.

mysql> GRANT ALL on mydb.* to 'myusr'@'localhost' identified by '123456';
mysql> FLUSH PRIVILEGES;

2. Install Node.js MySQL Module

MySQL driver for node.js is available under the node package manager (NPM). Use the following command to install it.

sudo npm install mysql

3. Simple App to Connect MySQL

Below is a sample node.js program which will connect node.js application with MySQL server. It will show success and error messages according to connections results and close the connection at the end of the program. Create a JavaScript file app.js.

cd myApp
vim app.js

and add the following content to above file.

Now execute the above script using nodejs and make sure that the database is connecting properly or not.

node app.js

Database connection successful

3. Insert Data in MySQL Table

Now add the code in above script to insert data in users table in mydb database.

var mysql  = require('mysql');
var dbconn = mysql.createConnection({
  host     : 'localhost',
  user     : '<DB_USER>',
  password : '<DB_PASSWORD>',
  database : '<DB_NAME>'
});

dbconn.connect(function(err){
  if(err){
    console.log('Database connection error');
  }else{
    console.log('Database connection successful');
  }
});


var record= { firstname: 'Rahul', lastname: 'Kumar', email: '[email protected]' };

dbconn.query('INSERT INTO users SET ?', record, function(err,res){
  if(err) throw err;

  console.log('Last record insert id:', res.insertId);
});

dbconn.end(function(err) {
  // Function to close database connection
}

Now execute app.js script using the command line and make sure that data is properly inserted in the database. Repeat this step a few more times with updating values in the above script.

node app.js

Database connection successful
Last record insert id: 5

Also, check the record added in users table under the mydb database using the mysql command line.

mysql> select * from users;

+----+-----------+----------+----------------+---------------------+
| id | firstname | lastname | email          | creation_date       |
+----+-----------+----------+----------------+---------------------+
|  5 | Rahul     | Kumar    | [email protected] | 2016-01-22 17:16:19 |
|  6 | John      | Smith    | [email protected] | 2016-01-22 17:16:55 |
+----+-----------+----------+----------------+---------------------+
2 row in set (0.01 sec)

4. Select Data from MySQL Table

Now edit app.js script and add the following code. This will fetch all records from users table in mydb database.

dbconn.query('SELECT * FROM users',function(err, records){
  if(err) throw err;

  console.log('Data received from Db:n');
  console.log(records);
});

or you can customize search by selection values basis of specific conditions.

dbconn.query('SELECT * FROM users WHERE id = ?' [5] ,function(err, records){
  if(err) throw err;

  console.log('Data received from Db:n');
  console.log(records);
});

5. Update Data from MySQL Table

Now if you required to update existing data in MySQL table, use the following code.

dbconn.query('SELECT users SET email = ? WHERE id = ?', ['[email protected]', 6], function(err, result){
  if(err) throw err;

  console.log('Record Updated ' + result.changedRows + ' rows');
});

Now check values in database table.

mysql> select * from users;

+----+-----------+----------+----------------+---------------------+
| id | firstname | lastname | email          | creation_date       |
+----+-----------+----------+----------------+---------------------+
|  5 | Rahul     | Kumar    | [email protected] | 2016-01-22 17:16:19 |
|  6 | John      | Smith    | [email protected] | 2016-01-22 17:16:55 |
+----+-----------+----------+----------------+---------------------+
2 row in set (0.01 sec)

6. Delete Data from MySQL Table

Now if you delete some record from the table, add following code in your node.js application code.

dbconn.query('DELETE FROM users WHERE id = ?', [6], function(err, result){
  if(err) throw err;

  console.log('Record Updated ' + result.affectedRows + ' rows');
});

Now check values in database table.

mysql> select * from users;

+----+-----------+----------+----------------+---------------------+
| id | firstname | lastname | email          | creation_date       |
+----+-----------+----------+----------------+---------------------+
|  5 | Rahul     | Kumar    | [email protected] | 2016-01-22 17:16:19 |
+----+-----------+----------+----------------+---------------------+
2 row in set (0.01 sec)