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

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

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. });

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');
  2. var connection = mysql.createConnection({
  3. host : 'localhost',
  4. user : 'root',
  5. password : '',
  6. database : 'test'
  7. });
  8. connection.connect();
  9. connection.query('SELECT * FROM users', function(err, rows, fields)
  10. {
  11. if (err) throw err;
  12. console.log(rows);
  13. });
  14. connection.end();

Now you have to launch the server by following command:

node main.js

Output :

Reading Database Table Records in node.js

Insert New Record in Table in Node.js
  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();
  9. var user = { email: 'Aj', password: '123456' };
  10. connection.query('INSERT INTO users SET ?', user, function(err,res){
  11. if(err) throw err;
  12. console.log('Last insert ID:', res.insertId);
  13. });
  14. 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');
  2. var connection = mysql.createConnection({
  3. host : 'localhost',
  4. user : 'root',
  5. password : '',
  6. database : 'test'
  7. });
  8. connection.connect();
  9. connection.query(
  10. 'UPDATE users SET email = ? Where id = ?',
  11. ["aj@demo.com", 2],
  12. function (err, result) {
  13. if (err) throw err;
  14. console.log('Updated ' + result.changedRows + ' rows');
  15. }
  16. );
  17. connection.end();
Delete Record from Table

Similarly run delete query to delete record from table.

  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();
  9. connection.query(
  10. 'DELETE FROM users WHERE id = ?',
  11. [2],
  12. function (err, result) {
  13. if (err) throw err;
  14. console.log('Deleted ' + result.affectedRows + ' rows');
  15. }
  16. );
  17. connection.end();

Phone: (+91) 8800417876
Noida, 201301
Betwinner
pendik escort
kartal escort