30 days of node | Day 17 | CRUD operation in MySQL using node.js | Nodejsera

30 Days of node
Day 17 : CRUD in MySQL using node.js

30 days of node - Nodejs tutorial series

What is MySQL ?

MySQL is world's leading relational database management system based on SQL ( Structured query language ). It is written in C and C++ and is managed by MySQL AB which is a sweden based company. MySQL stores data in the form of rows and columns within a table.

Features of MySQL

Features of MySQL includes :

Cross platform support.
Query caching
SSL support
Triggers
Cursors

Note that We are naming only few from the large list of features available in MySQL. All these features are explained in the respective tutorial of MySQL.

Advantages of MySQL

Advantages of MySQL includes :

It is released under open-source license.
Easy to use
It uses the commonly known SQL to perform operations.
It is fast and can handle large datasets very easily.
It is customizable so the programmer can mould it as per their requirements.

Prerequisites

MySQL : You can download it here .
Node.js : You can download it here .
NPM : It is already installed on your system when you installed node.js

mysql (npm package) : We can install it using the command given below :

											
>npm install mysql

Establishing connection

The first step is to establish a connection between the mysql database and our node.js application.

								
var mysql = require('mysql');

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

Creating a Table in MySQL using node.js

Let's start by creating a table in mysql database using node.js. The snippet for the following is given below :

								
// Name of the file is  : create-table-mysql.js

var mysql = require('mysql');

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

var table = "CREATE TABLE details (id int(15) NOT NULL AUTO_INCREMENT,"+
    "name varchar(30) DEFAULT NULL,"+
    "age float(15) DEFAULT NULL,"+
    "PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1";

//establishing connection
connect.getConnection(function(err, connection){    
  //Creating details table
  connection.query(table,  function(err){
    if(err) throw err;
    else {
        console.log('Table created Successfully!');
    }
  });

//releasing connection
 connection.release();

});

We can run the above code using the following command :

								
>node create-table-mysql.js
Table created Successfully!

Create Operation in MySQL using node.js

Snippet for insert/create operation in MySQL using node.js is as follows :

								
//Name of the file : insert-mysql.js
var mysql = require('mysql');

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


var insert_R = 'INSERT INTO details(name,age) VALUE(?,?)';
//establishing connection
connect.getConnection(function(err, connection){
    
  //Inserting a record into details
   connection.query(insert_R,['rejii',24], function(err,res){
    if(err) throw err;
    else {
        console.log('Details added successfully');
    }
  });

//releasing connection
 connection.release();

});

We can run the above code using the following command :

								
>node insert-mysql.js
Details added successfully

Read Operation in MySQL using node.js

Snippet for read operation in MySQL using node.js is as follows :

								
//name of the file : read-mysql.js
var mysql = require('mysql');

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


var read_R = 'SELECT * FROM details';
//establishing connection
connect.getConnection(function(err, connection){
    
  //retrieving a record from details
   connection.query(read_R, function(err, data){
    if(err) throw err;
    else {
        console.log(data);
    }
  });

//releasing connection
 connection.release();

});

We can run the above code using the following command :

								
>node read-mysql.js
[ RowDataPacket { id: 1, name: 'rejii', age: 24 },
  RowDataPacket { id: 2, name: 'angie', age: 47 },
  RowDataPacket { id: 3, name: 'alex', age: 27 } ]

Update Operation in MySQL using node.js

Snippet for update operation in MySQL using node.js is as follows :

								
//name of the file : update-mysql.js
var mysql = require('mysql');

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


var update_R = 'UPDATE details SET age = ? WHERE name=?';
//establishing connection
connect.getConnection(function(err, connection){
    
  //Updating a record from details
  connection.query(update_R,[25,'regii'], function(err, res){
    if(err) throw err;
    else {
        console.log('Updated the age of regii !');
    }
  });

//releasing connection
 connection.release();

});

We can run the above code using the following command :

								
>node update-mysql.js
Updated the age of regii !

Delete Operation in MySQL using node.js

Snippet for delete operation in MySQL using node.js is as follows :

								
//name of the file : delete-mysql.js
var mysql = require('mysql');

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


var delete_R = 'DELETE FROM details WHERE name=?';
//establishing connection
connect.getConnection(function(err, connection){
    
  //Deleting a record from details
  connection.query(delete_R,['regii'], function(err, res){
    if(err) throw err;
    else {
        console.log('A record is removed !');
    }
  });

//releasing connection
 connection.release();

});

We can run the above code using the following command :

								
>node delete-mysql.js
A record is removed !

Drop a Table from MySQL using node.js

Snippet for performing drop operation on a table in MySQL using node.js is as follows :

								
//name of the file : drop-table-mysql.js
var mysql = require('mysql');

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


var drop_T = 'DROP table details';
//establishing connection
connect.getConnection(function(err, connection){
    
  //Drop the details table
  connection.query(drop_T, function(err, res){
    if(err) throw err;
    else {
        console.log('The details table is removed successfully');
    }
  });

//releasing connection
 connection.release();

});

We can run the above code using the following command :

								
>node drop-table-mysql.js
The details table is removed successfully

Summary

In this chapter of 30 days of node tutorial series, we learned how we can establish a connection between MySQL and node.sj , how we can perform CRUD (create, read, update, delete) operations in MySQL using node.js and also how to create and drop a table in mysql using node.js.