Monday, April 27, 2020

Node.js MySQL Create table

Node.js MySQL

 

node.js mysql

Database operations


  1. Establishing Connection
  2. Connection Options
  3. Create a Table
  4. Deleting a Table
  5. Create a Table with Primary Key
  6. Inserting Data into Table
  7. Inserting Multiple Records into the Table
  8. Selecting(Retrieving) Table Data
  9. Updating Table Record
  10. Terminating Database Connection


Establishing Connection


First, we need to download and install the 'mysql'  module for establishing a MySQL database connection with the MySQL database.

Include the 'mysql' module to the program as

 var  mysql  =  require('mysql');   

We also need to create a MySQL database before performing these operations.

The recommended way to establish the connection is,

//Establishing the connection
    var mysql      = require('mysql');
    var connection = mysql.createConnection({
      host     : 'hostname', //Host name
      user     : 'username', //Username
      password : 'password', //Your secret password
      database : 'dbname'    // Database name
     
    });
    
    connection.connect(function(error) {
      if (error) {
  console.error('error in connecting to the database: ' + err.stack);
        return;
      }
    
 console.log('Connection established successfully with id ' + connection.threadId);
    });


We can also do the same by implicitly invoking the query,

   //Establishing the connection
    var mysql      = require('mysql');
    var connection = mysql.createConnection({
        host     : 'hostname', //Host name
        user     : 'username', //Username
        password : 'password', //Your secret password
        database : 'dbname'    // Database name
      });

connection.query('DATABASE SQL QUERY', function (err, results, fields) {
     if (error) throw err;
      // connected to the database
      console.log("Successfully connected to the database");
     });


Connection Options

The available options to set a connection are listed below

Option  Description
host The hostname(by default localhost)
port The port number(by default 3306)
localAddress The source IP address
socketPath Path to Unix domain Socket(no need in case of host and port available)
user database user name
password database user password
database database name used for this connection
charset Charset of the connection(by default UTF8_GENERAL_CI)
timezone database server timezone
connectTimeout The milliseconds before a timeout occurs during the initial connection to
the MySQL server. (by default value is 10000)
stringyfyObjects To stringify objects(by default false)
insecureAuth To permit insecure auth methods(default false)
typecast by default true
queryFormat Custom SQL function format
supportBigNumbers Allow support for BIGINT and DECIMAL entries(by default false)
dateStrings Allow Date types(TIMESTAMP, DATETIME, DATE) to be returned
as strings instead of objects(by default false)
debug To prints protocol details(by default false)
trace To generate stack traces on Errors(by default true)
localInFile To allow LOAD DATA INFILE to use the LOCAL modifier. (by default: true) 
multipleStatements Allows multiple SQL statements to execute(by default false)
flags list of connection flags
ssl Object with SSL parameters or a string containing the name of SSL profile


Create a Table


First, create a database before creating a table in the database. We need to mention the name of the database to create the table in connection options.

Let's create a table with the named employee using the "CREATE TABLE" query.

var mysql = require('mysql');

var connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "secret",
  database: "empdb"
});

connection.connect(function(error) {
  if (error) throw error;
  console.log("Successfully, Connected to the database!");
  var sql = "CREATE TABLE employee (name VARCHAR(255), address VARCHAR(255))";
  connection.query(sql, function (error, result) {
    if (error) throw error;
    console.log("employee Table created Successfully");
  });
});
/*
Output:

Successfully, Connected to the database!
employee Table created Successfully
*/

Deleting a Table


We can delete the table using the "DROP TABLE" query to the database.

//Establishing the connection
var mysql = require('mysql');

var connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "secret",
  database: "empdb"
});

connection.connect(function(error) {
  if (error) throw error;
  console.log("Successfully, Connected to the database!");
  var sql = "DROP TABLE employee";
  connection.query(sql, function (error, result) {
    if (error) throw error;
    console.log("employee Table Deleted Successfully");
  });
});

/*
Output:

Successfully, Connected to the database!
employee Table Deleted Successfully
*/ 

Creating a Table with Primary Key



//Establishing the connection
    var mysql = require('mysql');

    var connection = mysql.createConnection({
      host: "localhost",
      user: "root",
      password: "secret",
      database: "empdb"
    });

    connection.connect(function(error) {
      if (error) throw error;
      console.log("Successfully, Connected to the database!");
      var sql = "CREATE TABLE empdata (id INT PRIMARY KEY, empname VARCHAR(20), dept VARCHAR(10), salary int)";
      connection.query(sql, function (error, result) {
        if (error) throw error;
        console.log("New empdata Table created Successfully");
      });
    });

/*
Output:

Successfully, Connected to the database!
New empdata Table created Successfully
*/ 

Inserting the Data into Table


We can insert data into the table using the "INSERT INTO TABLENAME" query

//Establishing the connection
   var mysql = require('mysql');

   var connection = mysql.createConnection({
      host: "localhost",
      user: "root",
      password: "secret",
      database: "empdb"
    });

    connection.connect(function(error) {
      if (error) throw error;
      console.log("Successfully, Connected to the database!");
      var sql = "INSERT INTO empdata VALUES(20, 'Jhony', 'Sales', 20000)";
      connection.query(sql, function (error, result) {
        if (error) throw error;
        console.log("Data inserted into empdata Successfully");
      });
    });
/*
Output:

Successfully, Connected to the database!
Data inserted into empdata Successfully
*/ 

Inserting Multiple Records to the Table


We can also insert multiple records to the table by executing the "INSERT INTO TABLE" query

//Establishing the connection
     var mysql = require('mysql');

    var connection = mysql.createConnection({
      host: "localhost",
      user: "root",
      password: "secret",
      database: "empdb"
    });

    connection.connect(function(error) {
      if (error) throw error;
console.log("Successfully, Connected to the database!");
var sql = "INSERT INTO empdata (id, empname, dept, salary) VALUES ?";
      var values = [ 
    ['21', 'Alex', 'HR', 25000], 
    ['22', 'Virat', 'HR', 26000], 
    ['23', 'Vivaan', 'Marketing', 21500] 
    ]; 
      connection.query(sql, [values], function (error, result) {
        if (error) throw error;
        console.log("Multiple rows inserted into empdata Successfully");
      });
    });

/*
Output:

Successfully, Connected to the database!
Multiple rows inserted into empdata Successfully
*/ 

Selecting (Retrieving) Table Data


We can easily select the data from the table using the "SELECT" query.

//Establishing the connection
        var mysql = require('mysql');

    var connection = mysql.createConnection({
      host: "localhost",
      user: "root",
      password: "secret",
      database: "empdb"
    });

    connection.connect(function(error) {
      if (error) throw error;
      console.log("Successfully, Connected to the database!");
      var sql = "SELECT * FROM empdata";
     
      connection.query(sql, function (error, result) {
        if (error) throw error;
        //Data in Json Format
        console.log(result);
        //processing the results
        console.log('======== Table Data ==========')
        for(emp of result)
           console.log(emp.id+'\t'+emp.empname+'\t'+emp.dept+'\t'+emp.salary);
      });
    });
/*
Output:Successfully, Connected to the database!
    [
      RowDataPacket {
        id: 20,
        empname: 'Jhony',
        dept: 'Sales',
        salary: 20000
      },
      RowDataPacket { id: 21, empname: 'Alex', dept: 'HR', salary: 25000 },
      RowDataPacket { id: 22, empname: 'Virat', dept: 'HR', salary: 26000 },
      RowDataPacket {
        id: 23,
        empname: 'Vivaan',
        dept: 'Marketing',
        salary: 21500
      }
    ]
    ======== Table Data ==========
    20      Jhony   Sales   20000
    21      Alex    HR      25000
    22      Virat   HR      26000
    23      Vivaan  Marketing       21500
*/ 

Updating Table Record  


A table can be updated using the "UPDATE" query.

//Establishing the connection
        var mysql = require('mysql');

    var connection = mysql.createConnection({
       host: "localhost",
       user: "root",
       password: "secret",
       database: "empdb"
     });

     connection.connect(function(error) {
       if (error) throw error;
       console.log("Successfully, Connected to the database!");
       var sql = "UPDATE empdata SET salary = 23500 WHERE id = 20";
      connection.query(sql, function (error, result) {
         if (error) throw error;
              console.log("Data updated in Table empdata Successfully");
       });
       //Select the Data form the table
connection.query("SELECT * FROM empdata WHERE id=20", function (error, result) {
         if (error) throw error;
                 console.log("========Updated Record=========");
                 console.log(result);
       });     

     });

/*
Output:
Successfully, Connected to the database!
    Data updated in Table empdata Successfully
    ========Updated Record=========
    [
      RowDataPacket {
        id: 20,
        empname: 'Jhony',
        dept: 'Sales',
        salary: 23500
      }
    ]
*/ 

Terminating Database Connection


There are two ways to terminate a database connection. It can be performed by,

By calling connection.end() method

The connection will end properly and gracefully.

    connection.end(function(err){

    //Connection terminated successfully
    //Connection is terminated gracefully
 //
    }); 

By Calling connection.destroy() method

This will terminate the database connection immediately. This method ensures that no more functions or Callbacks will be executed for the connection.

    connection.destroy(); 

Reference: https://www.npmjs.com/package/mysql