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
ataşehir escort maltepe escort bostancı escort kadıköy escort maltepe escort ataşehir escort tuzla escort pendik escort ümraniye escort kartal escort
pendik escort
izmir escort izmir escort izmir escort izmir escort denizli escort antalya escort antalya escort antalya escort izmir escort izmir escort izmir escort izmir escort izmir escort
hatay escort nevsehir escort nigde escort kirikkale escort batman escort osmaniye escort kirklareli escort tokat escort rize escort adiyaman escort gumushane escort batman escort
porno izle
cialis satış cialis fiyat cialis 20 mg cialis 100 mg cialis 5 mg fiyat
sohbet okey oyna
ankara escort marmaris escort bursa escort mersin escort antalya escort izmir escort samsun escort bodrum escort çeşme escort kuşadası escort adana escort eskişehir escort gaziantep escort esenyurt escort pendik escort tuzla escort fatih escort ümraniye escort mecidiyeköy escort kartal escort bahçelievler escort bahçeşehir escort ataşehir escort bakırköy escort nişantaşı escort avcılar escort büyükçekmece escort kurtkoy escort konya escort beşiktaş escort taksim escort beylikdüzü escort malatya escort sakarya escort şişli escort maltepe escort kadıköy escort ataköy escort bostancı escort şirinevler escort bağcılar escort üsküdar escort etiler escort başaksehir escort ortaköy escort kağıthane escort sultangazi escort halkalı escort çekmeköy escort şaşkınbakkal escort mamak escort sincan escort keçiören escort çankaya escort beypazarı escort akyurt escort pursaklar escort kahramankazan escort göztepe escort alsancak escort bornova escort buca escort karabaglar escort karşıyaka escort konak escort narlıdere escort izmit escort başiskele escort kandira escort körfez escort karamürsel escort dilovası escort gölcük escort gebze escort kocaeli escort kayseri escort elazığ escort denizli escort diyarbakir escort adana escort ankara escort izmir escort antalya escort gaziantep escort kayseri escort konya escort mersin escort denizli escort kocaeli escort diyarbakır escort