/ #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.