Node.js with MySQL Examples

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

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 a example tables for doing insert, update and delete operations.

mysql> CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  firstname varchar(30),
  lastname varchar(30),
  email VARCHAR(50),
  creation_date TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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 node package manager (NPM). Use the following command to install it.

$ sudo npm install mysql

3. Simple App to Connect MySQL

Below is 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 program. Create a JavaScript file app.js.

$ cd myApp
$ vim app.js

and add the following content to above file.

var mysql  = require('mysql');
var dbconn = mysql.createConnection({
  host     : 'localhost',
  user     : 'myusr',
  password : '123456',
  database : 'mydb'
});

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

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

Now execute above script using nodejs and make sure that 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 command line and make sure that data is properly inserted in database. Repeat this step few more times with updating values in above script.

$ node app.js

Database connection successful
Last record insert id: 5

Also check the record added in users table under mydb database using 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 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 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)