Category: Computer Programming

Node.js – MongoDB Insert ”; Previous Next MongoDB is a document-oriented database, which can be interfaced with Node.js through the NPM module called mongodb. A Document is the heart of a MongoDB database. It is a collection of key-value pairs. We can also think of it being similar to single row in a table of SQL based relational database, just as a Collection in MongoDB is analogous to a table in relational database. The NPM mongodb package provides insertOne() and insertMany() methods, using which one or more Documents can be added into a Collection, from within a Node.js application. MongoDB stores data records as BSON documents. BSON is a binary representation of JSON documents. MongoDB documents are composed of field-and-value pairs and have the following structure − { field1: value1, field2: value2, field3: value3, … fieldN: valueN } The value of a field can be any of the BSON data types, including other documents, arrays, and arrays of documents. Each document is characterised by a special key called “_id” having a unique value, similar to primary key in entity table of a relational database. A key is also referred to as a field. If an inserted document omits the _id field, the MongoDB driver automatically generates an ObjectId for the _id field. insertOne() method The Collection object has insertOne() method that inserts a single document into the collection. Collection.insertOne(doc) The document to insert is passed as an argument. It inserts a single document into MongoDB. If documents passed in do not contain the _id field, one will be added to each of the documents missing it by the driver, mutating the document. Example With the following code, we insert a single document into the products collection in the database. const {MongoClient} = require(”mongodb”); async function main(){ const uri = “mongodb://localhost:27017”; const client = new MongoClient(uri); try { // Connect to the MongoDB cluster await client.connect(); // Make the appropriate DB calls // Create a single new document await createdoc(client, “mydatabase”, “products”, { “ProductID”:1, “Name”:”Laptop”, “Price”:25000 }); } finally { // Close the connection to the MongoDB cluster await client.close(); } } main().catch(console.error); async function createdoc(client, dbname, colname, doc){ const dbobj = await client.db(dbname); const col = dbobj.collection(colname); const result = await col.insertOne(doc); console.log(`New document created with the following id: ${result.insertedId}`); } Output New listing created with the following id: 65809214693bd4622484dce3 The Mongo shell can also be used to view the document inserted. > use mydb; < switched to db mydb > products=db[”products”] < mydb.products > docs=products.find() { _id: ObjectId(“65809214693bd4622484dce3″), ProductID: 1, Name: ”Laptop”, Price: 25000 } insertMany() method The insertMany() method of the Collection object makes it possible to insert more than one documents. An array of JSON documents is used as the argument. We also use SRV connection string the following example − Example const {MongoClient} = require(”mongodb”); async function main(){ //const uri = “mongodb://localhost:27017”; const uri = “mongodb+srv://user:[email protected]/?retryWrites=true&w=majority”; const client = new MongoClient(uri); try { // Connect to the MongoDB cluster await client.connect(); // Make the appropriate DB calls // insert documents await createdocs(client, [ {”ProductID”:1, ”Name”:”Laptop”, ”price”:25000}, {”ProductID”:2, ”Name”:”TV”, ”price”:40000}, {”ProductID”:3, ”Name”:”Router”, ”price”:2000}, {”ProductID”:4, ”Name”:”Scanner”, ”price”:5000}, {”ProductID”:5, ”Name”:”Printer”, ”price”:9000} ]); } finally { // Close the connection to the MongoDB cluster await client.close(); } } main().catch(console.error); async function createdocs(client, docs){ const result = await client.db(“mydb”).collection(“products”).insertMany(docs); console.log(`${result.insertedCount} new document(s) created with the following id(s):`); console.log(result.insertedIds); } Output 5 new listing(s) created with the following id(s): { ”0”: new ObjectId(”6580964f20f979d2e9a72ae7”), ”1”: new ObjectId(”6580964f20f979d2e9a72ae8”), ”2”: new ObjectId(”6580964f20f979d2e9a72ae9”), ”3”: new ObjectId(”6580964f20f979d2e9a72aea”), ”4”: new ObjectId(”6580964f20f979d2e9a72aeb”) } You can export the collection of document in CSV format. _id,ProductID,Name,Price,price 65809214693bd4622484dce3,1,Laptop,25000, 6580964f20f979d2e9a72ae8,2,TV,40000 6580964f20f979d2e9a72ae9,3,Router,2000 6580964f20f979d2e9a72aea,4,Scanner,5000 6580964f20f979d2e9a72aeb,5,Printer,9000 Print Page Previous Next Advertisements ”;

Node.js – MySQL Create Table ”; Previous Next MySQL is a RDBMS software that uses SQL to perform CRUD operations on the data stored in the tables of a relational database. In this tutorial, we are learning how to use a MySQL database as a backend to a Node.js application. This chapter describes how to create a table in a MySQL database using the Node.js program. In a relational database, a table represents an entity, characterized by one or more attributes. Each row in the table is an instance of the entity. Each entity instance is described by certain values of the table attributes. A table generally has one attribute marked as a primary key, constraining each row to have a unique value. CREATE TABLE in MySQL MySQL implements the CREATE TABLE statement as per the SQL standards. This statement defines the number and type of attributes of the table. The syntax of MySQL”s CREATE TABLE query is as follows − CREATE TABLE [IF NOT EXISTS] table_name( column1 datatype constraints, column1 datatype constraints, ) ENGINE=storage_engine; MySQL supports variety of datatypes, such as integer types (such as INT, TINYINT etc), floating-point types (FLOAT and DOUBLE), string types (VARCHAR, TEXT etc), and DATE and TIME types. To ensure data integrity of the relational database, a column may have constraints applied on it, such as PRIMARY KEY, NOT NULL, AUTO_INCREMENT etc. Let us create employee Table in the mydb database. Start the MySQL server, open the MySQL command-line client, and issue the CREATE TABLE query as below − mysql> use mydb; Database changed mysql> CREATE TABLE IF NOT EXISTS employee ( -> id INT AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(20) NOT NULL, -> age INT, -> salary FLOAT -> ); Query OK, 0 rows affected (0.09 sec) MySQL command SHOW TABLES lists all the tables in the database that is in use. mysql> SHOW TABLES; +—————-+ | Tables_in_mydb | +—————-+ | employee | +—————-+ 1 row in set (0.00 sec) CREATE TABLE in Node.js Now we have to programmatically create a MySQL table with the help of Node.js application. The process involves following steps − Include the mysql module Call createConnection() function to obtain connection object. Use the query() method of the connection object, and pass the CREATE TABLE query string to it as the first argument. To check that the table has been created, pass the SHOW TABLES query to the query() method. Example var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “mydb” }); var qry =`CREATE TABLE IF NOT EXISTS employee ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT, salary FLOAT );`; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, function (err, result) { if (err) throw err; console.log(“Created table successfully”); }); con.query(“SHOW TABLES;”, function (err, result) { if (err) throw err; console.log(“Showing tablesn”); for (var i = 0; i < result.length; i++) { console.log(JSON.stringify(result[i])); } }) con.end(); }); Output Connected! Created table successfully Showing tables {“Tables_in_mydb”:”employee”} You can also use the DESC command that displays the fields, type, and constraints like default, primary key, etc. of each field in our table. con.query(“DESC employee;”, function (err, result) { if (err) throw err; console.log(JSON.stringify(result)); }); Output [ {“Field”:”id”,”Type”:”int”,”Null”:”NO”,”Key”:”PRI”,”Default”:null,”Extra”:”auto_increment”}, {“Field”:”name”,”Type”:”varchar(20)”,”Null”:”NO”,”Key”:””,”Default”:null,”Extra”:””}, {“Field”:”age”,”Type”:”int”,”Null”:”YES”,”Key”:””,”Default”:null,”Extra”:””}, {“Field”:”salary”,”Type”:”float”,”Null”:”YES”,”Key”:””,”Default”:null,”Extra”:””} ] Print Page Previous Next Advertisements ”;

Node.js – Modules ”; Previous Next A module in Node.js is a collection of independent and reusable code that can be imported into any Node.js application. As the name suggests, modules enable a modular and structured approach for developing a Node.js application. Instead of putting all the functions, classes and methods of an application in a single .js file, these resources are arranged in separate files (called modules) based on their relevance. This gives a better control over the maintenance and troubleshooting of the Node.js application. Node.js runs on V8 JavaScript engine, which interprets the JavaScript code. All the server-side processes are handled by the relevant Node.js module imported into the application by the require() function. A Node.js module is a library of functions, classes and other reusable code, stored in one or more .js files. A typical example of importing a Node.js module is how a Node.js server is launched. The createServer() function defined in the http module is needed to start the Node.js server. Hence, before calling the http module needs to be imported. http = require(”node:http”); listener = function (request, response) { … … }; server = http.createServer(listener); server.listen(3000); Types of Modules Each module in Node.js has its own context, and implements CommonJS modules standard. The modules in Node.js can be of any of the following three types − Third-party modules These modules are developed by independent developers and made available for use on the NPM repository. You should install a module either globally or locally in a Node.js project folder, if you want to incorporate its functionality in your application. The Express module is an example of such a module. To install, use any of the following commands − npm install express -g //install globally or npm install express –save //install locally You can then import the installed module in your application. For example − var express = require(”express”); Built-in modules The Node.js runtime software comes with the V8 JavaScript engine, bundled with a number of core modules, that perform important server-side tasks, such as managing event loop, perform file IO and operating system-specific functions etc. The examples of built-in or core modules are http, fs, console etc. These modules are pre-installed, but you must import them with require() function (except for a few modules such as process, buffer and console modules, they are global objects). For example − var fs = require(”fs”); Local modules A local module is a .js file, which stores definition of one or functions or classes required for your Node.js application. Such a module is present locally in the same Node.js application folder, which also should be included in the application with require() function. Every .js file in a Node.js application has a special module object. It’s exports property exposes a certain function, object or a variable from the .js file to the code outside. Example The following functions.js file includes three functions that return value of x raised to y, square root of x, and log value of x. These functions are exported for external consumption. exports.power = function powerval( x, y) { var val = Math.pow(x,y); return val; } exports.root = function rootval(x,y) { var val = Math.sqrt(x); return val; } exports.log = function logval(x) { var val = Math.log10(x); return val; } We shall now use these exported functions in another Node.js application main.js. First import the local functions module and then call the functions imported from it. const mymodule = require(”./mathfunctions”); console.log(“10 Raised to 3:” + mymodule.power(10,3)); console.log(“Square root of 10: “+ mymodule.root(10)); console.log(“Log of 1000: ” + mymodule.log(1000)); Output 10 Raised to 3:1000 Square root of 10: 3.1622776601683795 Log of 1000: 3 Print Page Previous Next Advertisements ”;

Node.js – Web Module ”; Previous Next What is a Web Server? The http module in Node.js enables data transfer between the server and client over the Hyper Text Transfer Protocol (HTTP). The createServer() function in http module creates an instance of Node.js http server. It listens to the incoming requests from other http clients over the designated host and port. The Node.js server is a software application which handles HTTP requests sent by the HTTP client, like web browsers, and returns web pages in response to the clients. Web servers usually deliver html documents along with images, style sheets, and scripts. Most of the web servers support server-side scripts, using scripting languages or redirecting the task to an application server which retrieves data from a database and performs complex logic and then sends a result to the HTTP client through the Web server. Web Application Architecture A Web application is usually divided into four layers − Client − This layer consists of web browsers, mobile browsers or applications which can make HTTP requests to the web server. Server − This layer has the Web server which can intercept the requests made by the clients and pass them the response. Business − This layer contains the application server which is utilized by the web server to do the required processing. This layer interacts with the data layer via the database or some external programs. Data − This layer contains the databases or any other source of data. Creating a Web Server using Node Node.js provides an http module which can be used to create an HTTP client of a server. Following is the bare minimum structure of the HTTP server which listens at 5000 port. Create a js file named server.js − var http = require(”http”); var fs = require(”fs”); var url = require(”url”); // Create a server http.createServer( function (request, response) { // Parse the request containing file name var pathname = url.parse(request.url).pathname; // Print the name of the file for which request is made. console.log(“Request for ” + pathname + ” received.”); // Read the requested file content from file system fs.readFile(pathname.substr(1), function (err, data) { if (err) { console.log(err); } else { response.writeHead(200, {”Content-Type”: ”text/html”}); // Write the content of the file to response body console.log(data.toString()); response.write(data.toString()); } // Send the response body response.end(); }); }).listen(5000); // Console will print the message console.log(”Server running at http://127.0.0.1:5000/”); The createServer() function starts listening for client requests at the 5000 port of localhost. The Http Request and Server Response objects are provided internally by the Nde.js server. It fetches the URL of HTML file requested by the HTTP client. The callback function to the createServer() function reads the requested file, and writes its contents as the server’s response. You can send a HTTP request from a browser on the server machine itself. Run the aboce server.js file, and enter http://localhost:5000/index.html as the URL in a browser window. The contents of index.html page will be rendered. Send a request for any other existing web page, such as http://localhost:5000/hello.html, and the requested page will be rendered. Creating Web client using Node A web client can be created using http module. Let”s check the following example. Create a js file named client.js (Save this file in another folder, not in the folder containing server.js script) − var http = require(”http”); var fs = require(”fs”); var path = require(”path”); // Options to be used by request var options = { host: ”localhost”, port: ”5000”, path: path.join(”/”,process.argv[2]) }; var body = ””; // Callback function is used to deal with response var callback = function(response) { // Continuously update stream with data response.on(”data”, function(data) { body += data; }); response.on(”end”, function() { // Data received completely. console.log(body); fs.writeFile(options.path.substr(1), body, function (err) { if (err) console.log(err); else console.log(”Write operation complete.”); }); }); } // Make a request to the server var req = http.request(options, callback); req.end(); This client script sends a HTTP request for a webpage given as a command-line argument. For example − node main.js index.html The name of the file is the argv[2] in the argument list. It is used as the path parameter in the options parameter to be passed to the http.request() method. Once the server accepts this request, its contents are written in the response stream. The client.js receives this response and writes the data received as a new file in the client”s folder. Run the server code first. From another terminal, run the client code. You will see the requested HTML file created in the client folder. Print Page Previous Next Advertisements ”;

Node.js – MySQL Order By ”; Previous Next In a Node.js application, you may want to retrieve data from a MySQL database in ascending or descending order. In MySQL, the ORDER BY clause sorts the resultset returned by the SELECT statement in a specified order. When using a Node.js application to retrieve data from a MySQL database, the query string argument of the query() method of the connection object as defined in the mysql module should have the ORDER BY clause. In this chapter, the various use cases of ORDER BY clause in SELECT statement in a Node.js application have been described with examples. The syntax of ORDER BY clause in SELECT statement is as follows − SELECT select_list FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], …; You specify the one or more columns that you want to sort after the ORDER BY clause. The default sort order is ascending (ASC). ORDER BY column1; To obtain the rows in a descending order, use DESC in front of the column name. ORDER BY column1 DESC; The ascending order on numeric fields obtains the rows from smallest to largest value of the field. Whereas, the ascending order on a string field (such as VARCHAR or TEXT) results in alphabetical order from a to z. Similarly, for a DateTime field, the ascending order refers to the chronological order, i.e., from earlier date to the latter date. Example The following Node.js code, the query() method of mysql module returns the rows from employee table in the ascending order of salary field. var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “mydb” }); var qry =`SELECT * FROM employee ORDER BY salary;`; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, function (err, results) { if (err) throw err; console.log(results); }); con.end(); }); Output [ RowDataPacket { id: 1, name: ”Ravi”, age: 25, salary: 25000 }, RowDataPacket { id: 3, name: ”Meena”, age: 26, salary: 27000 }, RowDataPacket { id: 2, name: ”Anil”, age: 26, salary: 30000 } ] As an example of descending order with DESC keyword with a field, change the query string to the following − var qry =`SELECT * FROM employee ORDER BY name DESC;`; The resultant result set will be in the descending order of names in employee able. [ RowDataPacket { id: 1, name: ”Ravi”, age: 25, salary: 25000 }, RowDataPacket { id: 3, name: ”Meena”, age: 26, salary: 27000 }, RowDataPacket { id: 2, name: ”Anil”, age: 26, salary: 30000 } ] Multiple-column sort As mentioned earlier, you can specify the one or more columns after the ORDER BY clause. This first sorts the rows on the first column. The rows with same value of first column are sorted on the value of second column. In effect, this becomes a sort within a sort or nested sort. To illustrate multiple-column sort, we shall use the world database that is pre-installed in the MySQL 8.0 installation. The world database includes a city table with the following structure − +————-+———-+——+—–+———+—————-+ | Field | Type | Null | Key | Default | Extra | +————-+———-+——+—–+———+—————-+ | ID | int | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int | NO | | 0 | | +————-+———-+——+—–+———+—————-+ With the following Node.js code, we shall fetch District-wise list of cities sorted in ascending order of population. Example var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “world” }); var qry =`select * from city where name like ”D%” and countrycode=”IND” order by district, population;`; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, function (err, results) { if (err) throw err; console.log(results); }); con.end(); }); Output [ RowDataPacket { ID: 1276, Name: ”Dibrugarh”, CountryCode: ”IND”, District: ”Assam”, Population: 120127 }, RowDataPacket { ID: 1350, Name: ”Dehri”, CountryCode: ”IND”, District: ”Bihar”, Population: 94526 }, RowDataPacket { ID: 1138, Name: ”Darbhanga”, CountryCode: ”IND”, District: ”Bihar”, Population: 218391 }, RowDataPacket { ID: 1206, Name: ”Durg”, CountryCode: ”IND”, District: ”Chhatisgarh”, Population: 150645 }, RowDataPacket { ID: 1351, Name: ”Delhi Cantonment”, CountryCode: ”IND”, District: ”Delhi”, Population: 94326 }, RowDataPacket { ID: 1025, Name: ”Delhi”, CountryCode: ”IND”, District: ”Delhi”, Population: 7206704 }, RowDataPacket { ID: 1203, Name: ”Dhanbad”, CountryCode: ”IND”, District: ”Jharkhand”, Population: 151789 }, RowDataPacket { ID: 1119, Name: ”Davangere”, CountryCode: ”IND”, District: ”Karnataka”, Population: 266082 }, RowDataPacket { ID: 1347, Name: ”Damoh”, CountryCode: ”IND”, District: ”Madhya Pradesh”, Population: 95661 }, RowDataPacket { ID: 1186, Name: ”Dewas”, CountryCode: ”IND”, District: ”Madhya Pradesh”, Population: 164364 }, RowDataPacket { ID: 1113, Name: ”Dhule (Dhulia)”, CountryCode: ”IND”, District: ”Maharashtra”, Population: 278317 }, RowDataPacket { ID: 1167, Name: ”Dindigul”, CountryCode: ”IND”, District: ”Tamil Nadu”, Population: 182477 }, RowDataPacket { ID: 1117, Name: ”Dehra Dun”, CountryCode: ”IND”, District: ”Uttaranchal”, Population: 270159 }, RowDataPacket { ID: 1214, Name: ”Dabgram”, CountryCode: ”IND”, District: ”West Bengal”, Population: 147217 }, RowDataPacket { ID: 1082, Name: ”Durgapur”, CountryCode: ”IND”, District: ”West Bengal”, Population: 425836 } ] Print Page Previous Next Advertisements ”;

Node.js – MongoDB Join ”; Previous Next MongoDB is a NoSQL database, and it doesn’t support JOIN operations as found in relation databases such as MySQL. However, a similar functionality can be achieved by calling the aggregate() method of the Collection object, and the $lookup stage. $aggregate() function This function Performs a left outer join to another collection in the same database to filter in documents from the “joined” collection for processing. $lookup: Performs a left outer join to a collection in the same database to filter in documents from the “joined” collection for processing. The $lookup stage adds a new array field to each input document. The new array field contains the matching documents from the “joined” collection. To perform an equality match between a field from the input documents with a field from the documents of the “joined” collection, the $lookup stage has this syntax − { $lookup: { from: <collection to join>, localField: <field from the input documents>, foreignField: <field from the documents of the “from” collection>, as: <output array field> } } The parameters in $lookup stage are as follows − Sr.No Parameter & Description 1 from Specifies the collection in the same database to perform the join with. from is optional, you can use a $documents stage in a $lookup stage instead. For an example, see Use a $documents Stage in a $lookup Stage. 2 localField Specifies the field from the documents input to the $lookup stage. $lookup performs an equality match on the localField to the foreignField from the documents of the from collection. 3 foreignField Specifies the field from the documents in the from collection. 4 As Specifies the name of the new array field to add to the input documents. The new array field contains the matching documents from the from collection. The $lookup operation corresponds to the following SQL query − SELECT *, <output array field> FROM collection WHERE <output array field> IN ( SELECT * FROM <collection to join> WHERE <foreignField> = <collection.localField> ); Example To demonstrate the JOIN operation in MongoDB, create two Collections − inventory and orders. The inventory Collection ( [ { prodId: 100, price: 20, quantity: 125 }, { prodId: 101, price: 10, quantity: 234 }, { prodId: 102, price: 15, quantity: 432 }, { prodId: 103, price: 17, quantity: 320 } ] ) The orders collection ( [ { orderId: 201, custid: 301, prodId: 100, numPurchased: 20 }, { orderId: 202, custid: 302, prodId: 101, numPurchased: 10 }, { orderId: 203, custid: 303, prodId: 102, numPurchased: 5 }, { orderId: 204, custid: 303, prodId: 103, numPurchased: 15 }, { orderId: 205, custid: 303, prodId: 103, numPurchased: 20 }, { orderId: 206, custid: 302, prodId: 102, numPurchased: 1 }, { orderId: 207, custid: 302, prodId: 101, numPurchased: 5 }, { orderId: 208, custid: 301, prodId: 100, numPurchased: 10 }, { orderId: 209, custid: 303, prodId: 103, numPurchased: 30 } ] ) The following code calls aggregate() method on the Collection object and the $lookup stage. const {MongoClient} = require(”mongodb”); async function main(){ const uri = “mongodb://localhost:27017/”; const client = new MongoClient(uri); try { await client.connect(); await joindocs(client, “mydb”, “orders”, “inventory”); } finally { await client.close(); } } main().catch(console.error); async function joindocs(client, dbname, col1, col2){ const result = await client.db(dbname).collection(”orders”).aggregate([ { $lookup: { from: ”inventory”, localField: ”prodId”, foreignField: ”prodId”, as: ”orderdetails” } } ]).toArray(); result.forEach(element => { console.log(JSON.stringify(element)); }); } The $lookup stage lets you specify which collection you want to join with the current collection, and which fields that should match. Output {“_id”:”658c4b14943e7a1349678bf3″,”orderId”:201,”custid”:301,”prodId”:100,”numPurchased”:20,”orderdetails”:[{“_id”:”658c4aff943e7a1349678bef”,”prodId”:100,”price”:20,”quantity”:125}]} {“_id”:”658c4b14943e7a1349678bf4″,”orderId”:202,”custid”:302,”prodId”:101,”numPurchased”:10,”orderdetails”:[{“_id”:”658c4aff943e7a1349678bf0″,”prodId”:101,”price”:10,”quantity”:234}]} {“_id”:”658c4b14943e7a1349678bf5″,”orderId”:203,”custid”:303,”prodId”:102,”numPurchased”:5,”orderdetails”:[{“_id”:”658c4aff943e7a1349678bf1″,”prodId”:102,”price”:15,”quantity”:432}]} {“_id”:”658c4b14943e7a1349678bf6″,”orderId”:204,”custid”:303,”prodId”:103,”numPurchased”:15,”orderdetails”:[{“_id”:”658c4aff943e7a1349678bf2″,”prodId”:103,”price”:17,”quantity”:320}]} {“_id”:”658c4b14943e7a1349678bf7″,”orderId”:205,”custid”:303,”prodId”:103,”numPurchased”:20,”orderdetails”:[{“_id”:”658c4aff943e7a1349678bf2″,”prodId”:103,”price”:17,”quantity”:320}]} {“_id”:”658c4b14943e7a1349678bf8″,”orderId”:206,”custid”:302,”prodId”:102,”numPurchased”:1,”orderdetails”:[{“_id”:”658c4aff943e7a1349678bf1″,”prodId”:102,”price”:15,”quantity”:432}]} {“_id”:”658c4b14943e7a1349678bf9″,”orderId”:207,”custid”:302,”prodId”:101,”numPurchased”:5,”orderdetails”:[{“_id”:”658c4aff943e7a1349678bf0″,”prodId”:101,”price”:10,”quantity”:234}]} {“_id”:”658c4b14943e7a1349678bfa”,”orderId”:208,”custid”:301,”prodId”:100,”numPurchased”:10,”orderdetails”:[{“_id”:”658c4aff943e7a1349678bef”,”prodId”:100,”price”:20,”quantity”:125}]} {“_id”:”658c4b14943e7a1349678bfb”,”orderId”:209,”custid”:303,”prodId”:103,”numPurchased”:30,”orderdetails”:[{“_id”:”658c4aff943e7a1349678bf2″,”prodId”:103,”price”:17,”quantity”:320}]} Print Page Previous Next Advertisements ”;

Node.js – MySQL Insert Into ”; Previous Next A table in a MySQL database stores one or more rows or records. Each record consists of columns or attributes as defined by the table structure. In this chapter, you will learn how to use INSERT INTO statement to add a new row in a MySQL table, using MySQL client, as well as by a Node.js program. Assuming that the mydb database is already created on the MySQL server, and it has EMPLOYEE table in it, with the following structure − mysql> desc employee; +——–+————-+——+—–+———+—————-+ | Field | Type | Null | Key | Default | Extra | +——–+————-+——+—–+———+—————-+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int | YES | | NULL | | | salary | float | YES | | NULL | | +——–+————-+——+—–+———+—————-+ 4 rows in set (0.01 sec) The INSERT INTO statement has the following syntax − INSERT INTO table_name (field1, field2, ……) VALUES (value1,value2, ……); The parenthesis after the table_name is the field list. The values to be filled in these fields are put in the second parenthesis after the VALUES clause, in the same order as the fields. If the values list has the same number and order of the fields as in the table structure, you may omit the field list. If the table structure has any auto_increment fields, they will be filled automatically. Open the MySQL client and issue the following INSERT INTO statement to insert a new record in the EMPLOYEE table. Use the SELECT query to check if the new record is added. mysql> INSERT INTO employee VALUES(1, “Ravi”, 25, 25000); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM employee; +—-+——+——+——–+ | id | name | age | salary | +—-+——+——+——–+ | 1 | Ravi | 25 | 25000 | +—-+——+——+——–+ 1 row in set (0.00 sec) Insert with Node.js Now we have to programmatically insert a new record in a MySQL table with the help of Node.js application. To insert data in a table, its database must be loaded first. The SQL command USE database does it in MySQL client terminal. You can use a database by adding a database parameter in the createConnection() method − var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “mydb” }); The Node.js code for inserting a new record involves following steps − Include the mysql module Call createConnection() function to obtain connection object. Use the query() method of the connection object, and pass the INSERT INTO query string to it as the first argument. Example var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “mydb” }); var qry =`INSERT INTO employee (name, age, salary) VALUES(“Ravi”, 25, 25000);`; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, function (err, result) { if (err) throw err; console.log(“New Record inserted successfully”); }); con.end(); }); Output Connected! New Record inserted successfully Run the SELECT query in the MySQL client terminal to check if a new record has been added. INSERT with prepared statement MySQL supports the prepared statement syntax where the variable data is dynamically added to the query string. The syntax for INSERT query with prepared statement is − qry = “INSERT INTO table_name VALUES (?,?,?)”, [var1, var2, var3]; In the following code, the data for a new record is stored in an array. It is used as a parameter for the con.query() method. The values from the array substitute the? place holders in the prepared query. var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “mydb” }); var data = [“Ravi”, 25, 25000]; var qry =`INSERT INTO employee (name, age, salary) VALUES(?,?,?);`; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry,data, function (err, result) { if (err) throw err; console.log(“New Record inserted successfully”); }); con.end(); }); When the above program is executed, a new record with the values as given in the data array is stored. You can insert multiple records with a two-dimensional array used as the source of values. Example var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “mydb” }); var data = [ [“Ravi”, 25, 25000], [“Anil”, 26, 30000], [“Meena”, 26, 27000] ]; var qry =`INSERT INTO employee (name, age, salary) VALUES(?,?,?);`; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); for (var i=0;i<data.length; i++){ con.query(qry,data[i], function (err, result) { if (err) throw err; console.log(“New Record inserted successfully”); }) }; con.end(); }); Check the data in the employee table by running the SELECT query n MySQL command-line client as below: mysql> SELECT * FROM employee; +—-+——-+——+——–+ | id | name | age | salary | +—-+——-+——+——–+ | 1 | Ravi | 25 | 25000 | | 2 | Anil | 26 | 30000 | | 3 | Meena | 26 | 27000 | +—-+——-+——+——–+ 3 rows in set (0.00 sec) Print Page Previous Next Advertisements ”;

Node.js – MongoDB Sort ”; Previous Next The result of find() query on a collection in a MongoDB database can be arranged in ascending or descending order of a certain field in the Document. The mongodb driver for Node.js has sort() method defined in the Collection object. To sort the results of a MongoDB query in Node.js, you can use the sort() method. This method allows you to order the returned documents by the values of one or more fields in a certain direction. To sort returned documents by a field in ascending (lowest first) order, use a value of 1. To sort in descending (greatest first) order instead, use -1. The syntax for ascending order is as follows − result = col.find(query).sort(field:1); The syntax for descending sort is as follows − result = col.find(query).sort(field:-1); Ascending sort The following example displays the documents in the products collection in the ascending order of price. Example const {MongoClient} = require(”mongodb”); async function main(){ const uri = “mongodb://localhost:27017/”; const client = new MongoClient(uri); try { await client.connect(); await sortdocs(client, “mydb”, “products”); } finally { await client.close(); } } main().catch(console.error); async function sortdocs(client, dbname, colname){ var mysort = { price: 1 }; const result = await client.db(dbname).collection(colname).find({}).sort(mysort).toArray(); result.forEach(element => { console.log(element); }); } Output { _id: new ObjectId(”6580964f20f979d2e9a72ae9”), ProductID: 3, Name: ”Router”, price: 2000 } { _id: new ObjectId(”6580964f20f979d2e9a72aea”), ProductID: 4, Name: ”Scanner”, price: 5000 } { _id: new ObjectId(”6580964f20f979d2e9a72aeb”), ProductID: 5, Name: ”Printer”, price: 9000 } { _id: new ObjectId(”65809214693bd4622484dce3”), ProductID: 1, Name: ”Laptop”, Price: 25000 } { _id: new ObjectId(”6580964f20f979d2e9a72ae8”), ProductID: 2, Name: ”TV”, price: 40000 } Descending sort To produce list of documents from products collection in the descending order of name field, change the sortdocs() function to the following − Example async function sortdocs(client, dbname, colname){ var mysort = { Name: -1 }; const result = await client.db(dbname).collection(colname).find({}).sort(mysort).toArray(); result.forEach(element => { console.log(element); }); }); Output { _id: new ObjectId(”6580964f20f979d2e9a72ae8”), ProductID: 2, Name: ”TV”, price: 40000 } { _id: new ObjectId(”6580964f20f979d2e9a72aea”), ProductID: 4, Name: ”Scanner”, price: 5000 } { _id: new ObjectId(”6580964f20f979d2e9a72ae9”), ProductID: 3, Name: ”Router”, price: 2000 } { _id: new ObjectId(”6580964f20f979d2e9a72aeb”), ProductID: 5, Name: ”Printer”, price: 9000 } { _id: new ObjectId(”65809214693bd4622484dce3”), ProductID: 1, Name: ”Laptop”, Price: 25000 } Print Page Previous Next Advertisements ”;

Node.js – MySQL Update ”; Previous Next A data-driven Node.js application often needs to modify one or more records stored in a MySQL database. This is done by passing the UPDATE query string as an argument to the mysql.query() method. Quite often, the data with an existing record is to be updated, comes in the form of user input, such as a HTML form posted in a Node.js web based application. In this chapter, you”ll learn how to execute MySQL UPDATE query. Starting with a simple UPDATE, using a prepared statement and UPDATE with JOIN will be demonstrated with the help of suitable Node.js examples. Simple UPDATE The syntax of a basic UPDATE statement in MySQL is as follows − UPDATE table_name SET column_name1 = expr1, column_name2 = expr2, … WHERE condition; Assuming that a database mydb is available on the MySQL server, and the employee table is present with the following data − mysql> select * from employee; +—-+——-+——+——–+ | id | name | age | salary | +—-+——-+——+——–+ | 1 | Ravi | 25 | 25000 | | 2 | Anil | 26 | 30000 | | 3 | Meena | 26 | 27000 | +—-+——-+——+——–+ Example The following program updates the salary field of employee table, increasing the salary of each employee by Rs.500 var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “mydb” }); var qry =”UPDATE employee SET salary=salary+500;”; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, function(err) { if (err) throw err; console.log(“Records updated successfully”); }); }); After running the above code, go to the MySQL command-line client and check the rows in employee table − mysql> select * from employee; +—-+——-+——+——–+ | id | name | age | salary | +—-+——-+——+——–+ | 1 | Ravi | 25 | 25500 | | 2 | Anil | 26 | 30500 | | 3 | Meena | 26 | 27500 | +—-+——-+——+——–+ You can also add a loop as follows in the code to see the employee records qry =`SELECT name,salary FROM employee;`; con.query(qry, function (err, results) { if (err) throw err; console.log(results); }); Output [ RowDataPacket { name: ”Ravi”, salary: 25500 }, RowDataPacket { name: ”Anil”, salary: 30500 }, RowDataPacket { name: ”Meena”, salary: 27500 } ] UPDATE with Prepared statement MySQL supports prepared statements. You can build the query dynamically by inserting variable data in the placeholders embedded in the query string. MySQL uses ? symbol as the placeholder. var qry =”UPDATE employee SET salary=40000 WHERE name=?;”; var nm = “Anil”; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, nm, function(err) { if (err) throw err; console.log(“Records updated successfully”); var qry =`SELECT name,salary FROM employee WHERE name=?;`; con.query(qry,nm, function (err, results) { if (err) throw err; console.log(results); }); }); }); This will update the salary of employee with Anil as name to Rs. 40000 [ RowDataPacket { name: ”Anil”, salary: 40000 } ] UPDATE JOIN The JOIN clause is more commonly used in SELECT query to retrieve data from two or more related tables. You can also include JOIN clause in UPDATE query also, to perform cross-table updates. For this example, we shall create two tables with a common field to establish PRIMARY KEY − FOREIGN KEY relationship between the two. merits table CREATE TABLE merits ( performance INT(11) NOT NULL, percentage FLOAT NOT NULL, PRIMARY KEY (performance) ); Add some data − INSERT INTO merits(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); (4,0.05), (5,0.08); The contents of merits table − mysql> select * from merits; +————-+————+ | performance | percentage | +————-+————+ | 1 | 0 | | 2 | 0.01 | | 3 | 0.03 | | 4 | 0.05 | | 5 | 0.08 | +————-+————+ employees table CREATE TABLE employees ( emp_id INT(11) NOT NULL AUTO_INCREMENT, emp_name VARCHAR(255) NOT NULL, performance INT(11) DEFAULT NULL, salary FLOAT DEFAULT NULL, PRIMARY KEY (emp_id), CONSTRAINT fk_performance FOREIGN KEY (performance) REFERENCES merits (performance) ); In this table, performance is the foreign key, referring to the key of same name in merits table. Add some data − INSERT INTO employees(emp_name,performance,salary) VALUES(”Mary Doe”, 1, 50000), (”Cindy Smith”, 3, 65000), (”Sue Greenspan”, 4, 75000), (”Grace Dell”, 5, 125000), (”Nancy Johnson”, 3, 85000), (”John Doe”, 2, 45000), (”Lily Bush”, 3, 55000); The contents of employees table − mysql> select * from employees; +——–+—————+————-+——–+ | emp_id | emp_name | performance | salary | +——–+—————+————-+——–+ | 1 | Mary Doe | 1 | 50000 | | 2 | Cindy Smith | 3 | 65000 | | 3 | Sue Greenspan | 4 | 75000 | | 4 | Grace Dell | 5 | 125000 | | 5 | Nancy Johnson | 3 | 85000 | | 6 | John Doe | 2 | 45000 | | 7 | Lily Bush | 3 | 55000 | +——–+—————+————-+——–+ 7 rows in set (0.00 sec) We would like to increment the salary of employee based on the percentage related to his performance rating. Example var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “mydb” }); var qry =` UPDATE employees INNER JOIN merits ON employees.performance = merits.performance SET salary = salary + salary * percentage; `; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, nm, function(err) { if (err) throw err; con.query(qry,nm, function (err, results) { if (err) throw err; console.log(results); }); }); }); Output OkPacket { fieldCount: 0, affectedRows: 7, insertId: 0, serverStatus: 34, warningCount: 0, message: ”(Rows matched: 7 Changed: 6 Warnings: 0”, protocol41: true, changedRows: 6 } Check the updated salary field in employees table

Node.js – MySQL Delete ”; Previous Next Out of the CRUD operations to be performed with a MySQL database, the DELETE query helps in removing one or more rows from a table. In this chapter, we shall show how to call MySQL DELETE statement in a Node.js application. The basic DELETE query has the following syntax − DELETE FROM table_name WHERE condition; Although the WHERE clause is optional, most probably it is invariable used, otherwise it delete all rows in the table. Simple DELETE In the following Node.js code, a DELETE query string is passed to the mysql.query() method. The program will delete all the employee records with age>25. Example var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “mydb” }); var qry =”DELETE FROM employee WHERE age>25;”; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, function (err, results) { if (err) throw err; console.log(results); }); con.end(); }); Output OkPacket { fieldCount: 0, affectedRows: 0, insertId: 0, serverStatus: 34, warningCount: 0, message: ””, protocol41: true, changedRows: 0 } Drop the WHERE clause from the query string in above code. var qry =”DELETE FROM employee;”; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, function (err, results) { if (err) throw err; console.log(results); }); con.end(); }); Now open the command-line client and run the SELECT query mysql> select * from employee; Empty set (0.00 sec) You can see that there are no records left in the table. The DELETE query without WHERE clause is equivalent to TRUNCATE statement. var qry =”TRUNCATE employee;”; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, function (err, results) { if (err) throw err; console.log(results); }); con.end(); }); LIMIT clause The LIMIT clause in the DELETE query restricts the delete operation to a specified number. For example, LIMIT 5 deletes only first 5 records in the given order. For this example, we shall use the pre-installed world database, and the city table in it. Example var mysql = require(”mysql”); var con = mysql.createConnection({ host: “localhost”, user: “root”, password: “mypassword”, database: “world” }); var qry =”DELETE FROM city WHERE CountryCode=”IND” ORDER BY population LIMIT 5;”; con.connect(function (err) { if (err) throw err; console.log(“Connected!”); con.query(qry, function (err, results) { if (err) throw err; console.log(results); }); con.end(); }); Output OkPacket { fieldCount: 0, affectedRows: 5, insertId: 0, serverStatus: 2, warningCount: 0, message: ””, protocol41: true, changedRows: 0 } Originally the City table had 341 rows with IND as CountryCode. With 5 cities in ascending order of population deleted by running the above code, there will be 336 cities left in the table. You can check it by running the SELECT COUNT query before and after running the above code. mysql> SELECT COUNT(name) from city WHERE CountryCode=”IND”; Print Page Previous Next Advertisements ”;