NodeJS tutorial to Add, Edit and Delete Record Using MySQL

This is the next part of nodejs tutorial Hello World with Node.js.This node.js tutorial help to create a connection with MySQL and provide the functionality to add, edit and delete record from the MySQL database table.

MySQL is a very popular open-source database. I will demonstrate how to connect MySQL with nodejs project and use it. I am assuming you have read My Previous nodejs tutorial, That help to understand the use of package.json file and run nodejs application from the command line.

Let’s start MySQL integration with nodejs project using mysql package. Please take a look at files structure that we need to use in this tutorial.

main.js : This file is responsible for nodejs app creation and mysql connection.
node_modules : This folder will contain all dependency libraries.
package.json : This file will contain all dependency libraries information that we need to download into node_modules folder.

Checkout Other NodeJS tutorials,

Simple example of Node.js With MySQL

I will let you know the basics of MySQL and the use of MySQL with nodejs project. You can select records, add records into the database and delete records from the MySQL database using sql query. I will cover following points in this nodejs example tutorial:

Define mysql dependency into package.json file.
MySQL Connection creation with node.js.
Add Record into MySQL with node.js.
Edit Record into MySQL using node.js.
Delete Record from MySQL database with node.js using express.

Step 1: We will create a new folder node-mysql and change into that directory.

$ mkdir node-mysql
$ cd node-mysql

Step 2: We will create package.json file for this node.js application.

~/node-mysql$  npm init

Above command creates a package.json file into your nodejs project folder and help you manage dependencies nodejs libraries that we will install later on in the tutorial.

Step 3: We will install MySQL into this node.js project.

~/node-mysql$  npm install mysql --save

--save params will store MySQL module as a dependency into your package.json file. You can see an entry has been created into your package.json file by using the above command.

So now pcakage.json file look like the below,

{
  "name": "node-mysql",
  "version": "1.0.0",
  "description": "",
  "main": "main.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": {
    "name": "Adam",
    "email": "[email protected]",
    "url": "http://js-tutorials.com/"
  },
  "license": "MIT",
  "dependencies": {
    "mysql": "^2.13.0"
  }
}

Once you have MySQL installed into the project, Now you have to do, Connect your MySQL database with the project.

Step 5: Created main.js file and added the below JavaScript code to it.

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost', //mysql database name
  user     : 'root', //mysql database username
  password : '', //mysql database password
  database : 'dummy_db' //mysql database name
});

connection.connect();

connection.connect(function(err) {
  if (err) throw err
  console.log('You are now connected...')
})

in the above code, We have used 'dummy_db' for the sample database, You can change the database name as per your requirement. We will create a 'employee' table in the 'dummy_db' database. Please run the below MySQL query into the SQL query box.

CREATE TABLE IF NOT EXISTS `employee` (
`id` int(11) NOT NULL COMMENT 'primary key',
  `employee_name` varchar(255) NOT NULL COMMENT 'employee name',
  `employee_salary` double NOT NULL COMMENT 'employee salary',
  `employee_age` int(11) NOT NULL COMMENT 'employee age'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='datatable demo table';

Step 5: Now save your main.js file and run nodejs app using below command:

~/node-mysql$ node main.js

You should see 'You are now connected' at the terminal.

Insert Record into Database using MySQL and nodejs

Insert record is a basic operation of application that is used to insert a new record into mysql database. I will add the following code into main.js file.

//to insert record into mysql
connection.query('INSERT INTO `employee` (`employee_name`, `employee_salary`, `employee_age`) VALUES ("Adam", 2000 , 30)', function (error, results, fields) {
  if (error) throw error;
  console.log('The response is: ', results);
});

Update Record into Database using MySQL and nodejs

We will update the record into MySQL database using the below code, to update a record, we need 'id' to tell MySQL to whom record we are updating.

//to update record into mysql
connection.query('UPDATE `employee` SET `employee_name`="William",`employee_salary`=2500,`employee_age`=32 where `id`=1', function (error, results, fields) {
  if (error) throw error;
  console.log('The response is: ', results);
});

How to fetch all records from MySQL with nodejs

We will create a mysql query that use to fetch all employee table data from database. You need to add the below code into main.js file.

//featch records from mysql database
connection.query('select * from employee', function (error, results, fields) {
  if (error) throw error;
  console.log('The response is: ', results);
});

How to Delete records from MySQL Database with nodejs

We will fire a delete query to remove the record from the MySQL database table. You need to add the below code into main.js file.

//delete record from mysql database
connection.query('delete from employee where id=1', function (error, results, fields) {
  if (error) throw error;
  console.log('The response is: ', results);
});

The final main.js file code is :

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


connection.connect(function(err) {
  if (err) throw err
  console.log('You are now connected...')
})

//to insert record into mysql
/*connection.query('INSERT INTO `employee` (`employee_name`, `employee_salary`, `employee_age`) VALUES ("Adam", 2000 , 30)', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});*/

//to update record into mysql
/*connection.query('UPDATE `employee` SET `employee_name`="William",`employee_salary`=2500,`employee_age`=32 where `id`=1', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});*/

//featch records from mysql database
connection.query('select * from employee', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});
//delete record from mysql database
/*connection.query('delete from employee where id=1', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results);
});*/

//end connection
connection.end();

Conclusion

This is simple node.js project which will use MySQL module as a dependency. I have demonstrated add a MySQL connection with nodejs and adding a record into the database, update a record into the mysql database, fetching all records from mysql database, delete record from the MySQL database table.I hope you have enjoyed this nodejs tutorial.

You can download the source code and Demo from the below link.

Download Source Code