/ #crud operations using node js mysql #node.js express mysql example 

Using MySQL for CRUD operations with Nodejs[CRUD operations]

CRUD

MySQL is one of the best RDBMS being used for developing web-based software applications and in this article we will cover about using nodejs for CRUD operations with MySQL database with a step by step approach with code examples

  • Database - A database is a collection of tables, with related data.
  • Table - A table is a matrix with data. A table in a database looks like a simple spreadsheet.
  • Primary Key - A primary key is unique. A key value can not occur twice in one table. With a key, you can find at most one row.
  • Foreign Key - A foreign key is the linking pin between two tables.

Nodejs

Nodejs is a platform built on Chrome’s JavaScript runtime for easily building fast and scalable network applications.

Steps:

  1. To install MySQL in nodejs use npm package:

     npm install mysql
    
  2. Link MySQL with Nodejs server

    var mysql = require(‘mysql’);

  3. To create a new MySQL database and table

  • Create database: Creating separate space in MySQL

      Syntax:
          create database <DB_Name>;
    
  • Use database: To use the created Database

      Syntax:
          use <DB_Name>;
    
  • Create Table: Creating table in Database

     Syntax:
    
         create table <TableName>(Column1 Datatype,Column2 Datatype,...);
    
  • Connect MySQL and Nodejs:

    MySQL configuration

    Example :

      var connection = mysql.createConnection({ // creating connection string
          host     : 'localhost', //host name
          user     : 'root', // mySQL username  
          password : '', // mySql Password
          database : 'db_name' // database Name
      });
    
  • connecting with nodejs

    Example :

      connection.connect(function(err){ 
          if(!err) {
              console.log("Database is connected ... ");   
          } else {
              console.log("Error connecting database ... ");   
          }
      });
    
  • Insert table: Insert data in created table

    Syntax:

     insert into <TableName> values('string_data',integer_data,....);
    

    Example:

     connection.query("insert into user values("+value1+",'"+value2+"',"+value2+",'" value4+'")",
      function(err, rows, fields) { // INSERT query in nodejs file
     connection.end();
       if (!err)
         console.log('The solution is: ', rows);
       else
         console.log('Error while performing Query.');
       });
    
  • Select table: To retrieve data from table

    i) Syntax for access all Rows :

     select * from <TableName>; (or) select <column_name> from <TableName>;
    

    ii) Syntax for access particular Rows :

     select * from <TableName> where <column_name>='<user_data'>;
    

    Example :

     var query="select * from db_name where column1='"+value1+"' and column2='"+value2+"'";
     connection.query(query, function(err, rows, fields) { // SELECT query in nodejs file
     connection.end();
         if (!err){
             console.log('The solution is: ', rows);
         } else { 
             console.log('Error while performing Query.');
         }
     });
    
  • Update table: Syntax for update row:

     update <TableName> set <update_columnName> = <update_value> where <column_name>='<user_data'>; 
    

    Example:

     var query="update db_name set updatecolumn='"+updatevalue+"' 
     where conditioncolumn='"+conditionvalue+"'";
         connection.query(query, function(err, rows, fields) {           // SELECT query in nodejs file
         connection.end();
           if (!err)
             console.log('The solution is: ', rows);
           else
             console.log('Error while performing Query.');
           });
    
  • Delete table: To delete data or table

    i) Syntax for delete all data:

         delete table <TableName>;
    

    ii) Syntax for delete particular data:

         delete table <TableName> where <columnName>="<userdata>";
    

    Example:

     var query="delete table db_name where column1='"+value1;
     connection.query(query, function(err, rows, fields) { // SELECT query in nodejs file
         connection.end();
           if (!err){
             console.log('The solution is: ', rows);
           }else{
             console.log('Error while performing Query.');
           }
     });
    

MySQL aggregate functions:

The AVG function calculates the average value of a set of values. It ignores NULL values in the calculation.

Syntax:
    select AVG(<ColumnName) from <TableName>;

The COUNT function returns the number of the rows in a table.

Syntax:
    select COUNT(*) from <TableName> (or) select COUNT(<columnName>) from <TableName>;

The SUM function returns the sum of a set of values.

Syntax:
    select SUM(<ColumnName>) from <TableName>;

The MIN function returns the minimum value in a set of values.

Syntax:
    select MIN(<ColumnName>) from <TableName>;

The MAX function returns the maximum value in a set of values.

Syntax:
    select MAX(<ColumnName>) from <TableName>;

For any query contact Clofus Innovations.