How to connect mysql database in node js with crud query example?

Leave a Comment / php / By

How to connect mysql database in node js with crud query example?

In this post we will give you information about How to connect mysql database in node js with crud query example?. Hear we will give you detail about How to connect mysql database in node js with crud query example?And how to use it also give you demo for it if it is necessary.

In this tutorial, I will tell you how to create connection with MySQL database in Node.js.

To start with MySQL in Node.js you need to install node-mysql module via npm and then load MySQL module via require.

You can perform CRUD operations easily by using node-mysql driver in Node.js because it provides all most all connection and query from MySQL.


Install MySQL node.js driver

Open your command prompt and run following code :

npm install mysql


Create connection

After successfully running above command we create connection with MySQL database.

  1. var mysql =require('mysql');
  2. var connection = mysql.createConnection({
  3.     host     :'localhost',
  4.       user     :'root',
  5.      password :'',
  6.       database :'test'
  7. });
  8. connection.connect(function(err){
  9.     if(err){
  10.         console.error('error connecting: '+ err.stack);
  11.         return;
  12.     }
  13.     console.log('connected as id '+ connection.threadId);
  14. });
var mysql = require('mysql'); 
var connection = mysql.createConnection({ 
	host     : 'localhost', 
  	user     : 'root', 
 	password : '', 
  	database : 'test' 
}); 
connection.connect(function(err) { 
	if (err) { 
		console.error('error connecting: ' + err.stack); 
		return; 
	} 
	console.log('connected as id ' + connection.threadId); 
});


You will get connection id if successfully connected with database.



Terminating connection

You can close connection by two different ways :

  • end() method

  • destroy() method

end() method determine that all queries are executed before closing the connection request to the mysql server.

connection.end();

destroy() method terminate the connection immediately and after destroy() method, no any events will be triggered for the connection.

connection.destroy();

end() method takes a callback argument but destroy() method does not.



Reading Database Table Records

Now I am going to run select query to get all records from table in Node.js app.

First create a database ‘test’ and create a table ‘users’ within test database.

Now create a new file main.js and put following code and save it :

  1. var mysql      =require('mysql');
  3. var connection = mysql.createConnection({
  4.   host     :'localhost',
  5.   user     :'root',
  6.   password :'',
  7.   database :'test'
  8. });
  10. connection.connect();
  12. connection.query('SELECT * FROM users',function(err, rows, fields)
  13. {
  14. if(err)throw err;
  16.   console.log(rows);
  17. });
  19. connection.end();
var mysql      = require('mysql'); 

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

connection.connect(); 

connection.query('SELECT * FROM users', function(err, rows, fields)  
{ 
  if (err) throw err; 

  console.log(rows); 
}); 

connection.end();



Now you have to launch the server by following command:


node main.js

Output :



Insert New Record in Table in Node.js

  1. var mysql      =require('mysql');
  3. var connection = mysql.createConnection({
  4.   host     :'localhost',
  5.   user     :'root',
  6.   password :'',
  7.   database :'test'
  8. });
  10. connection.connect();
  12. var user ={ email:'Aj', password:'123456'};
  13. connection.query('INSERT INTO users SET ?', user,function(err,res){
  14. if(err)throw err;
  16.   console.log('Last insert ID:', res.insertId);
  17. });
  19. connection.end();
var mysql      = require('mysql'); 

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

connection.connect(); 

var user = { email: 'Aj', password: '123456' };
connection.query('INSERT INTO users SET ?', user, function(err,res){
  if(err) throw err;

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

connection.end();

You will get last inserted id by running above code.


Update Row

Now i update the record similarly when i run update query then we will get the affected number of rows.

  1. var mysql      =require('mysql');
  3. var connection = mysql.createConnection({
  4.   host     :'localhost',
  5.   user     :'root',
  6.   password :'',
  7.   database :'test'
  8. });
  10. connection.connect();
  12. connection.query(
  13. 'UPDATE users SET email = ? Where id = ?',
  14. ["aj@demo.com",2],
  15. function(err, result){
  16. if(err)throw err;
  18.     console.log('Updated '+ result.changedRows +' rows');
  19. }
  20. );
  21. connection.end();
var mysql      = require('mysql'); 

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

connection.connect(); 

connection.query(
  'UPDATE users SET email = ? Where id = ?',
  ["aj@demo.com", 2],
  function (err, result) {
    if (err) throw err;

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


Delete Record from Table

Similarly run delete query to delete record from table.

  1. var mysql      =require('mysql');
  3. var connection = mysql.createConnection({
  4.   host     :'localhost',
  5.   user     :'root',
  6.   password :'',
  7.   database :'test'
  8. });
  10. connection.connect();
  12. connection.query(
  13. 'DELETE FROM users WHERE id = ?',
  14. [2],
  15. function(err, result){
  16. if(err)throw err;
  18.     console.log('Deleted '+ result.affectedRows +' rows');
  19. }
  20. );
  21. connection.end();
var mysql      = require('mysql'); 

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

connection.connect(); 

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

    console.log('Deleted ' + result.affectedRows + ' rows');
  }
);
connection.end();
Now you know how to perform simple crud query in Node.js and MySQL. You can run nested query in same way such join two tables to get records.

Label :

MySQL

How To

Web Development

Node.js

Database

Hope this code and post will helped you for implement How to connect mysql database in node js with crud query example?. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs

For More Info See :: laravel And github

Leave a Comment

Your email address will not be published. Required fields are marked *

  +  16  =  24

We're accepting well-written guest posts and this is a great opportunity to collaborate : Contact US