nodejs with DB

/* to connect with db first we need to import that module*/
 var sql= require("./mysql");
var client = sql.createConnection({host:"localhost", user:"root"}); //at this point we can also send the password for db connecttion, ALL CAN PASS DATABASE name.
client.connect();
/*
CLIENT.CONNECT(FUNCTION(ERR){
    IF (ERR){
        RETURN CONSOLE.LOG("CONNECTION FAILED");
    }
    CONSOLE.LOG("CONNECTION SUCCEED");
}
*/


// if db is already created then call
client.query("use bookdb");
// if want to create a new database then call
client.query("create database bookdb", function(err){
	if (err){
		client.end()
		console.log(err);
	}else{
		console.log("Database is created");
	}
	});
client.query("use bookdb");

// NOW CREATE DATABSE tables-----

var sql="CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))";

var sql1= "CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))";
// sql1 is query with primary key

var sql2 = "INSERT INTO customers (name, address) VALUES ('Company Inc', 'Highway 37')";
// sql2 is query for insert data.

 var sql3 = "INSERT INTO customers (name, address) VALUES ?";
  var values = [
    ['John', 'Highway 71'],
    ['Peter', 'Lowstreet 4'],
    ['Amy', 'Apple st 652'],
    ['Hannah', 'Mountain 21'],
    ['Michael', 'Valley 345'],
    ['Sandy', 'Ocean blvd 2'],
    ['Betty', 'Green Grass 1'],
    ['Richard', 'Sky st 331'],
    ['Susan', 'One way 98'],
    ['Vicky', 'Yellow Garden 2'],
    ['Ben', 'Park Lane 38'],
    ['William', 'Central st 954'],
    ['Chuck', 'Main Road 989'],
    ['Viola', 'Sideway 1633']
  ];
// sql3 is query for insert data.



 client.query(sql, function(err,result){
     if (err){
         return console.log("table is not created");
     }
     console.log("table has created");
 });
 
 //for sql3
 client.query(sql, [values], function(err,result){
     if (err){
         return console.log("table is not created");
     }
     console.log("table has created with no of rows"+ result.affectedRows);
 });


/* THESE ALL THING RESULTS CONTAINS AFTER EXECUTING QUERY
{
  fieldCount: 0,
  affectedRows: 14,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '\'Records:14  Duplicated: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0
}
*/

/*===============================================*/

/*WHEN WE USE SELECT QUERY THEN WE NEED TO TAKE ONE MORE PARAMETER "fields" IN FUNCTION INSIDE CLIENT.QUERY()  
this fiels give all other details of fetch data
*/


/*===============================================


// 1
 var adr = 'Mountain 21';
  //Escape the address value:
  var sql = 'SELECT * FROM customers WHERE address = ' + mysql.escape(adr);
  
// 2
var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE address = ?';
con.query(sql, [adr], function (err, result)

// 3
var name = 'Amy';
var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE name = ? OR address = ?';
con.query(sql, [name, adr], function (err, result)

*/

/*
DROP TABLE customers
DROP TABLE IF EXISTS customers

============================================================================
SELECT * FROM customers LIMIT 5 = starting 5 rows
SELECT * FROM customers LIMIT 5 OFFSET 2= 5 rows but start from 3rd row.s

*/



















exports.authenticateUser=function(username, password, callback){
	var strQuery="select * from users where username='"+username+"' and password='"+password+"';";
client.query(strQuery,function(err,rows){
	if (err || !rows){
		return callback(err);
	}else if (rows.lenth===0){
		return callback(err);
	}else{
		return callback(null, "success");
	}
});
}
exports.addUser=function(username,password,address,res,callback){
	var strQuery="insert into users values('"+username+"','"+password+"','"+address+"');";
	client.query(strQuery,function(err, saved){
		if (err || !saved){
			return callback(err);
		}
	else{
		callback(null,saved);
	}
	});
}



/*=======================================CONNECT WITH MONGODB====================================================*/




var databasrUrl = "localhost/bookdb";
var collections = ["users","books"] // collections are like tables present in mongoDB.
var db = require("./mongojs");
db.connect(databasrUrl, collections); // this use when already database is created.

db.connect(databasrUrl,function(err,db){
	if(err){
		return console.log("error occured");
	}
	console.log("database is created");
	db.close();
});
var dbo = db.db("mydb"); // name of database
/* 
Important: In MongoDB, a database is not created until it gets content!

MongoDB waits until you have created a collection (table), with at least one document (record) before it actually creates the database (and collection).
 */
 
 db.createCollctions("customers", function(err,result){
	 if(err){
		return console.log("error occured");
	}
	console.log("Collctions is created");
 db.close();}
 );
 db.collection("customers").insertOne({name : "avinash", company : "infosys", address : "Maharamau"},function(err, result){
	 if(err){
		 return console.log("row not inserted");
	 }
	 console.log("data inserted");
	 db.close();
 });
 /* If you try to insert documents in a collection that do not exist, MongoDB will create the collection automatically. */

dbo.collection("customers").find({}).toArray(function(err, result) {
    if (err) throw err; // in this no parameter given in find function
    console.log(result);
    db.close();
  });

//updateCommands
 var myquery = { address: "Valley 345" };
  var newvalues = { $set: {name: "Mickey", address: "Canyon 123" } };
  dbo.collection("customers").updateOne(myquery, newvalues, function(err, res) {
    if (err) throw err;
    console.log("1 document updated");
    db.close();
  });