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 driverOpen your command prompt and run following code :
npm install mysqlCreate connection
After successfully running above command we create connection with MySQL database.
- 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 connectionYou 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 RecordsNow 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 :
- 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 :
- 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 RowNow i update the record similarly when i run update query then we will get the affected number of rows.
- 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();
Similarly run delete query to delete record from table.
- 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();
