Contents hide 1) Setup npm 2) SQL Database connection 3) Database And Table Creation 4) Creating Table 5) Inserting And Viewing Data 6) Updating And Deleting Data Beginning With NodeJS And MySql Queries, In this tutorial, we will see the different MySql queries (i.e insert, update, delete, drop, create etc) applying through nodeJS. Topics : Setup npm SQL Database connection Database And Table Creation Inserting And Viewing Data Updating And Deleting Data Setup npm Let’s create a directory with any name to create the app through the terminal. $ mkdir node_sql_demo After creating directory lets begin with npm setup for the package.json file. So enter the details for file creation. $ npm init // filing up the procedure name: (node_sql_demo) //default folder name version: (1.0.0) 0.0.1 // your version number description: Tutorial on Nodejs With Mysql Queries entry point: (index.js) app.js // your main file test command: git repository: // git project if any keywords: geekstrick author: rehmaanali license: (ISC) As it created the package.json file. So now we just need to install the MySql package by just a single command in terminal. $ npm install mysql --save The command will install the MySql node Package as well as it will make changes in the package.json file. Here it shows the dependencies and its version number. { "name": "node_sql_demo", "version": "0.0.1", "description": "Tutorial On NodeJS with mySql Queries", "main": "app.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1" }, "keywords": [ "geekstrick" ], "author": "RehmaanAli", "license": "ISC", "dependencies": { "mysql": "^2.9.0" } } SQL Database connection First import the MySql package in the app file by the require method and also start the MySql localhost server.As we declare the main as app.js so the file must be named as that only. var mysql = require('mysql'); // Creating the connection var con = mysql.createConnection({ host: "localhost", user: "username", password: "password" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); // message }); Now to run the app.js file simply write the command $ node app.js conected! // Successfully connected message Database And Table Creation And Now will create the database as well a table in that particular Database. But first, we will create the database var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); con.query("CREATE DATABASE mydb", function (err, result) { if (err) throw err; console.log("Database created"); }); }); This will connect database as well as creates the database of name mydb. So run the file as we run earlier i.e $ node app.js. Creating Table Creating table is as similar as it was creating a database, but all we need to do is to give the database name in the ‘con’ variable. let’s see: var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "yourusername", password: "yourpassword", database: "mydb" // Declare the database name }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); // Create table Query var sql = "CREATE TABLE customers (id VARCHAR(12), name VARCHAR(30))"; con.query(sql, function (err, result) { if (err) throw err; console.log("Table created"); }); }); Inserting And Viewing Data Let us see the further procedure for inserting data and viewing in the terminal. So first we have to insert data into the table. var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "", database: "mydb" }); con.connect(function(err) { if (err) throw err; console.log("Connected!"); var sql = "INSERT INTO customers (id, name) VALUES (0001, 'MR. Bean')"; con.query(sql, function (err, result) { if (err) throw err; console.log("1 record inserted"); }); }); After the data is inserted successfully, now we can display the data of table on the terminal. So just add the SELECT Query to get the data. con.connect(function(err) { if (err) throw err; console.log("Connected!"); con.query("SELECT * FROM customers", function (err, result) { if (err) throw err; console.log(result); }); }); And the data which is displayed are in raw (json format). $ node app.js Connected! [ RowDataPacket { id: '1', name: 'MR. Bean' }] Updating And Deleting Data For Editing the table you just need to change the query. so first we will update the table var mysql = require('mysql'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "", database: "mydb" }); con.connect(function(err) { if (err) throw err; var sql = "UPDATE customers SET name = 'MR.Cruze' WHERE id = 0001"; con.query(sql, function (err, result) { if (err) throw err; console.log(result.affectedRows + " record(s) updated"); // Shows the number of updated table }); }); And at last for deleting the record or table from the database and that is similar to all above queries. con.connect(function(err) { if (err) throw err; var sql = "DELETE FROM customers WHERE id = 0001"; con.query(sql, function (err, result) { if (err) throw err; console.log("Number of records deleted: " + result.affectedRows); }); }); Conclusion This was the simple data handling from database, but we can furthermore classify the working of the MySql in big project’s User Login Logout RESTful API Using Nodejs And Express 4 User Registration RESTful API Using Node.js And Express 4 Connecting Mysql Database Using Node Express Share this:TwitterFacebookRedditLinkedInWhatsAppPrintTumblr Related Tags: JavaScript, MySql, nodejs